Software

Ask something
Discussion Locked

Access 97 Database Relationship

0 helpful
7 replies

by mra2

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

My propesed database has employees. The Social Security # is unique.

Each employee can work for more than 1 of the 54 shops.

For shop I want to collect the pay amount for each employee.

I might also want a listing for a particular employee to show his total pay for the year from all shops that he worked in.

How do I relate these three tables CORRECTLY????

Thanks for your help!
Alan
NYC

7 replies

by chainsawz

An easy shortcut would be using the table analyzer wizard. Create a table with all the data, and then click on 'tools', 'analyze', then 'table'. Access will do the work for you.

Good luck!

by john_wills

You need a table of shops and a table of employees. You may need more than one further table, depending on your total requirement, but I suggest that your third table be of wage payments, with SSN and date as key, shop number and wages earned as data(I am assuming that an employee cannot work for more than one shop on one day; if he can, put the shop number too in the key. To enter the wages earned, construct a form which creates a new wage payment row while showing shop and employee information and accepting the amount of wages. For the annual report, I suggest you compose a form in which specifications of date range and SSN can be taken up into the WHERE clause of an OpenReport where the report opened will show totals of wages broken down as you like. The Sorting & Grouping box(accessed from the tool bar in design view) makes things fairly obvious.

by bob sellman

Here are the basic contents of each table:

Employees: SSN, name, wage rate, and any other relevant data such as job, phone, address, etc.

Shops: This is a lookup table with a code (ShopID) for each shop, the name/location of the shop, and any other pertinent information about the shop, such as the manager's name, phone number, type of work, etc.

Wages: This would be the active table, with the following type of data:
SSN
ShopID
Date of Work
Hours
Pay Amount (hours times currentwage rate which could come from the Employees table)

The Wages table might also include other data you might need to track payments received from the shop and the date it was paid. You could just add a field to each record and enter the date when the shop paid, or you also include other fields to allow for differences in payments and include the amount received and a check off box to indicate if it was settled (in case of disputes).

The relationships are:

One to many: SSN in Employees to SSN in Wages
One to many: ShopID in Shops to ShopID in Wages

Both relationships should automatically update the data in the Wages table if the SSN is changed or the ShopID is changed. Also, deletions in the Employees and Shops tables should not be allowed if there is matching data in the Wages table.

This discussion is closed to new replies.