help with

[Jimbo]

I want to know how many employees I have working in a given half hour
with the state licensure they have.

I have a table that has my employees, state licensure and each
individual half hour segement they are working. When I view as a
pivot table, the results are giving me all that have that specific
state licensure.

Example: @ 6am, I have one person with Alaska licensure, but the pivot
table say 11 (that is my total staff with Alaska)


How do I make this happen? Thanks in advance.

 

[vanderghast]

Assuming a table like:


Employee DateTime
Mary 06:00
Mary 06:30
Mary 07:00
Martin 07:00
Mary 07:30
Martin 07:30
Henry 07:30
....


then


TRANSFORM Nz(COUNT(*), 0)
SELECT employee
FROM tablenameHere
GROUP BY "all"
PIVOT DateTime


should return

06:00 06:30 07:00 07:30 ...
1 1 2 3 ...


Note that since we only have ONE big group, I explicitly added the GROUP BY
"all" clause, but in theory you should be able to remove it, since when
there is no group, the whole data is one group. Sure, if you have a third
field, such as licenceType, then you can decide to group on that field.


Vanderghast, Access MVP