including yes/no check box in query?

[Mand]

I am a new user of Access, and basically trying to teach myself to set up a database for employees' detail, annual leave and sickness episodes.

I have an employee and an absence table and would like to run a query to flag up more than 4 episodes of sickness in a given timescale.

My absence table contains the following fields:
Record ID (autonumber) - primary key
Employee ID - foreign key from personnel table
Absence Type (lookup list from Absence Code table)
Start Date
End Date
Hours Lost
Week Ending
Ongoing (yes/no check box)
New (yes/no check box)

I have set up the ongoing and new to sort out ongoing episodes of illness from new. I only want to flag up the 'new' episodes.

How would I go about this - every time I run the query it comes up with nothing when I try to count number of 'new' boxes...

I would really appreciate the help!

Thanks

 

[Atomic Shrimp]

How are you trying to count them?

You can do it like this in a totalling query:

Episodes: IIf([New]=True,1,0)

Then set that column to 'Sum'

 

[Mand]

Thanks for your reply,

sorry, if I am in the QBE grid and I have

field name: New
Table: Absence Table

Where do I put the code that you entered?

Thanks

 

[Atomic Shrimp]

Paste it in to replace the field name 'New' *

Then set the column to 'sum'

Hope this helps

Mike




*The table name will probably disappear, but unless the query contains more than one table with a field named 'new', it won't be a problem.
If you *do* have two tables in the query, both with a field named 'new', you'll need an expression that specifies the table - something like this: Episodes: IIf([TableName].[New]=True,1,0)

 

[Mand]

Sorry, I think I am beyond help!

I ran this and it brings up the end episode column with the number one in each one - totalling 18 records, which is all of the sickness episodes I have entered - both ongoing and new.

What I need is a query that will run and tell me what employees have had 4 or more new episodes of sickness withing a given time scale.

Thanks for all your help so far!

Mand x

 

[Atomic Shrimp]

I expect there are some fields in your query that contain different values on every row - if you include this in a grouping/totalling query, it will try to group on them, and when you group on unique values, you get single rows.

Make a copy of the query and try removing all of the columns except Employee ID and the one that calculates the number of absence events.

 

[Atomic Shrimp]

If that still doesn't make sense... copy the SQL of your query into a reply here and we'll take a look - you can get the SQL by clicking the bit where it says 'design view' (top left in most versions of Access, I think) and changing it to 'SQL view'