[Last Call] Learn how to a build a cloud-first strategyRegister Now


payroll table design

Posted on 2004-11-03
Medium Priority
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
  • 2

Expert Comment

ID: 12483947
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

ID: 12495768
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?

Accepted Solution

JonoBB earned 1500 total points
ID: 12496518
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.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

834 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