query output issue

[thegrimmerdiscovery]

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;

[rpeare]

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.

[thegrimmerdiscovery]

Thanks for the guidance. Appreciated.

[orange]

Can you show us your table designs and any relationships?