General discussion

  • Creator
  • #2322249

    Access 97 Database Relationship


    by mra2 ·

    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!

All Comments

  • Author
    • #3404936

      Access 97 Database Relationship

      by chainsawz ·

      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!

    • #3416927

      Access 97 Database Relationship

      by john_wills ·

      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.

    • #3398376

      Access 97 Database Relationship

      by bob sellman ·

      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:
      Date of Work
      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.

    • #3402657

      Access 97 Database Relationship

      by mra2 ·

      In reply to Access 97 Database Relationship

      This question was closed by the author

Viewing 3 reply threads