payroll table design

Hi all!

I need to develop a payroll database using MSAccess but without VBA.

The database should let me enter employee details and time sheet info, and then calculate the amount due for each employee for a range of working days.  The payroll should store the payment details, issue payment slips and also generate reports on the total amount of wages paid for a range of working days.  
So far I managed to create two tables

EMPLOYEE(EmpID, Name, Position, Rate)
TIME(TimeID, EmpID, Date, TimeIn, TimeOut)

and generated queries to calculate the amount due for each employee given the first and last working day(entered using parameters).  However, when i tried to add another table to store payment details I got stuck.  I added a new table

PAYMENT(PaymentID, EmpID, TimeID, Payment Date, Date From, Date To, Hours Worked, Amount Paid, Payment Type, Cheque No)

and used the following onetomany and manytoone relationship

Employee ---------------------------<- Payment ->-------------------Time


Somehow i can't manage to calucate the hours worked using the expression builder, even though i managed to calculate it before using a query.  I'm not that certain either of the table design... can someone help please :]
Thanks
saskya75
saskya75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JonoBBCommented:
First of all, whilst it is possible to create an access database without VBA, thats like eating pizza with any toppings....its edible but tastes horrible.

To get you started, you could probably drop the Time table and just use the Employee and Payment tables
with a one to many relationship between Employee.EmpID and Payment.EmpID

You should then be able to calculate the time worked for any specific employee

As a general rule as well, try not to leave any spaces between your field names, so change Date From to DateFrom, etc....
0
saskya75Author Commented:
Hi JonoBB
Thanks for your comments and useful tips.  I know its easier to build the database once u know VBA but by the time i learn it my assignment deadline will be due :]
I dropped the TIME table and worked with only EMPLOYEE and PAYMENT but i'm still finding the same problems as before.  I need to design a form which displays employees and their respective payment details.  The user should enter the PaymentDate, DateFrom and DateTo etc ... while HoursWorked and AmountDue are calculated automatically.  I'm almost there but somehow a #Name error is being displayed for the calculated fields.  I'm using an expression builder but somehow i feel i should be performing a query which will select the particular empid from TIME and get those dates which match DateFrom and DateTo...  Is this possible?
Thanks
saskya75
0
JonoBBCommented:
Yup, you are quite correct - you need to open up the relationships (Tools>Relationships) and create a link between Employee.EmpID and Payment.EmpID. You do this by dragging the field name from one table to another.

Then, create a new query that includes both tables and all field from both tables. Call it qryEmployee (or anything else that you want)

Then, in your form, set the recordsource to qryEmployee

That should be it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.