comparing month to month

[Guest]

I have a table that has three three fields with data. An example is below:

Operator Scrap Amount Year-Month
---------- ----------------- --------------
BRAD D $791.00 200612
TIM H $500.00 200611
BRAD D $350.00 200611

I would like to build a query that would take the December amount $791.00
for BRAD D and compare it to his previous month of $350.00 for November. Is
there a way I can do this?

 

[strive4peace]

Hi Steve,

make a general function
'~~~~~~~~~~
'change data types if you are not using strings
Function GetPrevYM(pYM as string) as string

dim mYear as long _
, mMonth as integer _
, mDate as date

mYear = left(pYM,4)
mMonth = right(pYM,2)

mDate = DateSerial(mYear, Mmonth-1,1)

GetPrevYM = format(mDate, "yyyymm")

end function
'~~~~~~~~~~~~

put 2 instances of your table at the top of the query

link on operator

field --> [Year-Month]
Table --> Tablename

field --> [Scrap Amount]
Table --> Tablename

on the second instance (which I will call Tablename1)...

field --> PrevYM: [Year-Month]
Table --> Tablename1
criteria --> GetPrevYM(Tablename.[Year-Month])

field --> PrevScrap: [Scrap Amount]
Table --> Tablename1

then, you will have what you need on your grid to do the comparison

field --> ScrapDiff: Tablename.[Scrap Amount] - [PrevScrap]

you may need to put a bit more error checking in there in case you do
not have a previous record as well as modify your join line to pick up
all the "current" values if desired



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*