Find maximum

[wcleve]

I need help finding the maximum value of A for the day. If the maximum occurs several times, return the maximum A value equal to or closest > to 16:00.

Example:

Date A B C
1/1/05 14:00 300 10 5
1/1/05 15:00 300 8 3
1/1/05 16:00 100 5 12
1/1/05 17:00 300 5 5
1/1/05 18:00 300 10 20

1/2/05 14:00 200 10 10
1/2/05 15:00 500 7 6
1/2/05 16:00 500 20 50
1/2/05 17:00 400 18 60
1/2/05 18:00 500 2 20

Results should be:
1/1/05 17:00 300 5 5
1/2/05 16:00 500 20 50

I've tried grouping and use MAX, didn't work of course.

Help!

TIA
Wendy

 

[PCLewis]

SELECT int([Date]), max([A]) 
FROM table
GROUP BY Int([Date])

I am not aware of a single query method using Access's SQL that will return you the entire row like you are after. In Oracle SQL I cna do it but I've eithe rnot learned the trick in Access or it simply is not possible.

What You have to do is have three queries:

Q1:
SELECT int([Date]) as myDay , *
FROM table;

Q2:
SELECT int([Date]) as myDay, Max([A]) as MaxA
FROM table
GROUP BY int([Date]);

Q3:
SELECT Q1.* 
FROM Q2 INNER JOIN Q1 ON Q1.myDay=Q2.myDay and Q1.MaxA=Q2.A;

 

[wcleve]

Thank you.

 

[PCLewis]

Hmmm I have learned some things reading these forums and revise my previous statement.

Here is a way to do it with a single query (I think)

SELECT * 
FROM 
  (SELECT int([Date]) as myDay, Max([A]) as MaxA 
   FROM sample1 GROUP BY int([Date])) as Q1
INNER JOIN 
  (SELECT int([Date]) as myDay, A 
   FROM sample1) as Q2  
ON Q1.myDay=Q2.myDay and Q1.MaxA=Q2.A;