Making a Table From a Query...

[dodgyone]

I have a field created from a query by where it lists the weeks overdue for members of staff.

I would like to query this one stage further and count how many records are 2 weeks, 4 weeks, 6 weeks, 8 weeks etc are overdue. I know how to query in order to bring up the result for each query (i.e <=2 etc etc) but how can you count how many of each that you have and then display this in some form of logical table for viewing purposes?

Any ideas on displaying the totals as well as creating a suitable chart or transporting the data into Excel maybe to work on it further.

Thanks in advance,

Marcus

 

[Qaroven]

Assuming a table something like

staffMembers (SID, StaffName, StaffDate)

something like

select
sum(iif(StaffDate <= Now()-14 and StaffDate > Now()-28, 1, 0)) as TwoToFourWeeksOverdue,
sum(iif((StaffDate <= Now()-28) and StaffDate > Now()-42, 1, 0)) as FourToSixWeeksOverdue,
sum(iif((StaffDate <= Now()-42) and StaffDate > Now()-56, 1, 0)) as SixToEightWeeksOverdue,
sum(iif((StaffDate <= Now()-56), 1, 0)) as EightOrMoreWeeksOverdue
from staffMembers Ben
+61 403 395 052