Results 1 to 6 of 6
  1. #1
    Bkper087 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Posts
    81

    Duplicate Records - Relationship Issue or Query Issue?


    I am trying to create a hiring and staff management database that includes departments, grade levels, jobs, etc. Because of the way my db is built I found it necessary to create separate tables and join some information using queries in advance. Those queries are populating as they should. The associated base salary shows up for the associated job based on the grade, etc. I now have three queries I want to join: HiredStaffQ, JobQ, DepartmentQ. I can create a query based on just the HiredStaffQ without issue. However, as soon as I add the JobQ or DepartmentQ and run it, the result creates many multiples of the same data. I have tried adjusting the join types and the results are still the same. Any ideas on what might be the issue?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    depending on how you have linked your tables you will get multiples if any of the relationships between your three queries is more than 1 to 1

    For instance if you have on record for each staff in HIREDSTAFFQ, but have 3 records in JOBQ and 2 records in DEPARTMENTQ you will, in all probability end up with 6 records for the single employee, however you could also end up with 3 records or 2 records as well there is no telling based on what you've provided.

    If you want to end up with 1 record per person you have to limit the queries that are showing multiple records per person to a single item (for instance their most recent position, rather than the one they were hired into, likewise, their most recent department, rather than all the departments they have served in).

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If HiredStaffQ has 1-to-many or many-to-many relationship with each of the other two tables, the consequences of all 3 tables in same query will be 'duplication' or if there is no relationship and therefore no JOIN clause, same result.

    A report/subreports arrangement may be needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Bkper087 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Posts
    81
    At this point every candidate should only have 1 record. Upon further investigation the query is joining the candidates name for every job title in the job title look-up table. I'm sure this is a simple issue of relationships being incorrect, but I'm not sure how to fix it. Would this help?

    SELECT [6HiredStaff_DepartmentJobTitle].FirstName, [6HiredStaff_DepartmentJobTitle].LastName, [6HiredStaff_DepartmentJobTitle].JobTitle, [2JobTitleGradeBenifit].JobBenifit_Grade, [2JobTitleGradeBenifit].JobBenifit_BaseSalary
    FROM 6HiredStaff_DepartmentJobTitle, 2JobTitleGradeBenifit;

    Here is a screenshot of my relationships: https://drive.google.com/file/d/0Bx9...ew?usp=sharing

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Note that in my previous post I stated a query with no JOIN clause will cause 'duplication'. Your query results in a Cartesian relationship. Every record of each table will join to every record of other tables.

    I can't download your image now unless you attach it to post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    wow no wonder you're getting a ton of duplicates. cartesian queries have very limited use, and this isn't one of them

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

Similar Threads

  1. Table Relationship Issue
    By excellenthelp in forum Access
    Replies: 2
    Last Post: 08-05-2014, 11:20 AM
  2. Issue with one to many relationship
    By ryancgarrett in forum Access
    Replies: 2
    Last Post: 08-06-2013, 06:15 AM
  3. Possible Relationship Issue?
    By Bill Sperry in forum Forms
    Replies: 7
    Last Post: 03-20-2012, 11:29 AM
  4. Relationship issue
    By Calgar99 in forum Access
    Replies: 4
    Last Post: 12-14-2011, 07:36 PM
  5. Relationship screen Issue!
    By Lincoln in forum Access
    Replies: 8
    Last Post: 07-14-2011, 04:16 AM

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