How to extract maximum record data across three tables

[Petrol]

I have a database recording the participation of people on walks. To meet normalization rules there are three tables - People, Walks and Walk_participat ion_history. The Walks table gives extra information about the walk such as the date and venue, and the walk_participat ion_history (WPH) table gives details of each person's involvement such as his position on the walk, photo number etc. Each person may participate on one or several walks, so there will be a WPH record for each time he goes on a walk.

I need to extract details of a person's involvement in the most recent walk he has attended. So I have made a query with the required fields from all three tables, and tried to use GroupOn People.Person_I D and Max(Walks.Date) to get each person's most recent walk. The problem is how to get the other fields into the output. Once I click on the Totals button for the query it seems to insist that every field in the query has some sort of aggregation applied, whereas I want to carry the walk number, position, photo number etc from the most recent record for each person into the output.

Looking at posts on the internet I gather that the answer is to use a subquery, but because three tables are involved I can't quite get my head around how to do it. Any help would be much appreciated.

PEOPLE......... ........WPH.... ............... .WALKS
...Person_ID... ..........Perso n_ID........... ...Walk_no
...Age......... .......... ..Walk_number.. .......Date
...Suburb_ID... ..........Posit ion............ ...Venue
.....etc....... ..............e tc............. ............etc

[PhilOfWalton]

I suspect the answer is to create a query with all the data you want and then put this criteria against the date in your join table (Walks.Date)

DMax("Date", "Walks", "Person_ID = " & Person_ID)

Note that "Date" is a reserved word, and I strongly suggest you change that name to something like "WalkDate" in tables, queries & forms.

Phil

[Petrol]

Thank you, Phil. That looks promising. However I failed to express my need clearly. I don't just want to get the answer for a given person, I want to create a report listing a chosen subset of people showing (amongst other things) the person's most recent walk details.

I have a query with all the required fields, and I tried to put the suggested DMax into the Start_Date column of that, but it just gave me the latest record of the whole lot, not the latest per person.

Thanks for the reminder on Date, too. (I actually had called the field Start_date, but I just used Date in my OP for simplicity, forgetting about the reserved word restriction.)

[PhilOfWalton]

It should work. Are you sure you have something like "Person_ID = " & Person_ID in the criteria I suggested. The Person_ID may need further qualification like

DMax("Start_Date", "Walks", "Person_ID = " & People!Person_ID)

That should get the last StarDate for that person only. If that is missing you would get the last start date for everyone which is what you appear to be getting.
If you are still struggling, can you post your SQL.

Phil

[Petrol]

Thanks ... I'll do that, but have to go out to a funeral just now. However I'm not getting the start date for everyone, only for the most recent person. I actually want the last date for everyone. (See the first para in my last post).
Back in a few hours ...

[Petrol]

I put the suggested expression as follows
DMax("Start_Dat e","Walk_partic ipation_history ","Person_I D = " & "Person_ID" ) as a criterion in the Start_date column of the query, but it insists it can't find the name Start_date, although it is spelt correctly and if I take the criterion out it finds it as the column heading OK. I suspect I'm putting it in the wrong place. But in any case, as you pointed out, that gives me the last start date for the entire table. I want a row in my output for everyone, based on the last start date for that person.

As you have probably gathered I am not strong on SQL (this is my first ever Access project) so where possible I'm building my queries and reports using the facilities in the Access ribbon, rather than in raw SQL. However here is the code Access produced for my query. (For the purpose of this post I have removed a couple of extra tables joined to the people table, so all that is left are the three tables mentioned in the OP - People -->WPH-->Walks).
CODE
SELECT People.Person_I D, People.Type, People.Title, People.[Name_FN-SN], People.[Name_SN-FN], People.Date_of_ Birth, Year(Date())-Year([Date_of_Birth]) AS Age, IIf([Age]<100,[Age],"") AS ATY, People.Gender, People.Status, People.Next_pos ition, People.Next_Tab le_position, People.Next_Mus o_position, People.Next_Spi ro_position,
Walk_participat ion_history_1.W alk, Walks.Start_Dat e

FROM Walks RIGHT JOIN (Walk_participa tion_history AS Walk_participat ion_history_1 RIGHT JOIN People ON Walk_participat ion_history_1.P erson = People.Person_I D) ON Walks.Walk_numb er = Walk_participat ion_history_1.W alk

WHERE (((People.Type) ="M") AND ((Walks.Start_D ate)=DMax("Star t_Date","Walk_p articipation_hi story","Person_ ID = " & "Person_ID" )))
ORDER BY People.[Name_SN-FN];
/CODE
Thanks again for your help

[Petrol]

(What appears to be a space in "Person_ID" in the DMax statement in line 2 of the above post isn't really there in the original).

[PhilOfWalton]

2 Things.
I suspect that your SQL should have Inner Joins, not Right Joins as we are only interested in people who have done the walks. (There are probably people in your DB who have not walked yet, and equally, you may have set up walks to do in the future, - so no takers yet)

Where you are definately wrong is in the DMax statement I posted, so I'll try once again.

DMax("Start_Date","Walk_participation_history","Person_ID = " & Person_ID)

There are no quotes round the last Person_ID in the expression.
To explain what we are looking for is the maximum date for that person (Defined by Person_ID)
The way you have written it is "Person_ID = " & "Person_ID" which is trying to compare Person_ID (a long integer) with a bit of text which you have said is "Person_ID" but it could just as well be "Rabbit" or any other bit of text. Clearly you are not going to get sense from that.

Incidentally, I am not a great lover of underscores in field names.
"To my mind, "StartDate" is jut as clear as Start_Date" and WalkParticipati onHistory just as clear as Walk_participat ion_history. Less typing.

I note also there is a space in the Walk_partic ipation_history in the SQL you posted. I assume this is a typo error. Much safer always to copy & past code to eliminate mistakes

Phil

[Petrol]

First thing: No, it needs right joins. I actually want to list a different subset of records, selected on other criteria, whether they have done a Walk or not; but if they have done a walk I want to show their latest one.

Second thing. Yes you're right, but I had actually put it in without the quotes around the last Person_ID. I cut and pasted it from your post and changed the names Date to Start_Date and Walks to Walk_Participat ion_History, but Access insisted on adding the quotes around the last Person_ID and I hadn't noticed. It was doing that quite consistently, and in order to stop it doing so I had to put it in square brackets.

However, having done all that I still have the error described in my last post (#6): "it insists it can't find the name Start_date, although it is spelt correctly and if I take the criterion out it finds it as the column heading OK". In fact it loops producing the message "The expression you entered as a query parameter produced this error 'Emmaus cannot find the name 'Start_Date' you entered in the expression' " and even Windows (taskbar 'Close window') couldn't stop it. I had to resort to task manager to kill it.

[PhilOfWalton]

Ah! I think I see the problem. There should be a date field in walks where basically you can plan ahead and say we are going to do a walk to somewhere on such & such a date in the future.

Your table WPH joins the walk to the person, and should contain an additional date to show that the person has entered. Generally, I appreciate it will be the same as the date in table Walks, but if for some reason the walk is postponed, it could be a different date. That is the missing StartDate.

The DMax (& Dlookup etc.) can be applied to both tables & queries, but require all the fields mentioned within inverted commas to be included in that table/query.

Phil

Phil

[Petrol]

Well we're getting into system design issues here, Phil, and I haven't tried to give (or ask you to take the time to read) a full definition of what the system is doing and how and why. Essentially it's a record of Walk to Emmaus participation, if that means anything. The walk participation is purely a historical record, and everybody who attended a Walk did so on the day that Walk was held. Hence if I were to put the start date in every WPH record it would have to be the same for everyone; I would have to somehow copy the start date from the Walks table to every WPH record involving that walk. This goes against all the principles I have learned of avoiding unnecessary data duplication, and allows the possibility of ending up with inconsistent data ("A attended a certain walk on a certain day, but B attended the same walk on a different day" - a logical impossibility. I think I would be violating the rules of normalisation - in particular 3NF, I think.

I appreciate your wisdom and experience and very much value your advice, but I would really like to find a solution which allows me to get an answer to the question "What are the number and date of the last Walk, if any, that this person attended?" without resorting to that extreme. Surely there must be a way to do that?

Incidentally I think there must be a bug in Access 2016 if it allows me to put something in a query which sends it into a cretinous loop from which it can only be extracted by killing the application with Task Manager!

[jforbes]

Create a Query called something like PeopleCurrentWa lk:

SELECT
  Person_ID
, DMAX("Walk_Number", "WPH", "[Person_ID]=" & Person_ID) AS Walk_Number
FROM People

This will give you a list of People and their most current Walk.

Then create a second Query, called something like PeopleCurrentWa lkDetails:

SELECT
  People.Person_ID
, People.Age
, People.Suburb_ID
, Walk.Date
, Walk.Venue
FROM PeopleCurrentWalk
LEFT JOIN People
ON People.People_ID=PeopleCurrentWalk.People_ID
LEFT JOIN Walk 
ON Walk.Walk_No=PeopleCurrentWalk.Walk_Number

This will give you all the details you are looking for.

[Petrol]

Thank you, JForbes. From my very limited knowledge of SQL that certainly looks as if it should work. Unfortunately when I try it the first column contains all the Person_IDs in sequence but second column always comes out empty. Changing the "As" clause to a different name, and adding an inner join to the WPH table, made no difference.

[PhilOfWalton]

This is a bit crude, but seems to work. You will have to interpret some of my names to match with yours.

I first created a query (Query49)

SELECT Max(Walks.WalkDate) AS MaxOfWalkDate, WPH.PersonID
FROM Walks INNER JOIN WPH ON Walks.WalkID = WPH.WalkID
GROUP BY WPH.PersonID;

This gives the last date any person walked.

I then created Query48 to give the detail

SELECT Member.MemberID, Member.MemSurName, Member.MemFirstName, Walks.Venue, Walks.WalkDate
FROM Member INNER JOIN ((Query49 INNER JOIN Walks ON Query49.MaxOfWalkDate = Walks.WalkDate) INNER JOIN WPH ON (Walks.WalkID = WPH.WalkID) AND (Query49.PersonID = WPH.PersonID)) ON Member.MemberID = WPH.PersonID
ORDER BY Member.MemSurName, Member.MemFirstName;

Obviously substitute my Member for your Person as appropriate.

Once you see the thing in the QBE grid, it will all make sense
Phil

[Petrol]

Thanks, Phil, and thank you both Phil and JForbes for offering help during this busy Christmas season.

I'm still working on your Query48, but the first one, query49, works well. However there is a further challenge with it: I need to get the WalkID into that first query. The date of a walk does not uniquely identify the walk, because Walks can take place in various locations and some of them may be concurrent. Hence I need the Walk number in query49 to specify which walk it is. (The WalkID contains an alphabetical prefix indicating the location - e.g. BS94 - which is why I need the date, rather than the Walk number, to identify which is the most recent walk a person has completed).

All I want is one more column in a query like query49, giving the WalkID which is already in tbl Walks beside the date of the chosen Walk, but I can't get it. For reasons which elude me, SQL insists that if I use an aggregate function on any field of a query I must use aggregate functions in every field.

Can anyone see a way around this, other than by redesigning the application to incorporate a redundant date field in WPH?