Joining 3 or more tables without duplicating entries in MS Access

Question

I am currently practicing my Access Skills and would appreciate any help with the below issue.

The thing is that I am trying to combine 3 tables into one. I am using one of them as a Master table (Fact Table) and the other 2 tables have extra information for the fact, however the information is the same but coming from different sources.You can see an example below (i am still figuring out stackoverflow so i am sorry for the poor styling)

Fact table

Name     Age    Gender      
Nordon   21     male

ExtraInfo

Name     Skills         Level
Nordon   Programming    Good
Nordon   Singing        Poor
Nordon   Drawing        Good

ExtraInfo_2

Name     Skills         Level
Nordon   Programming    Good
Nordon   Singing        Good
Nordon   Drawing        Poor

When i try to do a statement with Inner Join in Access, I get something like this:

Results

Name    Age   Gender    Skills_1      Skills_2     Level_1  Level_2
Nordon  21    male      programming   programming  Good     Good
Nordon  21    male      programming   Singing      Good     Good
Nordon  21    male      programming   Drawing      Good     Poor
Nordon  21    male      Singing       programming  Poor     Good
Nordon  21    male      Singing       Singing      Poor     Good
Nordon  21    male      Singing       Drawing      Poor     Poor

Basically it is mapping a Skill from table 1 to each Skill in table 2. And I was expecting something like this:

Results

Name    Age   Gender    Skills_1      Skills_2     Level_1  Level_2
Nordon  21    male      programming   programming  Good     Good
Nordon  21    male      Singing       Singing      Poor     Good
Nordon  21    male      Drawing       Drawing      Good     Poor

Basically, I want to then check for inconsistencies, but I will do this on my own. Sometimes the tables Skills_1 and Skills_2 also have a different amount of entries per fact. Is there a way to awesome deal with this?

All help is appreciated!

Can't add comment yet. [This](https://stackoverflow.com/questions/7854969/sql-multiple-join-statement) might be helpfull. — Reinis Verbelis, Mar 12 '18 at 13:45
I was also reading it, and mentioned it below, but thank you! — VBABegginer, Mar 12 '18 at 13:54

Reinis Verbelis · Answer 1 · 2018-03-12T15:04:30.447

Maybe you could try something like this:

SELECT t4.Name, t4.Age, t4.Gender, t4.Skills AS Skills_1, t4.Level AS Level_1, ei2.Skills AS Skills_2, ei2.Level AS Level_2 FROM ( SELECT t3.Name, t3.Age, t3.Gender, ei1.Skills, ei1.Level, t2.Skills AS SkillsAll FROM ( SELECT t1.*, t2.Skills FROM Fact t1 INNER JOIN ( SELECT Name,Skills FROM ExtraInfo UNION SELECT Name,Skills FROM ExtraInfo_2 ) t2 ON t1.Name=t2.Name ) t3 LEFT JOIN ExtraInfo ei1 ON t3.Name=ei1.Name AND t3.Skills=ei1.Skills ) t4 LEFT JOIN ExtraInfo_2 ei2 ON t4.SkillsAll=ei2.Skills AND t4.Name=ei2.Name;

Hey R. Verbelis, I have already tried your method, but then this way you might miss some skills, in case Table 3 has more skills than Table 2, right? — VBABegginer, Mar 12 '18 at 12:56

Richard Hansell · Accepted Answer · 2018-03-12T13:55:08.847

0

There's lots of different ways to do this, but using INNER JOINs I am going to take guess that you simply didn't include a link between the two Extra Info tables on the skill name? In which case something like this should work:

SELECT
    f.Name,
    f.Age,
    f.Gender,
    ei1.Skills AS Skills_1,
    ei2.Skills AS Skills_2,
    ei1.Level AS Level_1,
    ei2.Level AS Level_2
FROM
    Fact f
    INNER JOIN ExtraInfo ei1 ON ei1.Name = f.Name
    INNER JOIN ExtraInfo_2 ei2 ON ei2.Name = f.Name AND ei2.Skills = ei1.Skills

If you have potential gaps in the Extra Info tables then you would need to use LEFT JOINs instead, and this becomes trickier, as that join condition would stop working where ExtraInfo_2 is related to ExtraInfo. In this case you would need to use a subquery, so something like this:

SELECT
    f.Name,
    f.Age,
    f.Gender,
    ei1.Skills AS Skills_1,
    ei2.Skills AS Skills_2,
    ei1.Level AS Level_1,
    ei2.Level AS Level_2
FROM
    ((Fact AS f
    LEFT JOIN (
        SELECT Name, Skills FROM ExtraInfo
        UNION
        SELECT Name, Skills FROM ExtraInfo_2) AS q ON q.Name = f.Name))
    LEFT JOIN ExtraInfo AS ei1 ON ei1.Name = f.Name AND ei1.Skills = q.Skills)
    LEFT JOIN ExtraInfo_2 AS ei2 ON ei2.Name = f.Name AND ei2.Skills = q.Skills

(That syntax might not be 100% for MS Access, but it should be close to this?)

If your data is really as it looks, and you sometimes just have more rows in "Extra Info" for some people then that should be fine.

edited Mar 12 '18 at 13:55

answered Mar 12 '18 at 13:02

Richard Hansell

5,315
1
16
35

Hey Richard, I really appreciate the long and full answer! I tried it and I keep getting the following error: "Syntax error (missing operator) in query expression 'q.Name = f.Name LEFT JOIN ExtraInfo ei1 ON ei1.Name = f.Name'. " Do you by chance know the issue? In case I skip the last two Left Join's I am getting the correct amount of Skills, but they are just not per Source (per table). – VBABegginer Mar 12 '18 at 13:34
Yes, I had a feeling Access wouldn't like that syntax, I'll have a dig around and see if I can find a copy to spin up somewhere... – Richard Hansell Mar 12 '18 at 13:40
In case you have the time and find something it would be perfect and highly appreciated! Your answer is awesome by the way, thank you! Until now i just tried different variation of joins (left or inner) however i kept getting the wrong mappings – VBABegginer Mar 12 '18 at 13:43
This could be a number of things I guess, it could be because `Name` is a reserved word, or it could be the fact that Access requires the `AS` statement (so I added this to my answer above)? I found a link to an example that works sort of similarly to this, so that might be worth checking? https://dba.stackexchange.com/questions/73659/join-on-microsoft-access-derived-table-not-working – Richard Hansell Mar 12 '18 at 13:47
In MS Access, multiple `JOIN` clauses require parenetheses around paired tables. – Parfait Mar 12 '18 at 13:49
I thought it has to do something with the amount of Joins, so I was checking this example: https://stackoverflow.com/questions/7854969/sql-multiple-join-statement. But I will also have a look at what you shared! – VBABegginer Mar 12 '18 at 13:49
I tried the following : ((Fact AS f LEFT JOIN ( SELECT Name, Skills FROM ExtraInfo UNION SELECT Name, Skills FROM ExtraInfo_2) AS q ON q.Name = f.Name) LEFT JOIN ExtraInfo AS ei1 ON ei1.Name = f.Name AND ei1.Skills = q.Skills) LEFT JOIN ExtraInfo_2 As ei2 ON ei2.Name = f.Name AND ei2.Skills = q.Skills. However now I am getting an error saying that "JOIN expression not supported" – VBABegginer Mar 12 '18 at 13:50
Wow! Reminds me why I chose to forget most the MS-Access SQL I ever learned!! I guess that makes some sort of sense though, as it removes any level of ambiguity (not that there should be any in the first place though). – Richard Hansell Mar 12 '18 at 13:52
Because of MS Access's rule on this, it is advised for beginners to the dialect to build complex queries with the Query Designer. – Parfait Mar 12 '18 at 13:52
@VBABegginer ... the reason that error persists is because the *q* subquery should be the first listed table in `FROM` as the other joins pair with it and not with *Fact*. Again, begin with one join and then add the others with Query Design. Then see generated SQL. – Parfait Mar 12 '18 at 13:54
@VBABegginer I think you are missing a closing `)` after `q.Name = f.Name))`? I updated my answer again, to have the bracketing I think will be needed... wish I had somewhere to test this!! – Richard Hansell Mar 12 '18 at 13:56
Hey Richard, i tried also your version with the brackets but I keep getting the same error about JOIN Expression not supported.. I also added the AS's. I will try this thing with Query Design to see what will happen.. Guys I really appreciate all the tips and help :) in case you have also other ideas please share! – VBABegginer Mar 12 '18 at 14:01
I marked it guys, just in case I get carried away. In case I manage to figure it out I will add a comment and edit if you dont mind – VBABegginer Mar 12 '18 at 14:04
Cool, I can't find a copy of MS-Access at work, but I'm pretty sure I have it at home. I will take a check when I get back to see if you managed to get this resolved, otherwise I will have another stab. Do post your solution if you find one, as it will hopefully help others :D – Richard Hansell Mar 12 '18 at 14:06
Thank you :) I tried also Query Design and i started giving me the same error once I added a third join .. will try other things as well – VBABegginer Mar 12 '18 at 14:12

Parfait · Answer 3 · 2018-03-12T15:13:05.593

0

Consider matching on correlated count subqueries but will have a footprint with large tables:

SELECT agg1.Name, agg1.Age, agg1.Gender, agg1.Skills AS 1, agg1.Level AS Level_1,
       agg2.Skills AS 2, agg2.Level AS Level_2
FROM 
   (SELECT f.Name, f.Age, f.Gender, e1.Skills, e1.Level, 
           (SELECT Count(*) FROM ExtraInfo sub
            WHERE sub.Name = e1.Name AND sub.Skills <= e1.Skills) As rank
      FROM Fact f INNER JOIN ExtraInfo e1 ON f.Name = e1.Name
   )  AS agg1 

LEFT JOIN 
  (SELECT f.Name, f.Age, f.Gender, e2.Skills, e2.Level,
          (SELECT Count(*) FROM ExtraInfo sub
           WHERE sub.Name = e2.Name AND sub.Skills <= e2.Skills) As rank
      FROM Fact f INNER JOIN ExtraInfo2 e2 ON f.Name = e2.Name
   )  AS agg2 ON (agg1.rank = agg2.rank) AND (agg1.Name = agg2.Name);


-- Name    Age  Gender  Skills_1     Level_1    Skills_2      Level_2
-- Nordon   21  male    Drawing      Good       Drawing       Poor
-- Nordon   21  male    Singing      Poor       Singing       Good
-- Nordon   21  male    Programming  Good       Programming   Good

edited Mar 12 '18 at 15:13

answered Mar 12 '18 at 14:21

Parfait

104,375
17
94
125

Woah, Parfait, can you please explain it a bit more this matching based on count ? Correct me if I am wrong but with the inner join you would miss some skills, in case one of the tables has more entries. – VBABegginer Mar 12 '18 at 14:53
Subquery calculates a rank 1, 2, 3, for each distinct *Skill* in both *ExtraInfo* and *ExtraInfo_2* tables by alphabetical order. And yes, previous solution would leave out skills of larger table. See edit with `LEFT JOIN` to keep all rows of second table (if first can be as large as well, look into Full Outer Join). – Parfait Mar 12 '18 at 15:14
Basically you need to align ordering of skills and not skills themselves for your output. Thinking now on your needs, this is a database design problem. Consider combining both *ExtraInfo* and run report from there. – Parfait Mar 12 '18 at 15:15
maybe a stupid question, but what do you base the rank on? You have count, so you count how many times it appears? I am still a bit new and I might have gotten lost here – VBABegginer Mar 12 '18 at 15:22
As mentioned by alphabetical order of skill name. The correlation is between subquery and outer query: `sub.Skills <= e1.Skills`. You are counting how many rows are alphabetically lower than current row's skill name. – Parfait Mar 12 '18 at 15:44

Joining 3 or more tables without duplicating entries in MS Access

3 Answers3