Software·43,777 discussions

Access 97 Database Relationship

Locked

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

Topic

Access 97 Database Relationship

In reply to Access 97 Database Relationship

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!

Access 97 Database Relationship

In reply to Access 97 Database Relationship

Thanks

Access 97 Database Relationship

In reply to Access 97 Database Relationship

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.

Access 97 Database Relationship

In reply to Access 97 Database Relationship

Good Answer Thanks!

Access 97 Database Relationship

In reply to Access 97 Database Relationship

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.

Access 97 Database Relationship

In reply to Access 97 Database Relationship

Great Answer!!! Works too!!! Thanks for all your help!

Access 97 Database Relationship

In reply to Access 97 Database Relationship

This question was closed by the author

[Author]

Replies