Subtotals from one subreport to another

  • Thread starter Thread starter Perplexed
  • Start date Start date
P

Perplexed

I'm trying to design a report to track employee time off based on a set of
codes (Access 2003)l.
I already have an Access employee database. My main report uses a query
that gives me just the employee's name based on criteria (i.e., status,
location, and job title). I made a new table that is joined by EmployID
which has date and the code for type of leave (i.e., V=vacation, S= sick,
L=leave w/o pay, etc.).
I then made separate queries from this table for each of the individual
codes and subreports from them. I put the subreports into the main report
and now have a report that shows all the dates for each category per
employee. I grouped by date (month) and have subtotals.

Sample:
Vacation Sick Leave w/o Pay etc.....
John Public 1/13/09 2/3/98 3/23/09
1/14/09 Sub total = 1 Sub total = 1
Sub total = 2
4/1/09
4/2/09
4/3/09
Sub total = 3

I would like to put another subform in the report that lists the subtotals
by month based on above. I.e.
January February March
Vacation 2
Sick 1 1
Leave 1 1

Any suggestions?
 
Situation looks similar to what I have in my project and solution is probably
too complex to describe here in detail. Briefly, looks like you need to
'pivot' the data in the subreport. One solution for me was to create a 'temp'
table that holds data needed for the report just for the duration of the
process then all records are deleted so table is ready for the next use. I
have used UNION query in another situation to get the data rearranged from
the way stored in table. Also, a Crosstab Query may be suited to your needs.
Tried to do one in Access but ended up taking the data to Excel and doing it.
Seemed easier to understand.
 
Back
Top