This year's data subtracted from last year's data

  • Thread starter Thread starter Susy
  • Start date Start date
S

Susy

I think I asked this question in the wrong newsgroup: In a
report I need to subtract a data point for this year from
a data point for the same month last year. My date field
is mm/dd/yyyy.

10/3/2002 803.25 A
10/7/2003 521.88 B

How can I parse the dates for just month and year and then
tell Access to subtract B from A?

Thanks.
 
Hi,


In a query, bring the table twice, say we have ta and ta_1.

In the grid, in a free column, first line, type

DateSerial( Year(ta.TheDate ), Month(ta.TheDate), 1)

add the criteria, under it:


=DateSerial( 1+Year(ta_1.TheDate, Month(ta_1.TheDate), 1)


That makes ta correspond to ta_1, through the same month, one year later (ta
is one year after the year from ta_1, both data are about the same month).


ta.FieldName - ta_1.FieldName

would then subtract the field name values, as wanted, assuming there is only
one record, per month, per year. If there is more than one record per month,
per year, then you have to tell what you want to do in that case (probably a
SUM over a GROUP, but I can't tell from your initial message).


Hoping it may help,
Vanderghast, Access MVP
 
Thank you so much. It worked beautifully!

However, I've lost all my data for the first years
(because of course there is no previous data to subtract.
How do I show these values?

Thanks again!
 
Hi,


That would require editing the SQL text.

The WHERE clause will have to be "moved into" the FROM clause, and the whole
statement would have to look to something like:


SELECT ta.*,
ta.NumData- Nz( ta_1.NumData, 0) As variation

FROM ta LEFT JOIN ta AS ta_1
ON DateSerial( Year(ta.TheDate ), Month(ta.TheDate), 1)
=DateSerial( 1+Year(ta_1.TheDate, Month(ta_1.TheDate), 1)


ORDER BY ta.TheDate DESC ;



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top