Problem with 'MAX' function 1

[RGarcia87]

I'm having trouble querying with the 'max' function. Below is my table:

Time Head Count
0:00 10
0:30 15
1:00 9
1:30 7

I'm trying to use the 'max' function for 'Headcount' field and I want it to return the time interval that has the 'max' headcount which would be 15. Instead, it's returning the whole table.

 

[PHV]

Something like this ?
SELECT [Time], [Head Count]
FROM myTable
WHERE [Head Count] = (SELECT Max([Head Count]) FROM MyTable);



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jebry]

Hi!

You can do it as follows:

Select Top 1 Time, [Head Count] From YourTableName Order By [Head Count] Descending

Note, I used the field names you gave, if they are different then you need to use your actual names. If you do have a field named Time, I would change the name of the field since Time is a key word in Access.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[RGarcia87]

That worked great PHV! Thanks

 

[RGarcia87]

I would also like to add in a 'Date' column but can't seem to figure out the code.

 

[PHV]

I would also like to add in a 'Date' column
Where is the 'Date' coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RGarcia87]

From the same table. I would like the result to show the max head count for each date and what time interval in occurred at.

Example:
Date Time Head Count
6/1/04 0:00 8
6/2/04 0:30 15
6/3/04 1:00 18
6/4/04 1:30 11

 

[PHV]

Have you tried this ?
SELECT A.Date, A.Time, A.[Head Count]
FROM myTable A INNER JOIN
(SELECT B.Date, Max(B.[Head Count]) As Head FROM MyTable B GROUP BY B.Date) C
ON (A.Date = C.Date) AND (A.[Head Count] = C.Head);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RGarcia87]

PHV, I have one last request. I had to add to this query again and I can't get it to work. This is the way it needs to look:

Date Time Department Head Count
6/1/04 0:00 CCC 8
6/2/04 0:30 POS 15
6/3/04 1:00 CMD 18
6/4/04 1:30 OSS 11

I need for the query to give me the max 'headcount' by DAY, the TIME interval it took place, and what DEPARTMENT it was for. All items come from one table. I would really appreciate your help. Thanks

 

[PHV]

Have you tried this ?
SELECT A.Date, A.Time, A.Department, A.[Head Count]
FROM myTable A INNER JOIN
(SELECT B.Date, Max(B.[Head Count]) As Head FROM MyTable B GROUP BY B.Date) C
ON (A.Date = C.Date) AND (A.[Head Count] = C.Head);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RGarcia87]

PHV, I get an error saying, "Syntax error in FROM clause".

 

[PHV]

I guess you're with ac97.
Copy the subquery (inside parens and without 'em) in the sql pane of a query design windows and save this query as, say qryMaxHead.
Then change the posted query like this:
... INNER JOIN qryMaxHead C ON ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RGarcia87]

I'm using Access 2003. I'll try it, though.

 

[PHV]

I'm using Access 2003
So the syntax I posted is perfectly legal for me.
Can you please post the exact SQL code raising the syntax error ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RGarcia87]

SELECT A.row_Date, A.startTime, A.Department, A.[Headcount]
FROM Tbl_Interval Stats CCC A INNER JOIN
(SELECT B.row_Date, Max(B.[Headcount]) As Head FROM Tbl_Interval Stats CCC B GROUP BY B.row_Date) C
ON (A.row_Date = C.Row_Date) AND (A.[Headcount] = C.Head);

 

[PHV]

You may try this:
SELECT A.row_Date, A.startTime, A.Department, A.Headcount
FROM [Tbl_Interval Stats CCC] A INNER JOIN
(SELECT B.row_Date, Max(B.Headcount) As Head FROM [Tbl_Interval Stats CCC] B GROUP BY B.row_Date) C
ON (A.row_Date = C.Row_Date) AND (A.Headcount = C.Head);
Just a tip: try to avoid spaces in table/query/field/form/control/... names.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RGarcia87]

Awesome! This worked perfectly. Thanks a bunch!