Strange Report Requirement (Access 2003)

[odeus]

I am fairly new to Access. I've been given a report to create that has me stumped. I have a table that is used to collect "snapshots" of statistics regarding employment counts at a variety of customers' sites. Each month, the table is updated with current counts, and this provides an ongoing history. My table has the following information: Client ID, number of full-time staff, number of part-time staff, date. The report I have to create is supposed to show the counts for each client for the current month compared to the prior month; the current month compared to the previous year-end (which is March 31st) and the current month compared to the count a year ago. All this on the same page along with variance calculations. The report is supposed to have the following format: Client ID, Client Name, Current Month, Prior Month, Variance, Current Month, Year End, variance, Current Month, Last Year, variance. There are about 100 clients.

I have no idea where to start! A summary report will not work, and a detail report doesn't give me the information that I need in the format that has been requested. Can you help?!

[HansV]

Welcome to Woody's Lounge!

It's possible to create a series of queries that will retrieve the data for the specified time periods, and use these to populate a report. It would be nice if you could attach a small sample database with real dates but otherwise dummy data. See <post#=401925>post 401925</post#> for instructions.

I'm not sure what variance should be calculated. Can you provide more information about that?

[odeus]

Hans, I'm attaching the table that's causing me the problem.

As for variance, they want to know the actual difference between the current month's numbers and compared month's numbers and the percentage this represents. If we had 10 employees last month and 15 this month, that would represent an absolute variance of 5 and a percentage increase of 50%. They want this for each section: Current versus last month; Current month versus last year-end and Current versus last year.

Any help would be greatly appreciated!

[HansV]

See the attached version. I only created the calculations for fulltime, I'll leave those for parttime to you.
The results aren't very interesting since there are no data for the current month yet.

[odeus]

Wow. That's amazing. I'm still trying to get my head around all those queries!
How does this criteria work in the qryYearEnd query? DateSerial(Year(Date())+(Month(Date())<4),3,1) <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I think I've figured out the rest of them.
This will work like a charm. Thanks so much. You're the best. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

[HansV]

That expression uses a trick: True = -1 and False = 0.

Month(Date()) returns the month number of today's date (currently 4 because it is April).
Month(Date())<4 is True if the current month is less than 4, i.e. for January, February and March, and False if the current month is 4 or more, i.e. for April ... December.
So if the current date is in January, February or March, -1 will be added to the year, in other words 1 will be subtracted.

If "today" is February 14, 2008, the expression DateSerial(Year(Date())+(Month(Date())<4),3,1) evaluates to DateSerial(2008+(-1),3,1) = DateSerial(2007,3,1) = March 1, 2007.
If "today" is May 24, 2008, the same expression evaluates to DateSerial(2008+(0),3,1) = DateSerial(2008,3,1) = March 1, 2008.