Query with sort

[jackiem]

I have a table which logs driver hours using clock in/out input. The total hrs each day are calculated in a query. I need to do the following:

1. Calculate total hrs last 7 days
2. Calculate total hrs last 6 days
3. Calculate hrs avail tomorrow (1 & 2 - 70)

I need these to list sequentially in datasheet type format. Any suggestions?

 

[Fizzio]

You will need 3 queries

1 calc total in last 7 days
2 calc total in last 6 days
3. Create new query from these 2 and perform the calculation you need.

any help?

 

[jackiem]

I realize that but my question is really, How do I tell it to Sum the last 7 days from that date (not today). Do you understand?

 

[Fizzio]

Use the DateAdd function within the criteria of the query

Eg

Field: DrivingDate (Do not use 'Date' as a field name)
Total: Where (You will need to enable totals by depressing the Sigma button on the toolbar)
Criteria: Between DateAdd("d",-7,Date()) And Date() (for last 7 days)

hth

 

[jackiem]

With this criteria you are assuming I need the hours for the last 7 days from today.

This is what I need:

Driver: John Doe

Date Hrs Driving (Result) Last 7 days
9/1/03 0 0
9/2/03 5 5
9/3/03 10 15

Can this be done in a query?

 

[Fizzio]

You are looking at a Running Sum with the last field. This is easy to do in a report as there is an option to make a control a running sum but it takes a bit of code trickery to do it in a query.

See here
http://support.microsoft.com/?kbid=205183

Personally, I would use the report to present the data as it has the facility to create a running sum easily and populate the report with a simple query with the criteria I described earlier.

I strongly recommend you change the name of the date field to something other than 'Date' as this is a reserved word in access and will cause headaches at some time.