Query-Same Line Date/Time/Min Qty

[Guest]

I have a query where I am trying to get the same date and time where the min
qty occured. Example:

Table:
Date Time Qty

1/1/05 1:00pm 3
1/1/05 2:00pm 4
1/1/05 3:00pm 2
1/2/05 1:00pm 1
1/2/05 2:00pm 5
1/2/05 3:00pm 4

Wanted result:

1/1/05 3:00pm 2
1/2/05 1:00pm 1

Now I can get the min Qty and group the dates and it gives me the min for
the date, but I also need to know what time it happened at. I basically need
to retrieve the time of the same row the min qty occured. I hope I have
explained this well.

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
for 4 approaches to this issue.

 

[Jeff Boyce]

Let me make sure I understand ... for each date, you want the minimum [Qty]?
Then, you want the time associated with that date and Min([Qty])?

First, what should happen if you have more than one date/time record with
the same (minimum) Qty value?

Next, from your sample/example, it appears you are using two fields to store
date and time -- Access has a Date/Time data type, and you can store both in
a single field.

Third, if your field names are actually "Date" and "Time", you will only
confused both yourself and Access -- these are reserved words.

If you can resolve the first item above, you could use a two-step process.
Step 1 - group by date, finding the minimum Qty. Step 2 - use the query
from Step 1, joined back to your table, to find the row (and hence the time)
for the records that match the date and Qty values.

Good luck!

Jeff Boyce
<Access MVP>