Aggregate Query HELP

[isuruk]

I urgently need help in this I have attached a screenshot of the query I created to total points for each competitor and them I want the query to return only the highest for each agegroup. But I dont know how to do it. :banghead: :banghead:

 

[KenHigg]

You should be able to do this with a group by and dmax()...

 

[isuruk]

You should be able to do this with a group by and dmax()...

Well I am not sure how to do that. Can you pls advice further
I have a eventresults table and the agegroup is in the participant table
Points for individual event are stored in the eventresults table. I want to add the points of each participant and return the highest for each agegroup.

 

[isuruk]

Can you pls check the screeshot of my query and help me improve it?

 

[KenHigg]

My first guess is that you're grouping on too many columns...

 

[isuruk]

yes but access wont allow removing the grouping
this is the SQL
can you tel me how to improve it

SELECT tblStudent.AgeGroup, tblStudentEvent.StudentID, tblStudent.FirstName, tblStudent.Surname, tblStudent.HouseID, tblStudent.Gender, Sum(tblStudentEvent.Points) AS TotalPoints
FROM tblStudent INNER JOIN tblStudentEvent ON tblStudent.StudentID = tblStudentEvent.StudentID
GROUP BY tblStudent.AgeGroup, tblStudentEvent.StudentID, tblStudent.FirstName, tblStudent.Surname, tblStudent.HouseID, tblStudent.Gender
ORDER BY Sum(tblStudentEvent.Points) DESC;

 

[isuruk]

Can you pls look at the screenshot. This SQL is of that query

 

[KenHigg]

Remove the entire column(s) you don't want (need), not just the grouping clause...

 

[isuruk]

Yes I realise that but I need that information returned to generate a report
Is there another way to get round this prob? :banghead: PLs HELP

 

[plog]

I want the query to return only the highest for each agegroup...but I need that information returned to generate a report

If you need 2 different sets of data, use 2 different queries. You can't operate at 2 different levels in the same query (Totals By Age Group and Individual Names).

 

[isuruk]

thank u! will try n let you know how I get round this prob

 

[Brianwarnock]

There are probably a few ways to skin this cat but the simplest is a three query stack.

Query1 takes in tblstudentevent and totals the points for each studentid.
Query2 joins query1 to tblstudent on studentid selects these two fields and groups on them and also totalpoints which it max
Query3 joins query2 to tblstudent on studentid to select rest of fields required

The report is based on query3 , the system will run all of the queries.

Brian

 

[isuruk]

thank you.. will try this