Counting instances of a record?

[NewShoes]

Hi all,

I am in the process of designing a database that will track employee attendance. What I need is a query that can look up how many times a person has been absent but (this is where it gets tricky) I only want to return the ones that have more than a set amount of absences.

I realsie that without seeing the database it may be hard to give an answer but I am more looking at 1) is it possible? and 2) a rough idea of what an example query may look like.

Thanks for any help

NS.

 

[HAMMAMABUARQOUB]

use the Dcount as a filed
use the >=1 in the criteria section exactly under this field

 

[NewShoes]

use the Dcount as a filed
use the >=1 in the criteria section exactly under this field

Hi, thanks for your quick reply. Please can you explain this in a little more detail. I'm a bit of an Access novice.

Many thanks,
NS

 

[NewShoes]

Can I also make it clear (I dont think I did in my first post) that I would like to return the actual records (rows) of the people with more than a set amount of absences....not just the number of people.

Thanks,
NS

 

[Galaxiom]

It is rather difficult without knowing the database structure.
If you have one record for each employee absence in among other records:

Make a query with employeeID and the field that indicates absence.
Turn on Totals (right click the design grid)
Set EmployeeID field to Group By and the Absence to Sum.

Add a >= [minimum absence] criteria to the Absence column.

When you run the query it will ask for the minimum absence.
Or you can put a number directly into the criteria.
Or you can get the figure from a control on a form.

 

[Galaxiom]

Can I also make it clear (I dont think I did in my first post) that I would like to return the actual records (rows)....

The query I described will return the employees and a count of their absence records.

To get the actual rows of these absence records from the original table you then join this query back to the original table on employeeID.

 

[NewShoes]

The query I described will return the employees and a count of their absence records.

To get the actual rows of these absence records from the original table you then join this query back to the original table on employeeID.

Thanks, that is a fantastic help. There is only one this I am unsure of.....you say "join this query back to the original table on employeeID". Can you explain what this means?

Thanks,
NS

 

[Galaxiom]

Once you save the query it can be used like a table in another query.
In the new query design view add the original table and the new query.
Join the two by clicking on the employeeID field in one of them and drag to the employeeID field in the other. This will draw a line between them.

Add the fields you want from the original table to the design field grid. When you run the query only records from the original table with employeeIDs shown in the first query will appear.

Incidentally I should clarify my original post.
Use Sum of the Absence field if this field is the number of days off in that absence.
Use Count if their is one record for each day they had off.

If the records show the first date and last date of the absence you will need to derive another field with the number of days off then Sum this field.

 

[NewShoes]

Thanks for the breakdown, much appreciated!

 

[NewShoes]

As a follow on from this...is it possible to return only those records with more than a set amount of absences BUT within a 2 date period. For example, I'd like to create a list of each employee who had more than 2 absences with the period 01/07/09 to 20/07/09.

I'm guessing that I just do what has been posted already but add a 'Between...And' criteria to the date of absence field.

NB: There is one record for each day of absence.

Many thanks,
NS

 

[NewShoes]

As a follow on from this...is it possible to return only those records with more than a set amount of absences BUT within a 2 date period. For example, I'd like to create a list of each employee who had more than 2 absences with the period 01/07/09 to 20/07/09.

I'm guessing that I just do what has been posted already but add a 'Between...And' criteria to the date of absence field.

NB: There is one record for each day of absence.

Many thanks,
NS

Hi, can anyone offer me a glimmer of hope on the above?

I have tried it today but came across the problem whereby if I enter a 2 dates using the Between...And operator, and 1 as the number of sick days, I end up with all days in the result. It is very perculiar (and annoying!)

Any help would be very much appreciated

NS

 

[Galaxiom]

Can't quite grasp the problem.

Are you saying that your query is returning dates outside of the between dates criteria?

Can you post a database with the table and your current query? Just put a few dummy records in the table.

 

[NewShoes]

Thanks for your reply Galaxiom....however, i have sorted this now. It appears as if the query was working (looking at records between the 2 dates) but if it found more than the specified amount, it would return them ALL, not just the ones between the 2 dates.

I appreciate it is hard to get undertsand not seeing the database.

Mnay thanks,
NS

 

[MSAccessRookie]

Hi all,

I am in the process of designing a database that will track employee attendance. What I need is a query that can look up how many times a person has been absent but (this is where it gets tricky) I only want to return the ones that have more than a set amount of absences.

I realsie that without seeing the database it may be hard to give an answer but I am more looking at 1) is it possible? and 2) a rough idea of what an example query may look like.

Thanks for any help

NS.

Sounds like COUNTing the absences WHERE the Date is within a set range, and GROUPing BY EmployeeID HAVING COUNT of Absences greater than the limit would work here

SELECT YourTable.EmployeeID, COUNT(YourTable.Absences)
FROM YourTable
WHERE YourTable.AbsenceDate BETWEEN [Enter StartDate] AND [Enter End Date]
GROUP BY EmployeeID HAVING COUNT(YourTable.Absences) > {MinAmount}

 

[gemma-the-husky]

is this sorted yet - this is the sort of stuff that access does very quickly, and easily, but is slightly fiddly to set up, especially if you are new to it

assuming you have tables of
a) employees
b) absences

where tableb identifies the employee, then as follows.

1. have a query that selects the absences between given dates (open it, and make sure its picking up all the absences)
2. now change this to a totals query, and COUNT the absences by employee (so now you just get the totals)
3. any extraneous data about the absence now needs ot be EXCLUDED, and the dateabsent now needs to be a WHERE not a GROUPBY, because otherwise you will find this data messes up the counts (try it and see)


Now, if you want to limit this to absences over a certain number, then put (eg) >5 in the criteria for the absence count.

so you end up with a query that shows just
employeeid
absencecount

Now have a second query that takes this query, and the employees table, and joins them together - now you can pick up employee related stuff, like name etc

note that you probably could have included this in the original query - but often having the associated tables in the final query messes the counts up - depends exactly what you are doing.

--------
all of the criteria in the base query (the date range, and the absence count) can be picked up as parameters, rather than hard coded - this should get you started though

 

[NewShoes]

Many many thanks to MSAccessRookie and Gemma-the-Husky. From gaining what info I could from your replies I have managed to sort this out. My solution seems to be a mix of your 2 replies. It may not be the most well built query but it seems to work

Thanks again,
-NS