Question Aggregate Query Issue

[aztrix]

I think this is an issue that has haunted me for a long time and claims a lot of my loss sleep.

i need to create an aggregated query of records, with all fields grouped by except the DateCompleted field which is Max. This is so i can find the last date that an event was completed. simple enough.

Ok but what if i need to have the RecordID field as well so later i can distinguish between the records and save more data to it? Obviously without RecordID all the fields are grouped correctly but when i throw the RecordID field in it creates a unique field that can not be 'grouped', thus giving me ungrouped data.

is there anyway i can achieve this aggregation, and have the RecordID field there so i know what Record to work with later?

i hope someone understands where im coming from and can help.

 

[pbaldy]

Is this the type of thing you're trying to do?

http://www.baldyweb.com/LastValue.htm

 

[aztrix]

Yup, but within an access query. im gunna have to write the sql arn't i? cos mine has more than one join. IE the actual query im doing runs like this;

tblCompletedCourses has fields 'Personnel', 'CompletedCourse' and 'CompletedDate' and CompletedCourseRecordID (and a bunch more of other data)
-i want the max of date completed so i create an aggregate query to group the records by Personnel, CompletedCourse and the Max of Date. i can't include the CompletedCourseRecordID because its unique and will split all the records that im trying to group.

so that query is fine, i get the 'last' time (max of CompletedDate) that a Perssonel completed a course. but now i need that CompletedCourseRecordID so that i can link the 'Last' record with the rest of the data from the table.
-so i create a second query thinking that will be simple as my criteria to match the query back to the table will be 'Personnel', 'CompletedCourse' and 'CompletedDate'. i add the original table to the query and make the joins then i drag the CompletedcourseRecordID from the original table to the query and click run.
-it runs, but there is not data in the CompletedCourseRecordID field.............

its very frustrating! is there some way i can simply include CompletedCourseRecordID in the original aggregate query but 'exclude' it from aggregating as there is not option for this and the functions they do allow dont fix the problem.............i think.

 

[aztrix]

yeah kinda, but i have multiple joins cos in need to join PersonnelID, MaxofCompletedDate, CourseID to the original table to get the CompletedCourseRecordID.

this is so confusing and frustrating!

 

[pbaldy]

Can you post the db?

 

[aztrix]

ok, ill do it in 5 mins. thanks baldy

 

[aztrix]

ok so you will have to look at the results from the 1st query and then look how it excludes data in the 2nd.

thanks

View attachment QueryProb.mdb

 

[aztrix]

how did you go PBaldy?