How to extract maximum record data across three tables

[Petrol]

See my previous reply, above, concerning PhilOfWalton's suggested Query49.

As far as I can see I have accurately converted Phil's Query48 into my application's terminology, but it gets a "Syntax error in JOIN operation" when I try to run or save it. The error highlights the last instance of the WPH table name before the AND operator, thus:

SELECT PersonID, [Name_SN-FN], [Name_FN-SN], Walks.[WalkNumber], Walks.[StartDate] 
FROM People INNER JOIN ((qry_LastWalks INNER JOIN Walks ON qry_LastWalks.LastWalk = Walks.StartDate) 
INNER JOIN [Walk_participation_history ] ON (Walks.[WalkNumber] = [B][U][Walk_participation_history][/U][/B].WalkNumber) 
      AND ([qry_LastWalks].PersonID = [Walk_participation_history].PersonID)) 
ON People.PersonID = [Walk_participation_history].PersonID 
ORDER BY People.[Name_SN-FN];

What am I doing wrong?

[PhilOfWalton]

What you are asking for now is getting a little dodgy. A glutton for punishment could do 2 walks on 1 day, so you need further information in your table to identify the later walk. This could be a date time combination. In other words the Walk Date would be of the form 1st May 2016 11:00

If you want to add a WalkNumber to your Walk Table, that's fine.

Leave your modified Query49 alone (It should now show a Date & Time)

Difficult to know what is wrong with your SQL, but I notice an additional space in line 3

INNER JOIN [Walk_participation_history ]

SQLs are all very well, but I hope this image may help


Phil

[Petrol]

No, no, nobody is going to attend two walks in one day. (If there are concurrent walks they'll be many miles apart, possibly in different States!) And each Walk is uniquely identified by its Walk number or WalkID, which as mentioned before contains a location code (e.g. BS96, SYD114 etc). The point is that because of the aggregation issue I mentioned before, Query49 is only able to return the Date of the person's latest walk, not the walk number. So therefore if there were two walks in different locations on that day Query48 won't know which walk the person attended and so won't be able to reliably return the correct venue, position on the walk, photo number etc.

As for Query49, as I said it wouldn't run or save because of the join syntax error, so I wasn't able to see the QBE image. However after I removed the space from line 3 I did a compact and repair, and it now works.

So the whole system now seems to work OK except where a person attended one of two concurrent walks, in which case it's 50/50 whether we return the right information.

[PhilOfWalton]

Glad that at least you are able to see the QBE grid. That makes things much simpler.

As to the date problem, I can't see a way round it. If you remember, I said some time back that the walk date should also be in the WPH table, which I think would solve the problem. It's no big deal putting it there as you can set it's default value = WalkDate from your Walks table, and, assuming you have a form for the walks and a continuous subform for the walkers, you can set Enabled to false, so that date can't be changed.

Phil

[Petrol]

Yes, I was afraid that might be the case. It seems to be all because SQL won't let me have a non-aggregated field in a query with another field aggregated, so I can't carry the WalkID into Query49 along with the date. I assume there's some good reason for this, deep down in its heart.

Anyway I guess it's more or less working, and I'll think about whether to put the date in WPH or just live with the error in the odd case where two walks coincided. I learnt programming in the days of Fortran and Cobol and machine code subroutines, where every byte of storage was critical, so it goes against the grain to put redundant data in 7000 WPH records when it should be sufficient to put it in 500 Walk records, but ... c'est la vie!

Thanks again for all your help, both on this occasion and previously.
Peter

[PhilOfWalton]

Ah! Peter, don't tell me about storage space. Our first office computer used a language called Logol (Not Logo). 16k of memory and 2 * 14" 4MB hard drives. If you used only capital letters. you could squeeze 4 letters into the space of 3 by using bit manipulation. Nothing I couldn't do with that machine. Then like you, onto Cobol & Unix.

I am reminiscing, so back to your problem. I am always reluctant to provide duff results, sometimes no result is better than misleading information.

Anyway, if you do want to have a go using the date in the WPH table, (WPHDate) you might check this out.

Query51

SELECT Max(WPH.WPHDate) AS MaxOfWPHDate, WPH.PersonID
FROM WPH
GROUP BY WPH.PersonID
ORDER BY WPH.PersonID;

Query52

SELECT Walks.Walk, Walks.WalkNumber, WPH.Position, Member.MemSurName
FROM Walks INNER JOIN (Member INNER JOIN (WPH INNER JOIN Query51 ON WPH.WPHDate = Query51.MaxOfWPHDate) ON (Member.MemberID = WPH.PersonID) AND (Member.MemberID = Query51.PersonID)) ON Walks.WalkID = WPH.WalkID;

Both similar to the previous stuff, but I would use the old QBE version of the old Query48 and modify that.

Phil

[Petrol]

Hard drives? What's a hard drive? Our Control Data 3200 also had 16K of memory (though admittedly it was 16K of 24-bit words) but didn't have any hard drives, mainly because they hadn't been invented. We had 2400-foot reels of half inch magnetic tape. The 3200's baby brother, the 160A, didn't even have that, until we wrote a mag tape driver for it. For input it had a paper tape reader and a row of 12 little buttons in which we entered machine code one bit at a time. Thems were the days.

Anyway, forgive the gap in correspondence. I took a couple of days off to celebrate our 48th wedding anniversary.

I guess I'll give up and put the date into the WPH, but that means I'll have to do three things:
1. Set it into all the existing WPH records. I can do that easily enough with an update query.
2. Ensure that everywhere a WPH record is created I find the start date and put that into any new records. Again, that should be easy enough, possibly the way you suggested in post #19.
3. Make sure that nobody can ever change the date in either place without the other being updated also. That is likely to be trickier.

The ideal would be to have the date field in WPH containing just pointer to the date in the Walks field. Easy enough in a query, but I can't see any way of doing this in a table. Can you? Lookup fields don't quite seem to do it.

[PhilOfWalton]

Congrats on the wedding anniversary. Sound as if you're not much younger than I am.

So no problem with your project. I assume you have a main form for the walks and a continuous subform (SubWalks) with a combo box to add the walkers. Anyway that's what I have done to check all works OK

So on the subform you need this

Option Compare Database
Option Explicit

Private Sub Form_BeforeInsert(Cancel As Integer)

    WPHDate = Me.Parent!WalkDate
    
End Sub

That sets the date. Incidentally make sure you set WPHDate locked = True.

On the main form (Walks), you need the following code to ensure your dates are correct

Option Compare Database
Option Explicit

Private Sub WalkDate_AfterUpdate()

    Dim MyDb As Database
    Dim WalkSet As Recordset
    Dim SQLStg As String
    
    SQLStg = "SELECT WPH.WalkID, WPH.WPHDate From WPH "
    SQLStg = SQLStg & "WHERE WPH.WalkID = " & WalkID & ";"
    
    Set MyDb = CurrentDb
    Set WalkSet = MyDb.OpenRecordset(SQLStg)
    
    With WalkSet
        Do Until .EOF
            .Edit
            !WPHDate = WalkDate
            .Update
            .MoveNext
        Loop
        .Close
        Set WalkSet = Nothing
    End With
    
    SubWalks.Requery
    
End Sub

Phil

[Petrol]

Thanks again, Phil.
Actually it's not quite as simple as that. I didn't try to explain the full system in my early posts, but it's not about hikes in the country but about about the Walk to Emmaus Community, so it involves applications, sponsorships, team selection and preparation, diets, talks, musicians, a library and much more. Currently I have 18 tables, 51 queries, 41 forms and 32 reports - and counting!

None-the-less the code you have given will prove very useful - I'll adapt it for the walk participation entry bits. So thanks very much once again for all your help. I was well and truly lost without it.

Have a Happy New Year - and stand by for more posts from a gradually learning newbie!

Peter