Using queries to filter sales data and group by month year

wilkob

Registered User.
Local time
Today, 07:27
Joined
Jan 10, 2005
Messages
86
Hello,

As a newbie I need help with the following

I am trying to use MS access to collect Sales data via an ODBC connection and to output it in a certain fomat.

Basically I have done the following:

1. Create a maketable query to extract ALL sales data from 2008 into a table called Sales2009

2. I have made a query that groups for all customers the sales turnover and, sorts them from biggest to smallest and that saves the result in a table customer ranking

3. From the above table I only need the 150 biggest customers, so I manually delete the rest

4. I make a query that takes the customers from the customer ranking table and links it to the Sales 2009 table

5. Now I want to use the query in point 4 to group per customer per period the sales resulting in something like

customer ABC 2009-2009 02 € 25.000
customer ABC 2009-2009 08 € 12.000
customer DEF 2009-2009 01 € 150
customer DEF 2009-2009 02 € 1245
customer DEF 2009-2009 09 € 2500

Can anyone tell me how to do this ?
I am not know with SQL and am quite a newbie so be gentle :-) with me
 
I'm a bit puzzled as to what is actually going on, like how do you determine the biggest customers, why make an intermediate table here, why not just a TOP 150 query, but to answer point 5 just Group on Customer and period and sum the sales using a Totals query.

Brian
 
Hi and thank you for your reply

Since we have a lot of small customers that I am not interested in, I decided to take the top 150
I was thinking of doing an descending sort on sales turnover in the table Sales 2008 (made a misstake in my earlier post)

Then I will manually delete all records for customers 151 >

Thanks for the answer on question 5. I didn't think of this as it was probably very obvious
 

Users who are viewing this thread

Back
Top Bottom