Select qQuery to extract first and last run time into subtotal

I have a table with the following data:
ProductionDate   Time             Product    Pounds
10/15/2007         05:45:40am          00244          25
10/15/2007         05:45:42am          00244          32
10/15/2007         05:46:01am          00245          31
10/15/2007         06:01:02 am         00244          15
I need to write a query that will summarize pounds by date. It will also need to count each product/date combination. In addition, I need to determine the length of time between the time the 1st case was produced and the last case was produced.
Using the example above, the completed would create a table that looks like this:
Date             Product          Total Lbs            Count         StartTime           EndTime
10/15/2007   00244            82                       3                 05:45:40 am     06:01:02 am
10/15/2007   00245            15                       1                 05:46:01 am     05:46:01 am
I was able to create a report that gives me this data. I placed the following in the date footer:
=Round((DateDiff("s",First([TransTime]),Last([TransTime])))/60,2). This worked out fine. However,
Note: production date will not cross over from one day to another at midnight.  I was able to use
DoCmd.OutputTo acOutputReport, stDocName, acFormatTXT, "c:\test.txt"
to get a text file that can be imported into Excel. This will work. However, I would still like to know if it can be done in a query.


Start Free Trial