payroll table design

Posted on 2004-11-03
Last Modified: 2011-10-03
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 :]
Question by:saskya75
    LVL 8

    Expert Comment

    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....

    Author Comment

    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?
    LVL 8

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now