Another Access question

Factual Questions
1

I am an Access newbie, and having problems designing a database to track my books.

I’m trying to design a system to replace my manual ledgers. I have a table (and a form) that lets me enter in each client, and what they paid for various services (haircut, blowdry, color), etc.

Thanks to a Doper’s help last night, I can now add these totals together, to see a total for haircuts, colors, etc., and ultimately a grand total for all services.

The next thing I can’t figure out is the following: how do I do these sums, but for only the last week?

I’ve tried the “Between Date() and DateAdd(“d”,-7,Date())” in the criteria box, but that doesn’t work (presumably because the field is a currency addition, with no date attached to it - that is a seperate field).

So, the question is, how do I tell Access to do the following:

“Sum up all of the transactions in the “haircuts” column, only for transactions in the last week?”

I assume I can repeat the same formula, with substitution, for the rest of the columns, and for the weekly grand total, just do a Sum() function.

Thanks for your help on this.

2

You need to add a column in the query with the date field. Set the ‘Total’ row to “Where”, and put your criteria formula in that row. FYI-your terms are reversed. You need to put the earlier date in front of the ‘And’.

3

You need to add a column in the query with the date field. Set that column’s ‘Total’ row to “Where”, and put your criteria formula in that column’s ‘Criteria’ row. FYI-your terms are reversed. You need to put the earlier date in front of the ‘And’.

4

Pull your date field down into the query grid and then put your date criteria in that.

5

What BF said, only you don’t need Between, because obviously you haven’t sold anything tomorrow. “>= Date() - 7” will do.