# Another Access question

**URL:** <https://boards.straightdope.com/t/another-access-question/131400>\
**Category:** Factual Questions\
**Created:** [October 8, 2002, 6:04pm UTC](https://boards.straightdope.com/t/another-access-question/131400 "2002-10-08T18:04:21Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![sethdallob](https://avatars.discourse-cdn.com/v4/letter/s/e36b37/32.png) [@sethdallob](https://boards.straightdope.com/u/sethdallob)\
**Post date:** [October 8, 2002, 6:04pm UTC](https://boards.straightdope.com/t/another-access-question/131400/1 "2002-10-08T18:04:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\
**Post date:** [October 8, 2002, 6:15pm UTC](https://boards.straightdope.com/t/another-access-question/131400/2 "2002-10-08T18:15:25Z")

</div>

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’.

---

<div class="post-metadata">

**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\
**Post date:** [October 8, 2002, 6:15pm UTC](https://boards.straightdope.com/t/another-access-question/131400/3 "2002-10-08T18:15:46Z")

</div>

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’.

---

<div class="post-metadata">

**Author:** ![BF](https://avatars.discourse-cdn.com/v4/letter/b/b5ac83/32.png) [@BF](https://boards.straightdope.com/u/BF)\
**Post date:** [October 8, 2002, 6:56pm UTC](https://boards.straightdope.com/t/another-access-question/131400/4 "2002-10-08T18:56:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jbird3000](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@jbird3000](https://boards.straightdope.com/u/jbird3000)\
**Post date:** [October 8, 2002, 8:55pm UTC](https://boards.straightdope.com/t/another-access-question/131400/5 "2002-10-08T20:55:27Z")

</div>

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