Query to count Y/N

[mari_hitz]

Hi everybody,

Hope you are great. I have an access database and the purpose of it is to track the number of leave of absence people takes in my team for being sick or having to study.
I have two tables for this, because at my country people who is studing can take up to 10 days per year for studying reasons. So this table has the fields: Name of the person, and then a field for each day named "1,2,3..."; each of these fields are Yes/No fields to tick in case they did took the day.
And then another table which only has name of the person and a date picker to insert the date on which the person did not came to work (because we do not have a limit of days that people can take for sickness).
So I would like to create a query, to later create a report, that counts the number of days that people took for study and of sickness reasons.
I am trying to create a query that can count me the number of "Yes" fields that are ticked and the number of days taken for sickness. I have tried to create an expression that counts all the Fields with the tickboxes inserting the name of the person, but I had not succeeded. Do you have any idea how can I do this?

Thanks!

 

[Guus2005]

Your database isn't normalised properly. Search this forum for it or go to wikipedia.

When you have done that you'll get a table (tblLeave) with the following fields:
PersonID, number, FK to table tblPerson
LeaveDate, Date
ReasonID, number, FK to table tblReason containing all possible reasons

Here's the resulting query of all persons who exceeds their 10 day study limit

select tblPerson.Name, count(tblPerson.Name) as Exceeds
from tblLeave inner join tblPersons on tblLeave.PersonID = tblPersons.ID inner join tblReason on tblReason.ID = tblLeave.ReasonID
where tblReason.Description = 'Study'
group by tblPerson.Name
having count(tblPerson.Name)>10

From the top of my head.

As you can see the tblReason also needs a field called Description.

All the names mentioned are arbitrary.

Enjoy!