Counting by month

  • Thread starter bhrosey via AccessMonster.com
  • Start date
B

bhrosey via AccessMonster.com

Hi everyone...my employees are required to do 2 safety inspections each month
and if they meet that requirement, at the end of the quarter, they get a
small bonus. I have a table where their inspection information goes. It
contains empname, area they inspected, date of inspection and some other info
not pertinent to this issue. I have several different reports based on
queries where I can look at an individual empl and see what areas they
inspected and see that they did both inspections for a particular month OR I
have one where it shows a list of all empl and gives me just a count of
inspections QTD for each empl. and so on...Is there a way to write a query so
that I can see a count of every empl for each month so that the report would
look something like this??

Employee April May June Eligible for
bonus?
John Doe 2 1 2
no (an IIf statement here or something)
Mary Smith 2 2 2
yes
Jack Frost 1 2 2
no

Thanks in advance!!!!!
God Bless,
Bill R.
 
K

KARL DEWEY

Maybe play with the display some ---
TRANSFORM Count(bhrosey.[area they inspected]) AS [CountOfarea they inspected]
SELECT bhrosey.empname, IIf(Count([area they inspected])>3,"Yes","No") AS
[Eligible for bonus?]
FROM bhrosey
WHERE (((Format([date of inspection],"yyyyqq"))=Format(Date(),"yyyyqq")))
GROUP BY bhrosey.empname
PIVOT Format([date of inspection],"yyyy mm mmm");
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top