Select last four occurences only

[ahudson]

Hi, I've been scratching my head over this for a couple of days now but can't seem to see a way around it!
We run events and have various tickets outlets for each event. We monitor sales on a weekly basis and provide monthly feedback to the management. I therefore have EventID, SellerID and WeeklySales as fields. I am stumped at how to select only the last four weeks sales for a particular seller and to total these. Apologies if this is simplistic but Access is quite new to me and I'm a bit out of my depth with the selection of only the last four weeks part.

Thanks

[Rawb]

The simplest way is to simply include a date range in the WHERE Clause of your SQL String.

SELECT 
    [EventID], 
    [SellerID], 
    [WeeklySales] 
FROM 
    MyTable 
WHERE 
    [Timestamp] BETWEEN Date() AND DateAdd("w", -4, Date())

This assumes that you're storing the date in your Table, of course. If you're not, you should think about adding that in since you're at the mercy of the Table when it comes to ordering Records: Generally it's first-in, first-out, but that's not always the case (so a record you added today could appear BEFORE a Record you added yesterday).

Anyway, if you're not including the date in your Table, then you can just limit the results to the first 4 Records (or however many you need):

SELECT 
    TOP 4 
    [EventID], 
    [SellerID], 
    [WeeklySales] 
FROM 
    MyTable