How to Formulate this Access Query

[AlexMwells]

Hi,

I have a access database table with sales data in it, detailing sales rep, customer, date of sale, sale item etc.

I need to generate a query which will list which customers haven't brought off us in the last week, month, 6 months (input variable) and the sales rep responsible for the store.
eg
customer name, rep, date of last sale/ most recent sale
store a, tom, 17/3/2011
store b, joe, 1/1/2011

Then i know joe needs to go to store b and make a sale because they haven't purchased anything from us in the last 3 months. My problem has been calculating the lastest sales date. In excel i would calculate the number of days since sale for each company then use the small function to the most recent sale.

Thanks

Alex

[NeoPa]

This seems pretty straightforward Alex. Assuming all the details are accurate.

What you're looking to find is a list of all records where the Date stored is less than the current date - a variable factor. The first step then is to convert the value entered for the period (1 week; 1 month; 3 months; etc) into a recognisable SQL date literal (See Literal DateTimes and Their Delimiters (#)). I suspect that using datPeriod = DateAdd(?, ?, Date()) would be good here. I don't have enough detail from you to specify the two missing parameters but I'm sure you can work them out.

Assuming then, that you now have a variable (datPeriod) set up with the value that reflects the start of the period you want to scan back over, you can create the following string variable (strSQL) to contain the SQL you need to use to list the relevant items from your table :

strSQL = "SELECT * " & _
         "FROM   [tblSalesOV] " & _
         "WHERE  ([LastSaleDate] < " & _
         Format(datPeriod, "\#m/d/yyyy\#") & ")"

Clearly, I've had to guess at some of your field names, but it should be clear what you need to change to get it to work on your database.

[AlexMwells]

Hi NeoPa,

Thanks for your response.

Using your code would tells me all the sales made before a certain period of time. So company A may appear 50 times in that list. But don't care about 49 of them. I just need know the most recent sale to that company.

Bescially I need a list of every compnay i sell to, with the date of most recent sale to that company.

Thanks again

Alex

[Mariostg]

Simply adapt NeoPa'a query by adding SELECT TOP 1 * and GROUP BY [LastSaleDate] DESC

[NeoPa]

According to your post Alex, what data you had in which form wasn't exactly clear. I misread it as saying the data was already aggregated (hence my suggested code). If the data is as described in the first paragraph of your post then try instead something like :

strSQL = "SELECT   [Customer]" & _
         "       , [Sales Rep]" & _
         "       , Max([SaleDate]) AS [LastSaleDate] " & _
         "FROM     [tblSalesOV] " & _
         "GROUP BY [Customer]" & _
         "       , [Sales Rep]" & _
         "HAVING   ([LastSaleDate] < " & _
         Format(datPeriod, "\#m/d/yyyy\#") & ")"

[AlexMwells]

Hi MarioStg,
Thanks for the reply

Your idea works well thanks. I am currently using the following SQl query

SELECT TOP 1 SalesTable.ShopNumber, CustomerTable.[Shop name], CustomerTable.[Sales rep], SalesTable.SaleDate, Date()-[saledate] AS DaysAgo
FROM CustomerTable INNER JOIN SalesTable ON CustomerTable.[Shop Number] = SalesTable.ShopNumber
WHERE (((SalesTable.ShopNumber)=[Which shop number?]))
ORDER BY SalesTable.ShopNumber, SalesTable.SaleDate DESC;

Now at the moment, it requests a input for the shop number and gives back the most recent sale. :)

I would now like to expand it. I would like use the query over and over again incrementing the shop number 1,2,3,4 etc and give one single report back. I guess this will require a VBA macro, and some method to collect all the result together.

Do you have any ideas?

Thanks in advance

Alex

[Mariostg]

There are certainly more than one way to achieve this. But I cannot do it for you. I prefer to give hints when you are in trouble. Maybe someone will be willing to do it for you. I strongly recommend you start working on a solution. You might get better support that way.

[NeoPa]

Using TOP 1 will enable you to find the max for a specific grouping, but unfortunately doesn't scale well. As in, it doesn't work for multiple groupings (except by repeating the query over and over again).

If you look again at post #5 (clearly I was still working on the revised version as you posted your last) you should find the basics of how to achieve what you need. As Mario says, we prefer to, and are actively encouraged to, provide help while not providing boiler-plate answers for you (And I'm sure you're not expecting any more). If you have any difficulty implementing the concepts there into your own, already developed, SQL then please let us know. I'm sure we can help.

[AlexMwells]

Hi NeoPa and Mariostg,

Thanks for your help I will try working on it today. Yesteday was my first day using access. I had used WQL before, but not SQL. Thanks

[AlexMwells]

thank you very much

SELECT CustomerTable.[Shop Number], CustomerTable.[Sales rep], Max(SalesTable.[SaleDate]) AS LastSaleDate
FROM CustomerTable INNER JOIN SalesTable ON CustomerTable.[Shop Number] = SalesTable.ShopNumber
GROUP BY CustomerTable.[Shop Number], CustomerTable.[Sales rep];

Has worked exactly as I needed it

Thanks Again

Alex

[NeoPa]

Pleased to hear it Alex (and you're doing pretty well for a first-timer).

I notice there is nothing to exclude customers which fall outside of the range (The HAVING clause) so I expect that is either handled elsewhere or the data is convenient simply to scan through.