Hello guys and girls. I'm having a devil of a time with something, and thought you all might have some hints to get me moving forward again. I've inherited a database and have been tasked with some analysis but am unsure about how to do the following:
I have tables containing monthly data or units sold dating from two years ago. Other tables have ad campaigns, and the dates they began. I need to compare the three month average of units sold per month before the ad campaigns began. Here is the table structure I have to deal with:
tblMonthlySales
AgentNumber (PK related to master table of agents)
Jan-02 (number - units sold)
Feb-02 etc.
tblTVAds
AgentNumber (PK related to master)
TVStart (date - when the ads began)
How can I use queries or reports to relate the TVStart date to it's three prior months on the tblMonthlySales table? Should I use some kind of Iif statement? I tried on queries to use this:
[TVStart] between #mo/dd/yy# and #mo/dd/yy#
but that broke down when I did it for all the months, obviously. I'm at an impasse, so any help would be greatly appreciated. I'm not a frequent Access user, so forgive my lack of precision.
I have tables containing monthly data or units sold dating from two years ago. Other tables have ad campaigns, and the dates they began. I need to compare the three month average of units sold per month before the ad campaigns began. Here is the table structure I have to deal with:
tblMonthlySales
AgentNumber (PK related to master table of agents)
Jan-02 (number - units sold)
Feb-02 etc.
tblTVAds
AgentNumber (PK related to master)
TVStart (date - when the ads began)
How can I use queries or reports to relate the TVStart date to it's three prior months on the tblMonthlySales table? Should I use some kind of Iif statement? I tried on queries to use this:
[TVStart] between #mo/dd/yy# and #mo/dd/yy#
but that broke down when I did it for all the months, obviously. I'm at an impasse, so any help would be greatly appreciated. I'm not a frequent Access user, so forgive my lack of precision.
