query problem - calculating sick leave

[tizwaz]

Can anyone help me with the best way to do the following
I want to record days on sick leave against people's names. Then I want at anytime to pull up a query/report which will tell me how many days they have been off sick since a particular date (based on a parameter query)
I thought I'd got it to work by putting all dates for a few years in a speadsheet and putting a persons initials next to the day/days they are off and then linking that to my database that worked but then I realised that more than one person could be off on the same day. Now I seem to have gone braindead. I'm sure there's probably a simple way to do this. By the way only weekdays will count as sick leave not weekends

 

[beetee]

Are you using Access now?

If so, what's the structure of the table you are using to store the sick leave information?

If you want to start fresh, consider that an employee database should probably have a larger charter than simply monitoring how much sick leave people have.

That being said, an answer to your question might be:
1) Create a table describing employees; with the company ID number (e.g. SS number), First, Middle, Last names, and an autonumber field to use as a unique, internal identifier.

The Company ID field should be the primary key
The autonumber field should be a unique key

2) Create a second table that tracks sick leave taken, it consists of a number and a date. The number will be the autonumber field described above, and will tell you which employee had which sick day.

3) Use the relationships editor to create a relationship between the autonumber ID in the employee table and the number ID in the SickLeaveTaken table.

4) create a query that groups the SickLeaveTaken table by employee ID and counts the number of sick days.

NOTES:
you can make it easier to select employees by making the number ID in the SickLeaveTaken table a combo box.

When you design a table, as you click in each field you can set field properties.

There are two tabs, General, and Lookup.

With the cursor in the Number ID field, click the Lookup Tab.

Set the display control to ComboBox.

Click into the Row Source line, and click the builder (...)

Create a query using the Employee Table. Drag the autonumber ID field into column 1, and, say, first, middle, and last names into columns 2, 3, 4

Set the bound column to 1

Set the column count to 4

Set column widths to 0;1;.5;1 (you may want to play with those numbers later)

You will now be able to select employees from a drop-down list. When you build forms using wizards based on this table, the drop down list will be created for you.

Good Luck!

 

[tizwaz]

Thanks for this. The table I was using was a link to a excel spreadsheet. This had all dates for the year/s down col 1 with a number 1 in the column which related to a particular employee beside each day they were sick. The employees name being the column heading. The only reason I did this was I thought it would be quicker if someone was on long term sick to drag autofill down the column. However when I do a parameter query with > [enter start date] I get all the dates with either a 1 or a 0 next to them and cant seem to total it
I THINK YOU'RE RIGHT I NEED TO START AGAIN. HAVE TRIED TO DO WHAT YOU SUGGEST BUT HAVE GOT A BIT STUCK. FIRSTLY WHEN I GO ON THE DROP DOWN FIELD TO SELECT THE EMPLOYEE NAME ALL MY NAMES ARE REPEATED. SECONDLY I HAVE TRIED TO CREATE A QUERY THAT GROUPS THE SICK LEAVE TAKEN BY EMPLOYEE ID AND COUNTS THE NUMBER OF SICK DAYS BUT I'M DOING SOMETHING WRONG. I HAVE GOT FIRST NAME, LAST NAME AND EMPLOYEE ID IN THE GRID WITH GROUP BY UNDER EACH AND THEN DATE WITH COUNT UNDER THAT AND IN THE CRITERIA I HAVE > [ENTER DATE] BUT WHEN I TRY AND RUN IT IT SAYS IT IS TOO COMPLEX OR SOMETHING

 

[beetee]

1) Employee names repeated: that seems strange. When you create the employee table, there should only be one instance of each employee. To debug that problem, I'd probably need to see actual data.
2) I can't imagine what's wrong with your query either. One way to work with it would be to remove the [Enter Date] portion and see if it runs correctly.

Now that you've told me a little more about your excel spreadsheet, I think we can come up with a solution that's based solely on the link. Which seems to make sense in your case, possibly someone else is maintaining the spreadsheet.

Assuming your spreadsheet looks like this:

Date Emp1Name Emp2Name Emp3Name...
01/01/01 1 0 0
01/02/01 0 1 0

To get totals based on that, I'll give you some SQL that you can paste into a query in design view (select SQL view):

SELECT Sum(TestSickLeave.Emp1Name) AS Emp1Name, Sum(TestSickLeave.Emp2Name) AS Emp2Name, Sum(TestSickLeave.Emp3Name) AS Emp3Name, Sum(TestSickLeave.Emp4Name) AS Emp4Name
FROM TestSickLeave
WHERE TestSickLeave.Date>[Enter Date];


Substitute in the actual employee name column headings and that should solve the problem for you.

 

[AvGuy]

You're off to a good start. However, avoid using autonumber fields for real data because they will change if you compact the database. Assign an employeeID to each employee which can be a pseudo autonumber field such as DMax("EmployeeID", "Employees&quot + 1.

For recording sick leave why not have a table that has simply the EmployeeID and the date serial of any date they were off sick? You can embed a subform on a master employee form using this table as the data source for the subform. You could, for example, have a "date off sick" entry and a "date returned to work" entry and then compute the number of days, less weekends, the person was out.

Write a function to test a date for weekday or weekend (there are built in date functions for this) and them sum using VBA the number of days a person was out sick during any given period. Bind a field on the form to this function and you'll get an instanteous and continuous readout of a persons's sick leave status. You can also tie in number of days allowed against number of days used, make the arithemetic calculation and display the result. Really, it's only limited by your imagination and willingness to invest the time to make it both efficient and elegant.

Good luck.

Uncle Jack

 

[beetee]

Your post is full of good points (including the same table structure I suggested for the 'Days Off' table), but I think one misconception needs to be cleared up.

Autonumber fields do not change if you compact the database. The purpose of an autonumber field is to provide an ironclad unique ID for a record. It's very convenient for linking one table to another because you don't need to copy entire primary keys. The only thing that changes about autonumber fields d/t compaction is that the *next* autonumber field will be 1 + the largest autonumber field currently used (the internal counter can be larger than that due to deleted records or cancelled additions).

 

[AvGuy]

Compacting a database resets the autonumber counter to the next highest remaining entry in the table plus 1. If, for example, you have 50 records in a table and delete all but record 25, after compacting the next entry will be numbered 26 even though 26 was used once before. This can and does cause confusion in certain cases. For this reason I never use autonumbers for real data, but only for counters or lineID values. I think it is safer practice to control ID numbers as suggested above rather than submitting such key fields to Microsoft to manage.

Uncle Jack

 

[beetee]

Deleting records that are referenced elsewhere isn't considered correct database behavior.

You might want to consider looking at table relationships, enforcing referential integrity so you can avoid that situation.

When you use a key to link two records in two tables, you are defining a relationship between the two records. If you delete records from the table in which the key has been defined, you have essentially destroyed the relationship. When you add records back to that table, you have no guarantee that the same relationship will be established.

For these reasons, and the reasons posted above, I always use autonumber fields for real data, and have found it to be fast, convenient, and reliable.

I must additionally state, that while I am by no means an ardent Microsoft supporter, when you are using a Microsoft product, you are submitting every character in every field to Microsoft to manage.