Specific info on access report

0
votes

Probably get shot for posting this again but last attempt was put on hold so sorry in advance. i am very new at this so apologies if its a simple answer;

I created a table with name of purchaser, items purchased, date of purchase and cost of purchase. From that i wanted to create a report that would show each purchasers name only once with a combined total cost of all purchases.

I created a query that did just that using only the purchasers name and the total cost of their purchases. I then created the report from that query.

The report shows each name once with a total cost of purchases which was great except for the query continually adds those total purchases without the ability to select a date range and likewise the report shows the same info.

When i add the purchased date to the query/report so i can filter between 2 date ranges it then shows each name "X" amount of times with a total for each purchase made which is not what i am looking for as this ends up with a long report.

Hope this makes more sense than my last attempt at this question. I am very new at this so a simple answer would be great. Thanks in advance

1
sqlms-access
Sounds like you need to use the sum function and group by clause. It might be worth your while to learn some basics before trying to do stuff. I've heard good things about the book, Teach Yourself SQL in 10 Minutes. - Dan Bracuk
Just downloaded that book. Hope it helps. thanks - Deanjp32

1 Answers

0
votes
  1. You need to get two parameters for the query, say [Start] and [End].
  2. You need to add the date column twice so that it can be compared to [Start] AND [End]
  3. You need to add the date column (on both occasions) with a Total "Where"; this tells access that the column has no other purpose than to impact a WHERE-constraint on the base dataset.

If you run into trouble, take the SQL below, correct all names in it, paste it into the query's SQL view, and then see what the design view looks like!

SELECT table.customer, Sum(table.price) AS sum
FROM table
WHERE (((table.date)>=[Start]) AND ((table.date)<=[End]))
GROUP BY table.customer;