Please help - new user pulling hair out!

[Mand]

Hi, I am new to the forum and Access, and have the task of creating a database for the employees at work.

I have my table of personnel set up, and a form to enter their data, which includes Employee ID, Title (lookup table), name, surname, address 1, 2, 3, town, county, postcode, Home, work and Tel nos, DOB, Job Title (lookup table) and contracted hours and notes.

I want to make a sub-table to show their absences, and more importantly their sickness rate.

I thought of setting up one with their ID, record ID of absence episode, absence code, (say SL for sickness,AL for annual leave etc from lookup table), date started, date finished and hours lost, (as we work in hours for holiday and sick).

What we want is to be able to run a query that would flag all of the employees that have had more than 4 episodes of sickness within a given time scale. How would I do that? Also If I choose annual leave and have a field for holiday entitlement (so many hours) can I get access to work out a running total of how many hours they have left to take, if I then have a field for hours taken for that absence?

Can any kind soul help? I am a beginner - so I know how to run a simple query, and I know where the expression builder is etc but would need a bit of a step-by-step guide.

P.S. I am using 2003.

I would be really grateful!

Amanda x

 

[WIS]

Hi, I am new to the forum and Access, and have the task of creating a database for the employees at work.

I have my table of personnel set up, and a form to enter their data, which includes Employee ID, Title (lookup table), name, surname, address 1, 2, 3, town, county, postcode, Home, work and Tel nos, DOB, Job Title (lookup table) and contracted hours and notes.

I want to make a sub-table to show their absences, and more importantly their sickness rate.

I thought of setting up one with their ID, record ID of absence episode, absence code, (say SL for sickness,AL for annual leave etc from lookup table), date started, date finished and hours lost, (as we work in hours for holiday and sick).

What we want is to be able to run a query that would flag all of the employees that have had more than 4 episodes of sickness within a given time scale. How would I do that? Also If I choose annual leave and have a field for holiday entitlement (so many hours) can I get access to work out a running total of how many hours they have left to take, if I then have a field for hours taken for that absence?

Can any kind soul help? I am a beginner - so I know how to run a simple query, and I know where the expression builder is etc but would need a bit of a step-by-step guide.

P.S. I am using 2003.

I would be really grateful!

Amanda x

I would do this by setting up a Group qry built from the sub tbl and a Select qry from the main tbl.

Group qry: I always use the QBE grid unless I'm writing a UNION qry. I would firstly build a Select qry and then a Group qry based on that. (Because you need a date filter, I've found inconsistent results if you try and do it all at once).

In the QBE grid:
Qry1: Personnel, DateStart, DateFinish,AbsenceCode. In the Criteria grid for both DateStart and DateFinish add "Between [Enter Opening Date] and [Enter Closing Date] (both on the same line so that they make an "AND" - both Start and Finish have to fall between these 2 dates). Even though you have entered the criteria twice (I believe) it should only prompt you once. In the criteria for AbsenceCode enter SL. Make sure all criteria are on the same line as you want to satisfy all conditions at the same time. ie AND condition.

This qry will give you all the records that match the date criteria and SL.

The next step is to look for count of PersonnelID > 4. This is where you need a Total (GroupBy) qry built on Qry1.

GrpQry: Table Qry1
PIDersonnelID: First
NoersonnelID Count
In No: criteria grid put >4

Notice how I've put PID: and No: in front of the flds. This will name the flds in the GrpQry PID instead of FirstOfPersonnelID etc.

This will give you any PersonnelID with more than 4 occurences.

Qry2: Create another Select qry from the main tbl, putting in PersonnelID and any other info that you require on the employee.

Qry3: Now build a qry where PID from GrpQry joins PersonnelID from Qry2 and select all the flds you want to see.

This is quite long winded and there probably are others that could do this more simply.

 

[Mand]

Thank you so much for your time and effort, I will have a crack at this and let you know how I get on!

 

[Mand]

I have followed the instructions, and have made the first query, but when I try the group query - it's not counting the emplyees with more than 4 occurrences - it's coming up with only one Emplyee ID and the number 6 in the count - although there are only 3 episodes of Absence for this person... Not sure if I am doing this wrong...

I made a new query.
1st column field PID: Personnel from Query 1
Pressed totals button - put in group by first
second column put in No: Personnel and count in the group by
then >4 in the criteria

help! Sorry!

 

[WIS]

1st column field PID: Personnel from Query 1
Pressed totals button - put in group by first

My mistake - PID should be "Group By", not "First".

 

[Mand]

hmm, I seemed to have a problem with this! In the end I ran query 1, then in query 2 referred to query 1 table and did

Pesonnel ID: Group by
Name - group by
Surname - group by (added these in as I wanted to see the employee's name aswell)
Absence type - "count" and in criteria box >=4
Absence Type - "where" sl
This seems to work - does it seem right to you?

 

[WIS]

hmm, I seemed to have a problem with this! In the end I ran query 1, then in query 2 referred to query 1 table and did

Pesonnel ID: Group by
Name - group by
Surname - group by (added these in as I wanted to see the employee's name aswell)
Absence type - "count" and in criteria box >=4
Absence Type - "where" sl
This seems to work - does it seem right to you?

Have dummied up a db and am posting it - I've changed PersonnelID to EmployeeID. The qry with "final" in the name is the one that gives you the info as required for SL etc.

 

[Mand]

I see what you have done now I think. Hmm, mine seemed to be working - is this wrong then? - it does seem to be pulling out the info I want; however I don't know how to set parameters like you have done with yours, which does seem handier if you want to change the date that is inputted...

 

[WIS]

I see what you have done now I think. Hmm, mine seemed to be working - is this wrong then? - it does seem to be pulling out the info I want; however I don't know how to set parameters like you have done with yours, which does seem handier if you want to change the date that is inputted...

The main feature of my qrys is the parameters ([Enter Open Date] etc.). I tend to probably break the data down into too many qrys. But I have been caught out several times so I stick to my method, which I know will give me 100% accuracy.

You may be well right in what you are doing. I don't understand "however I don't know how to set parameters like you" as I thought that you were using my Qry1 as the basis. No matter how many qrys you build on top of this you'll always be prompted for that input, as you can see when you run my Qry3Final.

 

[Mand]

Right, sorry! What I had done with the date was actually enter the date that I wanted rather than 'opening date' etc. I am very new to Access and am self-taught so far, so got a bit confused!

I will try this again and see if this works!

Thank you for your patience! I think this application is amazing, and hopefully I will be able to get my boss to pay for a training course for me so that I can learn a bit more about it!

Thanks again x

 

[Mand]

Hi

Sorry to trouble you again - I now have the query working - have re-done it all and used your method.

I am just wondering how I get a 'total' column at the end? I want to add up all of the sickness absences in the date range I have chosen per employee and display it.

e.g Mrs Sarah Rayner has had 5 episodes of sickness between 01/01/09 and 21/05/09
Miss Julie Dunbar has had 3 episodes of sickness between these dates
- how do I display this (per employee), as well as displaying each start and end date for each episode?

Thanks again for all your invaluable help to a struggling newbie!

 

[WIS]

Hi

Sorry to trouble you again - I now have the query working - have re-done it all and used your method.

I am just wondering how I get a 'total' column at the end? I want to add up all of the sickness absences in the date range I have chosen per employee and display it.

e.g Mrs Sarah Rayner has had 5 episodes of sickness between 01/01/09 and 21/05/09
Miss Julie Dunbar has had 3 episodes of sickness between these dates
- how do I display this (per employee), as well as displaying each start and end date for each episode?

Thanks again for all your invaluable help to a struggling newbie!

Attached a new db (db2) with a total (qryDFAT) DateFilteredAbsenceTotal and by Employee for Date Filter.

 

[Mand]

Thank you...I had tried this, but when I ask it to show the episodes total AND the dates on the same query it all goes a bit awry...

I get one employee with their dates and then the total of all episodes next to each date. Is there a way just to have one total per employee's dates or can this only be done by form or report?

 

[WIS]

Thank you...I had tried this, but when I ask it to show the episodes total AND the dates on the same query it all goes a bit awry...

I get one employee with their dates and then the total of all episodes next to each date. Is there a way just to have one total per employee's dates or can this only be done by form or report?

If you create another qry using "qryDFAT" and "qryDFByEmployee" and join using "EmployeeID", then you have the recordsource for a rpt. As you can see, qrys can get quite complicated; tbls and qrys joined and qrys cascading ie 1 qry built using other qry(s).

Alternatively, you might have to use a Xtab qry.

 

[Mand]

thanks - yes, everything seems complicated with this program!

Your help is very much appreciated! x