Results 1 to 4 of 4

query output issue

  1. #1
    thegrimmerdiscovery is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    17

    query output issue

    Hi.

    Iíve got a query output issue I'd appreciate support with.

    The query (see SQL below) matches up the personís name(PersonID) with their job role (JobTitle) and in which area they work (eitherSevenServices or WestandNorth).



    This appeared to work until two people were given the samejob title of Service Director in SevenServices. While these two now show up thequery output correctly, it has gone wrong in WestandNorth where the one personwith the role of Service Director now appears twice. I suspect this is Database 101. But if anyone could point mein the right direction it would be appreciated. Been puzzling over this on andoff for a couple of days.

    SELECT DISTINCT tblJobTitles.JobTitle, tblSevenServices.PersonID,tblWestandNorth.PersonID
    FROM (tblJobTitles INNER JOIN tblSevenServices ONtblJobTitles.JobTitle = tblSevenServices.JobTitle) INNER JOIN tblWestandNorthON tblJobTitles.JobTitle = tblWestandNorth.JobTitle;


  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,438
    the reason it's appearing twice is you have two different tables for two different locations. This would be a non-normalized structure.

    You should actually have a single table storing the positions and an extra data entry field storing the location

    Secondly, you will have a number of records equal to the maximum number of people assigned to a specific facility. i.e. if you have 4 engineers at one facility and 1 engineer at another you're going to get 4 records in your query result and the facility where there is only one person filling the position will appear 4 times. That's mostly due to the construction of your query.

    You could look at a method to concatenate the names of your people into a single field if multiple people hold the same position at any given facility. That's another solution.

  3. #3
    thegrimmerdiscovery is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    17
    Thanks for the guidance. Appreciated.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102
    Can you show us your table designs and any relationships?

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

Similar Threads

  1. Replies: 5
    Last Post: 01-08-2015, 02:08 PM
  2. Replies: 4
    Last Post: 04-23-2014, 04:35 PM
  3. Replies: 4
    Last Post: 12-30-2013, 05:51 PM
  4. How to query for this output?
    By seemi143 in forum Queries
    Replies: 1
    Last Post: 12-04-2013, 02:37 AM
  5. Output Query to Text
    By denileigh in forum Queries
    Replies: 1
    Last Post: 05-27-2006, 12:34 PM

Posting Permissions

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