totals in queries

  • Thread starter Thread starter Jeff Lane
  • Start date Start date
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.
 
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.
 
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.
 
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
 
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.
 
Back
Top