Date Equation

[Skotor]

Ok I hate working with dates as I can never seem to get queries to do what I need with them.

I have sales collection records and I'm trying to get a per day sales equation to work. The sales records are entered like so...

Month / Year / Collect Date / Location# / Sales$

So each month there is a new record per location.

I am trying to do the following equaltion..

(Current Sales / (current collection date-previous collection date))

So anyone have any tips on how to get the # of days between the last 2 collection dates for a location? Or any idea on a differant way for the same result? Thanks!

 

[bjackson]

use datediff function
currentsales/(DateDiff("d", currentcollectiondate, previous date))

 

[Skotor]

Guess I need to make it a lil more clear hehe.

All dates are in one field. I need to select the last 2 dates entered per location. How would I go about getting the last 2 dates for a location? I am able to get the last entered date by turning on Sums and selecting Last. But how do I get the previous date for that?

The end result I'm looking for is...

Location # / Last [collection date] / previous [collection date] / Sales

I can get the Last, how do I get the previous?

 

[Rich]

Select Top2, order descending

 

[Skotor]

I tried that, but then it only gives 2 records. I need last and previous for EACH location. (over 500 locations a month collected)

 

[Jon K]

You can do it with two queries.

As an illustration, I have attached a sample DB, which contains 10 Collect Date records for 3 Location#'s.

The queries that I used are:

qryOne:-
SELECT Tablename.*,
(Select Count(*) from TableName as s where s.[Location#]=TableName.[Location#] and s.[Collect Date]>=TableName.[Collect Date]) AS Rank,
Rank-1 AS Num
FROM TableName
WHERE (Select Count(*) from TableName as s where s.[Location#]=TableName.[Location#] and s.[Collect Date]>=TableName.[Collect Date]) BETWEEN 1 AND 2
ORDER BY [Location#], [Collect Date] DESC;

qryTwo:-
SELECT Last.[Location#], Last.[Collect Date] AS [Last Collect Date],
Previous.[Collect Date] AS [Previous Collect Date], Last.Sales AS [Last Sales],
Previous.Sales AS [Previous Sales], DateDiff("d",[Previous Collect Date],[Last Collect Date]) AS [Number of Days],
CCur([Last Sales]/[Number of Days]) AS [Average Sales per day]
FROM qryOne AS [Last] INNER JOIN qryOne AS Previous ON (Last.Rank = Previous.Num) AND (Last.[Location#] = Previous.[Location#]);


The first query uses a subquery (a Select query in brackets) to retrieve the last and the previous records for each Location#. The Rank and the Num field in the query are for linking purpose in the second query.

The second query makes a self-join of qryOne to calculate the Number of Days and the Average Sales per day.


There are some limitations. Each Location# must have at least two records in the table. As subquery is used, it will take time if the table contains many records.


Hope you can adapt the queries to suit your needs. (It would be easier to type the queries in SQL View.)


The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.

 

[Skotor]

Thanks for the database!!

I'm not at work today, but from what I can tell it looks to do what I need it to do. i'll plug it into mine next tiem I'm in the office and post back about it. Your a life saver! Thanks again!

 

[Skotor]

Ok having a problem plugging qryONE into my database.

When I go to run it, it prompts me for input on s.Location#. Below is how I have it set up. Anyone see where I screwed it up?

SELECT MasterSales.*, (Select Count(*) from MasterSales as s where s.[Location#]=MasterSales.[Location#] and s.[Date]>=MasterSales.[Date]) AS Rank, Rank-1 AS Num
FROM MasterSales
WHERE ((((Select Count(*) from MasterSales as s where s.[Location#]=MasterSales.[Location#] and s.[Date]>=MasterSales.[Date])) Between 1 And 2))
ORDER BY MasterSales.[Location#], MasterSales.[Date] DESC;

 

[Jon K]

The SQL statement is fine.

Check the spelling of the field name in the MasterSales table.