CODEWITHSUNDEEP
sqlms-accessduplicatesforeign-key-relationship
JustNuts
JustNuts

Reputation: 37

How can I stop 1:* relationships outputting more records than in source table (MS Access SQL)

My database is a time & billing database, recording time spent on various projects, by various users. A user can produce many time recordings "hours", a project can have many "hours" assigned to it.

I am importing these hours into a temporary table from excel, with no primary keys, and then into:

  1. The Projects table (one query just takes the project name and makes a new Project if one isn't there - working well)
  2. The Users table - (Same, left join makes new User if no match)
  3. the Hours table (many user/description combinations for a given project)
## ImportData ##
Project | User | Description
ABC Inc | RMA  | Looked at stuff
ABC Inc | JRQ  | Found something
LOL Inc | JRQ  | Looked at stuff

## Users ##
ID | Name
 1 | RMA
 2 | JRQ

## Projects ##
ID | Name    | Client
 1 | ABC Inc | 1 (McDonalds)
 2 | ABC Inc | 2 (Wendys)
 2 | LOL Inc | 1 (McDonalds)

I want to insert the ImportData hours into the Hours table, with the correct Foreign Key, but I am getting duplicates using the following code:

SELECT Users.ID, Projects.ID, ImportData.*
FROM (ImportData LEFT JOIN Employees ON ImportData.User = Users.Name) 
    LEFT JOIN Projects ON ImportData.Project = Projects.Name;

How can I force SQL to simply add the foreign keys which relates to the user/project of THAT PARTICULAR ROW, without adding more matches?

I have discovered a large amount of duplicates stem from the same project name having different clients. Perhaps I need to nest another query?

Upvotes: 2

Views: 70

Answers (3)

JustNuts
JustNuts

Reputation: 37

For those who find this, the answer was a third layer of Select queries to base my query on, which I suspect removed the scenario of one project name for multiple clients.

  1. Projects Extended - Collates details of the Project - Owner, Client
  2. ImportData Extended - Joins Projects Extended on [Project Name] and [Client Name]
  3. Actual Import query - doing the appending I originally wanted.

Thanks to those helpful people who could see what I could not

Upvotes: 0

philipxy
philipxy

Reputation: 15118

LEFT JOIN introduces rows with NULL where no match was found. Use (INNER) JOIN. Also there is no need to nest your joins.

Upvotes: 0

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

You said it yourself:

the same project name having different clients.

Forget a moment that you are trying to build a query: just look at the data and see if you can guess what should be the ProjectID for a given Projects.Name if there are multiple projects with the same name ?
I certainly couldn't begin to guess what to return.

You can't ignore the problem: until you are able to find a rule for associating the proper Projects.ID with a given ImportData, there is no solution to your issue.

My guess is that there is something else in the ImportData fields that could help you allocate the right project to a given ImportData record.
How do you know which Client a given ImportData record is for?

How would you proceed if you had to do the matching by hand?
Solve that first, then the solution will probably become evident.

Upvotes: 1

Related Questions