Converting Excel to Access Database

[PlumDingo]

I am in the process of converting my jobs vacation spreadsheet for their employees to an Access database. I have a bunch of different classifications and I need to have a table that shows a summary of the hours of vacation earned, the hours carried over from the previous year and the number of hours remaining to be scheduled for this year in one table. This I call the summary table (for ex tbladmin summary). The table also includes the seniority date for the employee and the key is the last name first name. It was hard to do it by senority because in the admin classification there are three different sub classifications that I have to take their seniorirty in that sub group into account. Anyway, I am trying to have a form where the clerk can go in and input the amount of hours of vacation earned and the hours carried over from the previous year for a particular employee, and then another form where the clerk can enter the date (in terms of month and day because we can assume the year) and number of hours vacation wanted for that date, max 8 hours per day. That form should update a calender table for that month and automatically update the summary table with the amount of hours remaining to be scheduled for this year.

I got as far as creating a form to input the hours earned and creating the summary table, but when I attempt to use the form to update the hours, I get an error that says duplicate entry. I also am confused as to how to reflect a subtraction in the summary table.

I hope that this makes sense. Thanks in advance.

 

[PHV]

You may first take a look here:

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BSman]

Rather than trying to keep summary data in a table, I'd suggest that your tables should contain the necessary data to compute the "summary" data (i.e., hours earned, hours used, etc.) from the basic data tables. Summary tables actually are duplicate data and are difficult to keep properly in sync with the real, detailed, data. Grouping queries work very well to calculated the necessary totals.

Seems you should have these tables:

Employees

Jobs

JobClassifications (i.e. to identify different ways of calculating vacations)

VacationScheduled (hours to be/have been taken by each employee and the dates)

VacationEarned (hours and employee and date)


VacationEarned is intended for recording of vacation hours earned where the vacation earned is not just based on an employee's seniority date (versus the current date), such as 3 weeks for a certain number of years of seniority, but rather based on specific days or whatever that have been worked (such as for every 200 hours worked 8 vacation hours are earned). Otherwise you don't need this table. If you do use this table, then you could enter the carried over hours as of Jan 1 of the each year. Then the net vacation hours available would be the sum of the hours in VacationEarned for the year of interest minus the sum of the hours taken/scheduled for the current year in VacationScheduled.

If you don't need a VacationEarned you could have an entry for each employee in VacationScheduled that would be the number of hours carried over as of Jan 1 for each year. The carried over hours would be a negative number (since they are not hours taken, but hours "in the bank").