Find Last Yaer's Value

[Jahanzaib]

I have a query having two columns.

Date Sales

01/01/07 xxx
03/02/07 xxx

Now I want to add one more column:

Date Sales Sales(Before 364 days)

01/01/07 xxx yyy
03/02/07 xxx yyy

How can I do this?

JB

 

[John Spencer]

Perhaps by using a subquery

SELECT T.Date, T.Sales,
(SELECT Sum(Sales)
FROM TheTable as T2
WHERE T2.Date Between DateAdd("d",-364,T.Date) and T.Date) as OldSales
FROM TheTable as T


Or by using the DSUM function
Field: DSum("Sales","TheTableName","Date between #" &
DateAdd("d",-364,TheTableName.Date) & "# AND #" & TheTableNameDate & "#")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dale Fye]

What do the current Date and Sales columns represent within your current
query? Is that cumulative sales for that date only? Why the large gap
between dates in your example (will you really have consecutive dates)?

For your 3rd column, are you trying to say that you want the sum of the
sales for the previous 364 days? If so, why 364, this is not a full year?
Why not use the previous year (for today, 30 Nov 07, why not use the sales
from 30 Nov 2006 - 29 Nov 2007)?

 

[Jahanzaib]

Hi, Thanks for reply,
Mr Fye,
My 'Sales' column of query shows the sales of 30 stores for each (day)
'Date' (in 1st column) and it does not shows cumalative sales.
I just want the sales before 364 days in 3rd column because it will return
'Last Year Same Day Sales'.

JB

 

[John Spencer]

SQL query statement would look something like the following.
SELECT T.Date, T.Sales
,(SELECT T2.Sales
FROM TheTable as T2
WHERE T2.Date = DateAdd("yyyy",-1,T.Date)) as LastYearSales
FROM TheTable as T

In design view, you would add a new column to your query.

Field: LastYearSales: (SELECT T2.Sales FROM TheTable as T2 WHERE T2.Date =
DateAdd("yyyy",-1,[TheTable].[Date]))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dale Fye]

I think John's post from today is what you are looking for.

Dale

 

[John Spencer]

Have you now added that you want this for each store?

SELECT T.Store, T.Date, T.Sales
,(SELECT T2.Sales
FROM TheTable as T2
WHERE T2.Date = DateAdd("yyyy",-1,T.Date)
AND T2.Store = T.Store) as LastYearSales
FROM TheTable as T

In Design view
Field: LastYearSales: (SELECT T2.Sales FROM TheTable as T2 WHERE T2.Store =
[TheTable].Store AND T2.Date = DateAdd("yyyy",-1,[TheTable].[Date]))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..