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

grouping - max

P: 10
I have a question that feels like it should be so simple. I have records for different employees with dates they have taken a certain test. I want to run a query that groups each employee and finds the last date they took the text using the 'max' choice in the query. This runs with no problem. Shows me each employee and the last date they took the test. However, I want the query to also return the score. But when I add the score field to the query, it returns all the scores for that test. Any way to get it to just show me the most recent score for each employee? Thanks in advance for any help!
Sep 5 '07 #1
Share this Question
Share on Google+
4 Replies


Jim Doherty
Expert 100+
P: 897
I have a question that feels like it should be so simple. I have records for different employees with dates they have taken a certain test. I want to run a query that groups each employee and finds the last date they took the text using the 'max' choice in the query. This runs with no problem. Shows me each employee and the last date they took the test. However, I want the query to also return the score. But when I add the score field to the query, it returns all the scores for that test. Any way to get it to just show me the most recent score for each employee? Thanks in advance for any help!

This depends on how you are storing your score data you mention 'ALL scores for that test' so there are many scores for one test?, is there more than one table involved in this? Please post your SQL statement so that we can see what it is your are doing currently, that gives us an idea on what structure you are working with.

Regards

Jim
Sep 5 '07 #2

P: 10
This depends on how you are storing your score data you mention 'ALL scores for that test' so there are many scores for one test?, is there more than one table involved in this? Please post your SQL statement so that we can see what it is your are doing currently, that gives us an idea on what structure you are working with.

Regards

Jim
There are many scores for the one test. I am trying to find only the score for the last time they took the test. Here is the SQL statement:

SELECT [admiss/disch].[admiss/disch #], [admiss/disch].[Client Name], Max(Gaf.Date) AS MaxOfDate, Gaf.Gaf, [admiss/disch].[D/C], [admiss/disch].Program
FROM [admiss/disch] INNER JOIN Gaf ON [admiss/disch].[admiss/disch #] = Gaf.[admiss/disch #]
GROUP BY [admiss/disch].[admiss/disch #], [admiss/disch].[Client Name], Gaf.Gaf, [admiss/disch].[D/C], [admiss/disch].Program
HAVING ((([admiss/disch].[D/C]) Between [start date] And [end date]) AND (([admiss/disch].Program)="Residential Treatment Facility"));


There are two tables: admiss/disch and Gaf. They are linked by the admiss/disch #. The Gaf.Gaf field in that table is the score for the test. Gaf.Date is the date field in that table. I have to include the admiss/disch table so I can add criteria for the D/C field and the Program field. The admiss/disch # and Client Name fields from the admiss/disch table are not absolutely necessary but I like to see them just to double check my results.

I hope this gives you all the information you need. Once again, thanks for any assistance you can give me. I really appreciate it.
Sep 5 '07 #3

P: 47
I think your problem is that the join returns a value of Gaf for ALL values of Date, not just MaxOfDate.
I would suggest deleting Gaf from the first part of the query, adding the Gaf table again as Gaf_1 (it will be named as this automatically if you are using the QBE grid), this time joining MaxOfDate to Gaf_1.date as well as [admiss/disch#].
You may have to break this into two subqueries to make it work.

Hope this helps.
Sep 5 '07 #4

Jim Doherty
Expert 100+
P: 897
There are many scores for the one test. I am trying to find only the score for the last time they took the test. Here is the SQL statement:

SELECT [admiss/disch].[admiss/disch #], [admiss/disch].[Client Name], Max(Gaf.Date) AS MaxOfDate, Gaf.Gaf, [admiss/disch].[D/C], [admiss/disch].Program
FROM [admiss/disch] INNER JOIN Gaf ON [admiss/disch].[admiss/disch #] = Gaf.[admiss/disch #]
GROUP BY [admiss/disch].[admiss/disch #], [admiss/disch].[Client Name], Gaf.Gaf, [admiss/disch].[D/C], [admiss/disch].Program
HAVING ((([admiss/disch].[D/C]) Between [start date] And [end date]) AND (([admiss/disch].Program)="Residential Treatment Facility"));


There are two tables: admiss/disch and Gaf. They are linked by the admiss/disch #. The Gaf.Gaf field in that table is the score for the test. Gaf.Date is the date field in that table. I have to include the admiss/disch table so I can add criteria for the D/C field and the Program field. The admiss/disch # and Client Name fields from the admiss/disch table are not absolutely necessary but I like to see them just to double check my results.

I hope this gives you all the information you need. Once again, thanks for any assistance you can give me. I really appreciate it.

Hi,

Use this have a look at it in design and then run it. You will see that it returns the score based on the maximum date value for each client where that client is referenced by the admiss/disch #

It returns all of those clients on "Residential Treatment Facility" between DC dates and returns your required list plus a column showing the Gaf score. This is done as an expression returning the Gafscore based on the MAX of the date column in the Gaf Table. It also displays those clients who whilst on the facility you have 'no score' for as represented by a blank value (as opposed to only returning a list where the score is greater than zero if you understand me on that.


Expand|Select|Wrap|Line Numbers
  1. SELECT [admiss/disch].[admiss/disch #], [admiss/disch].[Client Name], [admiss/disch].[D/C], [admiss/disch].Program, (SELECT Max([Gaf])  FROM Gaf WHERE [admiss/disch #]=[admiss/disch].[admiss/disch #]) AS Latest_Score
  2. FROM [admiss/disch]
  3. GROUP BY [admiss/disch].[admiss/disch #], [admiss/disch].[Client Name], [admiss/disch].[D/C], [admiss/disch].Program
  4. HAVING ((([admiss/disch].[D/C]) Between [start date] And [end date]) AND (([admiss/disch].Program)="Residential Treatment Facility"));

Just a pointer you might want to reconsider your field and table naming convention as it becomes a real pain to read and work with. Tip: leave out forward slashes, hyphens spaces and stuff like that. Its a nightmare to convert if your system needs to scale up. Have a look at Leszynski naming convention on the web if you need help on that

Kind Regards :))

Jim
Sep 5 '07 #5

Post your reply

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