Developer Forum

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!


ta_1.
1)
(ta_1.TheDate), 1)
one year later (ta
about the same month).
assuming there is only
one record per month,
that case (probably a
message).


In a
from
then

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



(ta
month).
only
month,
a