Grouping and Max Query

[Taff]

Hi all,

Really unsure how to word this so I have attached a sample of what I am trying to do.

I have three tables:-

tblStudents -
Learn_ID
Provi_ID
Surname
Forename

tblSchemes -
Learn_ID
Provi_ID
Lprog_ID
Type_LPG
ElwaOffi

tblQuals -
Learn_ID
Provi_ID
Lprog_ID
Lacti_ID
StartDte
EEnddte
TermDate

What I am trying to do is create a query which shows the all the fields from Students table, Lprog_Id and Type_Lpg from the schemes table and Lacti_ID, Startdte, EEnddte and Termdate from the Quals table.

However I am trying to group the results by Learn_ID and Lprog_ID and only show the record with the maximum Termdate within each group.

Not sure if I've made myself clear but any help is greatly appreciated.

Many Thanks

Ant.

 

[Brianwarnock]

I think that you will need to create a query grouping on Learn_ID and Lprog_ID with max on termdate and then join this on termdate to your original query

Brian

 

[Taff]

Hi Brian,

Thanks for the reply, am a bit unsure how to do that.

Could you please give me a bit more info.

Many thanks,

Ant.

 

[Brianwarnock]

Hm not sure I can but lets see

call your original query query1

Now create query2 take in tblQuals with the 3 fields I mentioned this will group as indicated but selecting only the record with max TermDate

Then query3 takes in query1 and query2 with a join on Termdate, and outputs all fields from query 1 but only those records with max TermDate will be selected.

Hope this explains it

Brian

 

[Taff]

Hi Brian,

Thanks for the again for the reply. I tried what you said but I am not sure if i have done it correctly.

I have attached what I have done.

Regards,

Anthony.

 

[Brianwarnock]

Back from the long weekend, 4 days in my case, but there is no attachment.

Brian

 

[Taff]

OOPS!!!

Try again.

Ant.

 

[Brianwarnock]

Hi
Query2 was incorrect but it still didn't work when I corrected it, so I looked at your data and I think I've now got it right.
Quals needs a unique id for each record so I've modified quals and created a 4th query and join that to query1, I hope it gives you what you want, you have duplicates on Learn_ID LPROG_ID and Termdate but with different Lacti_ID, these are all selected, is that correct?
I attach my attempt to solve your problem.

Brian

 

[Brianwarnock]

Curious to know if your problem is solved

 

[Brianwarnock]

Curious to know if your problem is solved

Thanks for your response, it's good to know that one's work is successful!

 

[Taff]

Hi Brian,

Sorry I haven't replied - been running around like a headless chicken for the past week.

It works fine - Just haven't had the time to put in place yet though.

Thanks again for your time and effort, much appreciated.

Regards,

Taff