By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

How to extract maximum record data across three tables

P: 91
I have a database recording the participation of people on walks. To meet normalization rules there are three tables - People, Walks and Walk_participation_history. The Walks table gives extra information about the walk such as the date and venue, and the walk_participation_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_ID 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....................WALK S
...Person_ID.............Person_ID..............Wa lk_no
...Age................... ..Walk_number.........Date
...Suburb_ID.............Position...............Ve nue
.....etc.....................etc.................. .......etc
Dec 22 '16 #1

✓ answered by 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)
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(Walks.WalkDate) AS MaxOfWalkDate, WPH.PersonID
  2. FROM Walks INNER JOIN WPH ON Walks.WalkID = WPH.WalkID
  3. GROUP BY WPH.PersonID;
  4.  
This gives the last date any person walked.

I then created Query48 to give the detail

Expand|Select|Wrap|Line Numbers
  1. SELECT Member.MemberID, Member.MemSurName, Member.MemFirstName, Walks.Venue, Walks.WalkDate
  2. 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
  3. ORDER BY Member.MemSurName, Member.MemFirstName;
  4.  
Obviously substitute my Member for your Person as appropriate.

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

Share this Question
Share on Google+
23 Replies


PhilOfWalton
Expert 100+
P: 1,430
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)

Expand|Select|Wrap|Line Numbers
  1. DMax("Date", "Walks", "Person_ID = " & Person_ID)
  2.  
Note that "Date" is a reserved word, and I strongly suggest you change that name to something like "WalkDate" in tables, queries & forms.

Phil
Dec 22 '16 #2

P: 91
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.)
Dec 22 '16 #3

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. DMax("Start_Date", "Walks", "Person_ID = " & People!Person_ID)
  2.  
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
Dec 22 '16 #4

P: 91
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 ...
Dec 23 '16 #5

P: 91
I put the suggested expression as follows
DMax("Start_Date","Walk_participation_history","Pe rson_ID = " & "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_ID, 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_position, People.Next_Table_position, People.Next_Muso_position, People.Next_Spiro_position,
Walk_participation_history_1.Walk, Walks.Start_Date

FROM Walks RIGHT JOIN (Walk_participation_history AS Walk_participation_history_1 RIGHT JOIN People ON Walk_participation_history_1.Person = People.Person_ID) ON Walks.Walk_number = Walk_participation_history_1.Walk

WHERE (((People.Type)="M") AND ((Walks.Start_Date)=DMax("Start_Date","Walk_partic ipation_history","Person_ID = " & "Person_ID")))
ORDER BY People.[Name_SN-FN];
/CODE
Thanks again for your help
Dec 23 '16 #6

P: 91
(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).
Dec 23 '16 #7

PhilOfWalton
Expert 100+
P: 1,430
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.

Expand|Select|Wrap|Line Numbers
  1. DMax("Start_Date","Walk_participation_history","Person_ID = " & Person_ID)
  2.  
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 WalkParticipationHistory just as clear as Walk_participation_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
Dec 23 '16 #8

P: 91
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_Participation_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.
Dec 23 '16 #9

PhilOfWalton
Expert 100+
P: 1,430
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
Dec 23 '16 #10

P: 91
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!
Dec 23 '16 #11

jforbes
Expert 100+
P: 1,107
Create a Query called something like PeopleCurrentWalk:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   Person_ID
  3. , DMAX("Walk_Number", "WPH", "[Person_ID]=" & Person_ID) AS Walk_Number
  4. FROM People
This will give you a list of People and their most current Walk.

Then create a second Query, called something like PeopleCurrentWalkDetails:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   People.Person_ID
  3. , People.Age
  4. , People.Suburb_ID
  5. , Walk.Date
  6. , Walk.Venue
  7. FROM PeopleCurrentWalk
  8. LEFT JOIN People
  9. ON People.People_ID=PeopleCurrentWalk.People_ID
  10. LEFT JOIN Walk 
  11. ON Walk.Walk_No=PeopleCurrentWalk.Walk_Number
This will give you all the details you are looking for.
Dec 24 '16 #12

P: 91
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.
Dec 24 '16 #13

PhilOfWalton
Expert 100+
P: 1,430
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)
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(Walks.WalkDate) AS MaxOfWalkDate, WPH.PersonID
  2. FROM Walks INNER JOIN WPH ON Walks.WalkID = WPH.WalkID
  3. GROUP BY WPH.PersonID;
  4.  
This gives the last date any person walked.

I then created Query48 to give the detail

Expand|Select|Wrap|Line Numbers
  1. SELECT Member.MemberID, Member.MemSurName, Member.MemFirstName, Walks.Venue, Walks.WalkDate
  2. 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
  3. ORDER BY Member.MemSurName, Member.MemFirstName;
  4.  
Obviously substitute my Member for your Person as appropriate.

Once you see the thing in the QBE grid, it will all make sense
Phil
Dec 26 '16 #14

P: 91
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?
Dec 27 '16 #15

P: 91
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT PersonID, [Name_SN-FN], [Name_FN-SN], Walks.[WalkNumber], Walks.[StartDate] 
  2. FROM People INNER JOIN ((qry_LastWalks INNER JOIN Walks ON qry_LastWalks.LastWalk = Walks.StartDate) 
  3. INNER JOIN [Walk_participation_history ] ON (Walks.[WalkNumber] = [Walk_participation_history].WalkNumber) 
  4.       AND ([qry_LastWalks].PersonID = [Walk_participation_history].PersonID)) 
  5. ON People.PersonID = [Walk_participation_history].PersonID 
  6. ORDER BY People.[Name_SN-FN];
  7.  
What am I doing wrong?
Dec 27 '16 #16

PhilOfWalton
Expert 100+
P: 1,430
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

Expand|Select|Wrap|Line Numbers
  1. INNER JOIN [Walk_participation_history ]
  2.  
SQLs are all very well, but I hope this image may help



Phil
Dec 27 '16 #17

P: 91
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.
Dec 27 '16 #18

PhilOfWalton
Expert 100+
P: 1,430
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
Dec 27 '16 #19

P: 91
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
Dec 27 '16 #20

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(WPH.WPHDate) AS MaxOfWPHDate, WPH.PersonID
  2. FROM WPH
  3. GROUP BY WPH.PersonID
  4. ORDER BY WPH.PersonID;
  5.  
Query52
Expand|Select|Wrap|Line Numbers
  1. SELECT Walks.Walk, Walks.WalkNumber, WPH.Position, Member.MemSurName
  2. 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;
  3.  
Both similar to the previous stuff, but I would use the old QBE version of the old Query48 and modify that.

Phil
Dec 28 '16 #21

P: 91
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.
Dec 29 '16 #22

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_BeforeInsert(Cancel As Integer)
  5.  
  6.     WPHDate = Me.Parent!WalkDate
  7.  
  8. End Sub
  9.  
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub WalkDate_AfterUpdate()
  5.  
  6.     Dim MyDb As Database
  7.     Dim WalkSet As Recordset
  8.     Dim SQLStg As String
  9.  
  10.     SQLStg = "SELECT WPH.WalkID, WPH.WPHDate From WPH "
  11.     SQLStg = SQLStg & "WHERE WPH.WalkID = " & WalkID & ";"
  12.  
  13.     Set MyDb = CurrentDb
  14.     Set WalkSet = MyDb.OpenRecordset(SQLStg)
  15.  
  16.     With WalkSet
  17.         Do Until .EOF
  18.             .Edit
  19.             !WPHDate = WalkDate
  20.             .Update
  21.             .MoveNext
  22.         Loop
  23.         .Close
  24.         Set WalkSet = Nothing
  25.     End With
  26.  
  27.     SubWalks.Requery
  28.  
  29. End Sub
  30.  
  31.  
Phil
Dec 30 '16 #23

P: 91
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
Dec 31 '16 #24

Post your reply

Sign in to post your reply or Sign up for a free account.