Covert an Excel Holiday spreadsheet design into an Access Database version (1 Viewer)

[djspod]

Hi All,

I would like to convert this excel spread sheet to an access database, At the moment there is a tab for each employee using the same template on the attached screenshot.

How would I convert this into a normalised Access Database, Which and how many tables would I need, IE Employee table with EmpID, HolidayBooking table etc

Would the structure be something like a Holiday booking table, Fields of: BookingID, EmpID, Holiday 1, Holiday 2, Holiday3 etc. and a Staff Id table of names, Basic holidays per year information and job role.

I have played around with it but I can't seem to get the right logic flowing.

Ideally I would like the user form to look similar to the spread sheet version and also print off an report of an similar design.

I have a reasonable level of knowledge of access reports, Table,Macros and queries.

It's the Table foundation structure I'm not sure which way to approach it

Any help would be useful.


Thanks

 

[JHB]

You need a table for:

• Master data for the Employee.
• Number of vacation days per year.
• Held vacation days - date from / to
• Days in leiu and a table when they are used, (if you don't want them into the table of Held vacation days.

The rest you show in the printscreen can be calculated from the data into the tables above.

 

[djspod]

Hi,

Thanks for your help.

To help identify each DIL, I was thinking of the lines of:

Field names:
GoodFriday1
GoodFriday2
EasterMonday1
EasterMonday2
Mayday1
Mayday2

Etc., Etc.

The reason for 2 for each is that all employees have the choice of:

Trebletime for working a Bank Holiday or Normal Time + 2 DIL's or Double Time pay and 1 DIL.

They can request either 1 or 2 days per Bank Holiday or none and opt for Treble Time.

As each Bank Holiday comes around we place a sheet for all names on the sheet and the choices above for them to make their choice.

Its the Team Manager job to enter this into each invidual user file to indicate that they accrued a DIL or 2 on that date and later on down the line a date when that DIL was used.

I'm guessing the next table (Or in the same table)

Therefore Would the fields that be for used something like:

GFUsed1
GFUsed2
EMUsed1
EMUsed2

and so on. (To tie each together and to the employee)

A master Staffid/Employee table record would have to link to each Holiday booking record in the "Held days used table" and then also link to the accrued and used records.

In my head this sounds complicated in Access form but I believe this achievable.

The end result is as each holiday is booked or DIL accrued a report can be printed for the employee.

Also at the time of booking the holiday, Visibility of what days they have to use.

Thanks again for the reply.

 

[JHB]

...
To help identify each DIL, I was thinking of the lines of:

Field names:
GoodFriday1
GoodFriday2
EasterMonday1
EasterMonday2
Mayday1
Mayday2

Etc., Etc.
..

I'm guessing the next table (Or in the same table)

Therefore Would the fields that be for used something like:

GFUsed1
GFUsed2
EMUsed1
EMUsed2

and so on. (To tie each together and to the employee)
..

Sorry - but the table design you want to create is wrong for a database, (in a spreadsheet would it be okay, but not in a database).
You need to change your mind from thinking horizontal to vertical and what you would use as fieldnames should be put in as data!
So the DIL table would be something like below.

 

[djspod]

Thanks for help and putting me on the right track!

I think my brain had been brainwashed by Excel to think horizontal

What do you think of this? Better?

 

[JHB]

..
I think my brain had been brainwashed by Excel to think horizontal

Yes and also the tick with storing calculate values!
TotalforYears no need for storing that value, it is Basic- + carriedover!

..What do you think of this? Better?

Yes better, but the Basic- + carriedover should go in a table on its own, then next year I don't think a person carrie over the same amount of days also the Basic- could change!
I would also if I where you, have a look at Holidaysbooked I think it could be done smarter, but it is up to you, you know your business!