Need help with a query

[Schwoerer]

I am a relatively new Access user and would really appreciate help with the following:

I have created a database for a daycare center and created a field with a drop-down menu populated with times: 7:00, 7:15, 7:30, 7:45, 8:00, etc. and called the field "Monday In". I have an In and Out field for each day of the week Monday - Friday. To fill out the form the user selects a time for a child's care hours, such as Monday In 7:00, Monday Out 6:00, Tuesday In 7:00 Tuesday Out 6:00, etc.

I want to create a query where I can calculate how many children are in the center at a given time for staffing purposes, so I built a query with the "Monday In" field and in the criteria filed I entered "8:00", but when I set the aggregate function to "count" or "sum" I get a "data type mismatch in criteria expression" error. I would like to know how many kids are in the center at 8:00, but I can't seem to make it work. I also tried to get the query to count "7:00 and 7:15 and 7:45, and 8:00", but it won't count. What am I doing wrong?

 

[Andrew Fergus]

Can you post the SQL of the query that didn't work? This will give us more information. You can get to the SQL in the query design screen under View -> SQL View.
Andrew

 

[Schwoerer]

SELECT Count(Roster.[Monday In]) AS [CountOfMonday In]
FROM Roster
HAVING (((Count(Roster.[Monday In]))="7:00"));

 

[Andrew Fergus]

To get the number of children on the roster as at 7am, try this SQL :

SELECT Count(Roster.[Monday In]) AS [CountOfMonday In]
FROM Roster
WHERE (((Roster.[Monday In])<=#07:00:00#) AND ((Roster.[Monday Out])>=#07:00:00#));

HTH, Andrew

 

[Schwoerer]

Thanks for the suggestion - I tried it and it came back with a vlaue of 0

 

[Andrew Fergus]

It worked for me with some sample data. Check your data has some values that meet the conditions of the query. In particular check the 'Monday Out' field has values in your rsoter table. The reason I included the 'Monday Out' field was to exclude children who, quite possibly, are checked in before 7am but are also rostered to leave before 7am (improbable I know but possible). Also, check your time fields are in the same format as the time entered in the query criteria. If these suggestions don't work, can you provide a sample of your data as well as the SQL that is not working?
Andrew