Query question

D

Denise

I am a new Access user and I am having lots of problems!!

I have my table set up with the following:

Date
Employee
RoomsComplete
Notes

The RoomsComplete is a "Yes/No". I want to set up a query that will count
the total number of Yes for each employee for the month.

Can someone help me - in baby steps please!!

Thank you.
 
G

George

Dear Denise,

First create the first query to select only the RoomsCompeted=True e.g.

SELECT Table1.Date1, Table1.Employee, Table1.RoomComplete, Table1.Notes
FROM Table1
WHERE (((Table1.RoomComplete)=Yes));

Then create a second query based on the first query to do what you need, e.g.

SELECT Format$([Date1],"mmmm yyyy",0,0) AS [Month], Query1.Employee,
Count(Query1.RoomComplete) AS CountOfRoomComplete
FROM Query1
GROUP BY Format$([Date1],"mmmm yyyy",0,0), Query1.Employee;

Note that you should change all instances of Table1 to your table name.

Hope this helps

GeorgeCY




Ο χÏήστης "Denise" έγγÏαψε:
 
J

John W. Vinson

I am a new Access user and I am having lots of problems!!

I have my table set up with the following:

Date
Employee
RoomsComplete
Notes

The RoomsComplete is a "Yes/No". I want to set up a query that will count
the total number of Yes for each employee for the month.
Click to expand...

Create a Query based on the table. Use a criterion on the [Date] field (which,
by the way, you should really rename; it's a reserved word) of
= DateSerial([Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:] + 1, 1)
Click to expand...

to select the desired month. If it's always the current month you can use
= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)
Click to expand...

Add the Employee field (see below for comments!).

Change the query to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M). Leave the default Group By in the totals row for Employee;
change it to Where for [Date] ( you just want to use this field to filter
which records to sum, you don't want to see it).

Finally type

-Sum([RoomsComplete])

in a vacant Field cell. For Yes/No fields Yes is stored as -1, No as 0 - so
you want to take the negative of the sum of all the records to count Yes
values.

Click the datasheet (leftmost) icon and you'll see the names and the count for
each.


Comments: If Employee is an employee name, you can improve your database by
adding an Employees table. It should have a primary key EmployeeID, and fields
LastName, FirstName, and any other biographical or workplace data that you
need. Having the name in your data table wastes space and - more critically -
leaves you open to data errors, with separate records for "Phil Jones",
"Philip Jones", "Phil Jonrs" and so on; and to problems if you happen to get
two employees with the same name. Your data table would then contain only an
EmployeeID field; you'ld fill it using a Combo Box on a form rather than
repetititvely typing (or mistyping!) the name.

John W. Vinson [MVP]
 
Top