Record -1 year

[David8]

Hi from Barcelona,
I've a Table with two fields: date (date format) and sales (currency).
There are records for 5 years.
I want a query to show date, sales and the sales field whit the sales of the
year before.
I've try whith (date)-365 but i can't show the sales field.
Some ideas?

Thanks,
David8

 

[kc-mass]

Hi David

Try
SELECT Sum(tblSales.SaleValue) AS SumOfSaleValue
FROM tblSales
WHERE (((tblSales.DateOfSale) Between Date() And (Date()-365)));

You should look at the DateDiff() function in defining the range of dates.

Regards

Kevin

 

[John Spencer]

You need a correlated sub-query in the select clause to do this. The
SQL statement would look like the following.

SELECT [Date]
, Sales
, (SELECT Sum(Sales) as TotalSales
FROM [YourTable] as Temp
WHERE Temp.[Date] Between DateAdd("yyyy",-1,[YourTable].[Date])
and [YourTable].[Date]) as SalesForYear
FROM [YourTable]

If you need to include additional fields such as ITEMSold then you will
need to add that into the where clause of the correlated sub-query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================