Access 2007 query

Question

Below is the data I'm working with:

Location ID   Member ID   $1 Bill   $5 Bill   DATE       TIME
56789         000000000   2220      2000      4/1/2012   43337
56789         000000000   2221      2000      4/1/2012   62219
56789         000000000   2221      2000      4/1/2012   62220
56789         000123456   2226      2000      4/1/2012   63206
56789         000000000   2228      2001      4/1/2012   64438

I need a query to determine the dollar amount of each bill denomination received for each location, as well as the total dollar amount received for each location.

Location ID 56789 received a total of $1 between the time (s) of 4:33:37 and 6:22:19 from Member ID 000000000.

Location ID 56789 received a total of $5 between the time (s) of 6:22:20 and 6:32:06 from Member ID 000000000.

Location ID 56789 received a total of $7 between the time (s) of 6:32:06 and 6:44:38 from Member ID 000123456.

The Total amount received for Location ID 56789 for the date of 4/1/2012 is $13.

Any suggestions on how I would write a query to determine this ?

EDIT (additional info)

I also need to able to return [Member ID] = 000123456 with a [$1 Bill] total count of 2.

Example of desired result (per request):

Location ID  Member ID  $1 Bill Total  $5 Bill Total  DATE
  56789      000000000      5               0       4/1/2012
  56789      000123456      2               5       4/1/2012

Answer 1

First I'd like to say I am glad I don't have to work with a database that has this kind of naming convention.

Open a new query and paste this into the SQL view

SELECT[location id], max([$1 bill])-min([$1 bill]) +( max([$5 bill])-min([$5 bill]))*5 as dailyTotal
FROM myTable
group by [location id],date

and it will write a query that groups the daily totals for each location. from there you can change the WHERE part of the query to limit it down to whatever is useful.

Good luck