Sort by date, group by center, sum by copies 1

[ksmith108]

I am setting up a photocopying query based on a table with the following criteria.
Date = the date the record is entered into the database.
Center = the center that will pay for the copying.
Copies = the number of copies that were printed for each center.

In the query I can group by center and sum by copies. It gives me the total number of copies made for each center since the database was started. Perfect if I need a total of copies made since the beginning of time. However I would like to narrow the scope down to month to date totals. As soon as I add the date column to the query it no longer gives summary totals, but each record ever entered.
How can I sum by date, group by center and sum by copies?
Cheers
Kirk

 

[allanon]

I tried what you said and this is what I got:

Input table:
key date center copies
1 01/05/2002 center1 100
2 02/05/2002 center1 200
3 03/05/2002 center1 300
4 01/05/2002 center2 50
5 02/05/2002 center2 100
6 01/04/2002 center3 1000
7 15/04/2002 center3 2000
8 01/05/2002 center3 10000
9 15/05/2002 center3 2


The query:

SELECT Table6.center, Sum(Table6.copies) AS SumOfcopies
FROM Table6
WHERE Table6.date between [BeginDate] and [EndDate]
GROUP BY Table6.center
ORDER BY Table6.center;

I set [BeginDate] to be May 1, 2002 and [EndDate] to be May 30, 2002

The Results:

center SumOfcopies
center1 600
center2 150
center3 10002

As you can tell the query is not including records outside of my date range. Therefore your problem is something else. Post your query and I will look at it.

 

[ksmith108]

Thanks for the very prompt reply Allanon.
One word can make a big difference or no difference at all.
I added your AS SumOfcopies FROM Table6 and volia perfection.

Must have been my brain on snooze, after all I am at work.

Thanks again fhr the post
Cheers
Kirk