Count or Sum function in query

[Neil_Pattison]

I have designed an attendance database, with fields for personal details and fields for the days of each month.

I need to have a query that will ask for a certain date(i.e. find the specific field) and then search on specific criteria (i.e. ON SITE) to see which staff are available.

What is the best way of doing this? Any help would be greatly appreciated

 

[quest4]

It is a little hard to picture exactly what you are trying to do without seeing it, but to get a query to ask for a date just enter this in the criteria of the date field:
[ Enter Date to Search]
In the query you would have the Date and probably the StaffName and anything else you need to check for. hth.

 

[Neil_Pattison]

Basically there is a field for each date. In that field is the status of each employee (ie On Site, Holiday, Sick etc).


I need the user to be able to type in a date (ie search for the corresponding field) which would then display the total the amount of staff for that day that are On Site.

For instance in excel this formula would show as =COUNTIF(C5:C55,"On Site") where the C column corresponds to a date field

 

[neileg]

You need to create a totals query. In query design view click the sigma Σ button and use Count. Add the data and status criteria that you want.

 

[quest4]

Just do what I previously stated and what neileg stated and that should get you what you want. hth.