Record Sick Leave via access

[jzwp11]

Question how did you get the pk officerID and TransID and fkOfficerID not to display in the main form

You can hide a control on a form by changing it's visible property to No.

[Nixx1401]

Hi there

I did some cosmetics to the database but on completion I realise that when a new officer is added the Current Allocation, Total Days Taken This Year and Remaining Days are not populating immediately you have to close or switch to design view of the frmOfficerInfo form for that info to populate. Why is this?

You must enter an effective date when entering a new officer for the the above data to also populate?

I have attached the database once again.

Thank you very much in advance you have been a life saver

Nixx

[jzwp11]

Actually, if you enter a new transaction or a new status for an existing officer, the controls in the main form would not update accordingly (as well as when entering a new officer's info). To correct this, I had to add some code to the after update event of both subforms. I also noticed that the controls did not update even when a record was deleted in either of the two subforms, so I had to added the same code in the on delete event of both subforms as well. I've attached the amended database.

[Nixx1401]

Once again Thanks a million. Its perfect

[jzwp11]

You're welcome!

[Nixx1401]

It hurts to bother you once again after how much you have helped me but now that I was planning to install the database on the HR and PO computer they now realise that there is one condition they failed to mention that is

If a Temporary officer is allocated 14 days at the beginning of the year and their status changes at some point during the year and they have used some the days from the 14 days allocated at the beginning of the years on status change the days used are deducted from the 21 days now being allocated.

eg temporary as of 1999-01-12 with 14 days being allocated yearly
on 2010-03-01 he took 7 sick days and was appointed on 2010-08-01 therefore he now has 7 days balance from from the 14 days allocated at the beginning of 2010 he is now awarded an additional 7 days which gives him a total of 14 days for the balance of the year.

If this new condition is too complicated to make the necessary adjustments I understand

thx for your time

[jzwp11]

If you add a new status record (do not change the existing one) with the new status and new effective date, then the number of days allocated (and the remaining balance) will update automatically. I did not know if you prorated the additional allocated days depending on when the status change occurred. For example, if a person was temporary and then changed to appointed on July 1st of the current year would they be entitled to the full 7 days additional allocation or would they only be eligible for 3.5 days?

[Nixx1401]

If a person was changed from temporary to appointed on July 1st of the current year that person is allocated an additional 7 days which gives them a total of 21days. If that person used any of the 14 days allocated at the beginning of the year before july 1st the balance of the 14 days is added to the 7 days.

Jan 1st 14days
Mar used 5days
Balance 9days
July 1st 7days allocated because of appointment
Remaining Days 16 days

hope this help

[jzwp11]

The database as is should handle what you described.

[Nixx1401]

Yes Thx

Have a good weekend

[Nixx1401]

Hi there

Its me once again now that I have submitted that database I was once again given the task of creating the database to record all types of leave. Actually they loved it so that Is why I am at this once again
I've started but I need some advice concerning some queries and also attempted to make things alot easier because i did not know how to make the necessary changes to the formulaes created with the change in status

Can you please indicate If I have the right approach or should i consider creating these queries different and how should handle the status because an temporary officer is allocated 21 days vacation and 14 days sick whilst the appointed is allocated 28 days vacation and 21 days sick

Any help would be appreciated

The query names and objectives

qryTotalNumberStaffOnVacation - Total number of staff on vacation
qryTotalMonthlyVacationAccumulated - Total number of vacation days accumulated for the month
qryTotalOfficerOnVacation - Number of Officers on vacation
qryTotalCivilianOnVacation - Number of civilians on vacation

Queries werent generated for the these below :

Average number of staff on vacation per day
Peak day and no of persons on vacation monthly
Minimum day and no of person on vacation monthly

Now I created queries within queries to achieve the above and was hoping to incorporate the queries in one report.

Do you think this is my best option. I will attach the database for viewing.

[jzwp11]

Since you now have multiple types of leave with varying allocations depending on the status we have a couple new relationships that need to be handled: one status can have many types of leaves and a type of leave is associated with many statuses. When you have two one-to-many relationships between the same two tables, you need a junction table to handle them.

We need to modify tblStatus by removing the allocation field.

We need a table to relate the status and the leave type, and based on the combination, assign the correct allocation. This is the junction table:

tblStatusLeaveType
-pkStatusLeaveTypeID primary key, autonumber
-fkStatusID foreign key to tblStatus
-fkLeaveTypeID foreign key to tblLeaveTypes
-longAllocation

I went ahead and added the new table, modified you frmStatus to include a subform based on the junction table and put in the allocation data as you described. The revised database is attached.

With the new structure, everything done previously will be impacted.

I'll let you review the attached before moving forward.

[Nixx1401]

I've reviewed the addtional forms and tables added but this database design is beyond my scope because I've never heard of the above

I trust your judgement. You can proceed.

[jzwp11]

I did notice that you had a lookup for the job title in your officer info table. Having lookup fields at the table level is not a good idea; see this site for more details as to why it is not good. (I noticed that the site was down at present; hopefully it will be back up soon). I created a table to hold the job titles and modified your data and all related forms and queries. The amended DB is attached.

Can you please indicate If I have the right approach or should i consider creating these queries different and how should handle the status because an temporary officer is allocated 21 days vacation and 14 days sick whilst the appointed is allocated 28 days vacation and 21 days sick

The allocated leave time for a person does not come into play when you are counting how much time was actually taken off in a period, so I am not really sure what you are asking.

In reviewing these queries, is your ultimate goal to gather statistics for a time period? If so, in most of the queries you have a date criteria of >=date() which will only look at records from today and beyond, so if it late in the month you are essentially ignoring everything earlier then today. So I need to understand what you really want by these queries. I am thinking that you need to specify a beginning and ending date.

[Nixx1401]

I did not think of that condition you indicated with using >=Date(). Basically i would like to generate the following stats

Total number of staff on vacation
Total number of vacation days accumulated for the month
Total number of officers on vacation
Total number of civilians on vacation
Average number of staff on vacation per day
Peak day and number of persons (Day with the most persons on vacation and total for that day)
Minimum day and number of persons (day with least persons on vacation and total number for that day)

Accumulated Sick days for the month
Number of officers on sick leave
Number of civilian on sick leave
Number of officers on excessive sick leave for the month
Number of civilians on excessive sick leave for the month
Average number of sick per day
Peak sick day and number of persons
Minimum sick days and number of persons

That is the stats I would like to generate. You can delete the queries that you wouldn't use.

Thanks once again. I am a new IT Officer and I am being approached about alot of databases to solve lots of problems constantly but I am not too versatile in database management. So your help is very much appreciated.