Most Recent 12 months

[JennP]

Hello -
I have a consumption table set up that records
consumption of a product each week. Then in a query, I
sum the consumption for each month. I need to set up a
query that will show the most recent 12 months of
consumption for a product. For example...
If it is currently March of 2005, the most recent 12
months would be March 2004 through February 2005. This
would provide me with a rolling update with the prior 12
months.

Any suggestions on how I would do this??
Thank you!!
Jenni

 

[Jason Byrnes]

SELECT * FROM yourTabel WHERE dateField BETWEEN now() and
dateAdd("yyyy", -1, now())

HTH
Jason

 

[JennP]

That worked!
Thank you, Thank you!!

-----Original Message-----
SELECT * FROM yourTabel WHERE dateField BETWEEN now() and
dateAdd("yyyy", -1, now())

HTH
Jason




.

 

[MGFoster]

Hello -
I have a consumption table set up that records
consumption of a product each week. Then in a query, I
sum the consumption for each month. I need to set up a
query that will show the most recent 12 months of
consumption for a product. For example...
If it is currently March of 2005, the most recent 12
months would be March 2004 through February 2005. This
would provide me with a rolling update with the prior 12
months.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a criteria like this (all one line):

date_column between dateadd("yyyy",
1,dateserial(year(date),month(date),0)+1)
and dateserial(year(date),month(date),0)

The formula:

dateserial(year(date),month(date),0)

returns the last date of the previous month.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9vnIechKqOuFEgEQIRoACg1YZTiMu10Qt52y06kMa8BmLAmM8An3yn
+CmuoOsTpElu+1doehJ2RgDW
=pWPx
-----END PGP SIGNATURE-----