J
JEA via AccessMonster.com
I have a crosstab query, it's columns are staff member ID's, the rows are
dates and the value of each 'cell' is the number of hours that staff member
worked.
Each staff member can be either core staff or other.
I want to create a report from this that has columns for each 'core' staff
members and one column that totals the hours worked for 'other' staff. Eg, if
on 01/12/07 core staff member 123 worked 12 hours, core staff member 456
worked 12 hours and other staff members 789 and 987 worked 8 hours each the
report would look like:
...Date...|...123...|...456...|...Other...|...Total...|
=================================|
01/12/07|....12....|....12....|...16.......|...40.......|
I can test if the staff member is 'core' or 'other' with a lookup of the
StaffData table using their staffID.
DLookup("[OfficerType]","StaffData","[StaffPin] = [x]") will return 'Core' or
'Other' depending on x.
There will always be 7 core staff. But who they are can change. The report
needs to be able to cope with this. I'm thinking I need perform another query
on the crosstab to do the calculation and create a report from this second
query. Any ideas?
(In case you're wondering, it's similar to a previous post as I'm being lazy
and trying to work around that problem)
dates and the value of each 'cell' is the number of hours that staff member
worked.
Each staff member can be either core staff or other.
I want to create a report from this that has columns for each 'core' staff
members and one column that totals the hours worked for 'other' staff. Eg, if
on 01/12/07 core staff member 123 worked 12 hours, core staff member 456
worked 12 hours and other staff members 789 and 987 worked 8 hours each the
report would look like:
...Date...|...123...|...456...|...Other...|...Total...|
=================================|
01/12/07|....12....|....12....|...16.......|...40.......|
I can test if the staff member is 'core' or 'other' with a lookup of the
StaffData table using their staffID.
DLookup("[OfficerType]","StaffData","[StaffPin] = [x]") will return 'Core' or
'Other' depending on x.
There will always be 7 core staff. But who they are can change. The report
needs to be able to cope with this. I'm thinking I need perform another query
on the crosstab to do the calculation and create a report from this second
query. Any ideas?
(In case you're wondering, it's similar to a previous post as I'm being lazy
and trying to work around that problem)