totals in queries

J

Jeff Lane

I have one table that has sales in 6 different areas, A1, A2, A3...A6.
I want to read the total number of sales by month for each area so that my
report shows MARCH A1=27 A2=34 A3=22 etc.
The only fields I think I need are Area, Sales and Date.
It sounds simple to me but I can't seem to get my head around it! Help is
desperately needed.
Should I include the table six times to get a result, if I do it without
asking for a total I get all the sales by area and handcount them. It works
but it isn't very professional.
 
L

Les

Not sure how you have date stored. You may have to play
around with it to get the month breakdown that you want.
(like yy/mm)

You should group by date, group by area, and sum on sales.
 
J

Jeff Boyce

Jeff

From your description, it sounds like you might have a spreadsheet, not a
database. Are you saying that your table structure has a different column
for each area? If so, consider looking into the topic of normalization --
it would make your querying much easier, and let you use more of the
strengths that Access offers.
 
J

Jeff Lane

none of the above.
I get 100 sales in a month,
SELECT [Ryder Mot].Sales, [Ryder Mot].Area
FROM [Ryder Mot]
WHERE ((([Ryder Mot].Area) Is Not Null) AND (([Ryder Mot].[Date]) Between
#11/1/2003# And #4/1/2004#))
ORDER BY [Ryder Mot].Area;

RyderMot is the table
Sales is the product
Area is the area the product is sold in
The date bit is easy and I have put in a fixed date to test the query.
I want it to return a total of the number in each area e.g. sale in area 1
=22
At the moment it returns a list of the product and the area in which they
were sold. I don't need to see the product so I could remove that but I have
left it in for now to make sure I am getting correst results.
I can get the total I need with a pen and pencil by counting up the 1s and
2s etc but I figure Access should be able to do that and the count function
isn't the answer
 
J

Jeff Boyce

Jeff

Check into the Totals query (see the toolbar button that looks like a Greek
sigma?). It sounds like you want to use the GROUP BY aggregation on your
Area, and the SUM aggregation on your Sales.
 
Top