Results 1 to 7 of 7
  1. #1
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83

    Relaionships

    Hi Guys

    I am having a problem with my Payroll relationships. I have set up the tables and the relationships. My query only works for one employee. I think my relationships are dodgy. Please can you have a look at my relationships and advise.

    TableRelationships

    Click image for larger version. 

Name:	Payroll Table Relationships.jpg 
Views:	21 
Size:	62.8 KB 
ID:	31194

    Query Relationships

    Click image for larger version. 

Name:	Payroll Query Relationships.jpg 
Views:	16 
Size:	96.9 KB 
ID:	31196


    Click image for larger version. 

Name:	Payroll Query Relationships.jpg 
Views:	16 
Size:	16.8 KB 
ID:	31195

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what does 'My query only works for one employee.' mean? what is the expected outcome? what is the actual outcome?

    The only thing I can see different is the relationships don't match between the two diagrams in any way

    And why do you have employeeid_fk and payfrequecy in the employee table?

    You would make things simpler for yourself if you had a consistent naming convention - how is someone supposed to know that a field called employeeid in one table is called salaryid in another

    edit: hit the save button in error

    I would suggest your employee table does not require the employeeid_fk and payfrequency fields

    the payrollsalary table should have the salaryid_fk field renamed employeeid_fk

    The other two tables need changing as well, but it is not clear what they are supposed to do so cannot advise. The issues are you are joining pk's to pk's which implies a one to one relationship - in which case I would expect them to be one table

    There are potentially other issues - what happens if an employee changes position? or the rates change?

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    As Ajax has said, either your naming convention is awful or your linking of the tables is way off.
    You should never have circular paths in the main table relations.

    Assuming your database is called payroll - adding Payroll to the front of every table is unnecessary and confusing.
    It should be tblEmployees, tblSalary etc.
    Why have you got a matching fk field for the pk field in every table ??

  4. #4
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    Thanks for the information guys. I am sorry But I am very new. I have tried to clean up the tables as best I know and also tried to create the relationships but it seems as if I am not willing. I will paste what I have done so you can see. Please can you make some suggestions on how to make this work. If you need me to sent the DB so you can physically see it, I do not have a problem.

    Payroll Query relationship..(I know is incorrect because no information at all populates the query)

    Click image for larger version. 

Name:	Payroll Query Relationships.jpg 
Views:	16 
Size:	120.1 KB 
ID:	31202

    Database Table relationship (Must also be very incorrect as it is the same as the query relationships)

    Click image for larger version. 

Name:	Payroll Table Relationships.jpg 
Views:	14 
Size:	61.7 KB 
ID:	31203


    Apologies guys but I am stumped and I am just trying to learn from you

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    The first thing which hurts the eye - you pay salaries to salary table not to employee?

    Rename the table PayrollPayrollTbl to tblEmployeePayroll, and link it with PayrollEmployeeTbl (which will be more conventional when renamed to tblEmployees).

    When employees salary never changes, then you can calculate monthly salary based on working time in tblEmployeePayroll and rates in tblEmployeeSalary. When the rates can change, then read the current rate value from tblEmployeeSalary into tblEmployeePayroll (p.e. using combo with single column), and make further calculations only based in tblEmployeePayroll. Anyway, look at the table tblEmployeeSalary as some kind of registry - you can relate it to tblEmployees, but that's all. Anyway, no need to estabilish relationship between tblEmployeePayroll and tblEmployeeSalary.

  6. #6
    Peter Simpson is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    South Africa
    Posts
    83
    Thank you Guys....you are all my Hero's

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Might I suggest some homework reading before you delve further into your db?
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums