CODEWITHSUNDEEP
sqlms-accessms-access-2010
Pri2304
Pri2304

Reputation: 11

Number of Records don't match when Joining three tables

Despite going through every material I could possibly find on the internet, I haven't been able to solve this issue myself. I am new to MS Access and would really appreciate any pointers. Here's my problem - I have three tables

  1. Source1084 with columns - Department, Sub-Dept, Entity, Account, +few more
  2. R12CAOmappingTable with columns - Account, R12_Account
  3. Table4 with columns - R12_Account, Department, Sub-Dept, Entity, New Dept, LOB +few more

I have a total of 1084 records in Source and the result table must also contain 1084 records. I need to draw a table with all the columns from Source + R12_account from R12CAOmappingTable + all columns from Table4.

Here is the query I wrote. This yields the right columns but gives me more or less number of records with interchanging different join options. 

 SELECT rmt.r12_account,
       srb.version,
       srb.fy,
       srb.joblevel,
       srb.scenario,
       srb.department,
       srb.[sub-department],
       srb.[job function],
       srb.entity,
       srb.employee,
       table4.lob,
       table4.product,
       table4.newacct,
       table4.newdept,
       srb.[beg balance],
       srb.jan,
       srb.feb,
       srb.mar,
       srb.apr,
       srb.may,
       srb.jun,
       srb.jul,
       srb.aug,
       srb.sep,
       srb.oct,
       srb.nov,
       srb.dec,
       rmt.r12_account
FROM   (source1084 AS srb
        LEFT JOIN r12caomappingtable AS rmt
               ON srb.account = rmt.account)
       LEFT JOIN table4
              ON ( srb.department = table4.dept )
                 AND ( srb.[sub-department] = table4.subdept )
                 AND ( srb.entity = table4.entity )
WHERE  ( ( ( srb.[sub-department] ) = table4.subdept )
         AND ( ( srb.entity ) = table4.entity )
         AND ( ( rmt.r12_account ) = table4.r12_account ) );

Upvotes: 1

Views: 146

Answers (3)

HansUp
HansUp

Reputation: 97131

In this simple example, Table1 contains 3 rows with unique fld1 values. Table2 contains one row, and the fld1 value in that row matches one of those in Table1. Therefore this query returns 3 rows.

SELECT *
FROM
    Table1 AS t1
    LEFT JOIN Table2 AS t2
    ON t1.fld1 = t2.fld1;

However if I add the WHERE clause as below, that version of the query returns only one row --- the row where the fld1 values match. 

SELECT *
FROM
    Table1 AS t1
    LEFT JOIN Table2 AS t2
    ON t1.fld1 = t2.fld1
WHERE t1.fld1 = t2.fld1;

In other words, that WHERE clause counteracts the LEFT JOIN because it excludes rows where t2.fld1 is Null. If that makes sense, notice that second query is functionally equivalent to this ...

SELECT *
FROM
    Table1 AS t1
    INNER JOIN Table2 AS t2
    ON t1.fld1 = t2.fld1;

Your situation is similar. I suggest you first eliminate the WHERE clause and confirm this query returns at least your expected 1084 rows.

SELECT Count(*) AS CountOfRows
FROM   (source1084 AS srb
        LEFT JOIN r12caomappingtable AS rmt
               ON srb.account = rmt.account)
       LEFT JOIN table4
              ON ( srb.department = table4.dept )
                 AND ( srb.[sub-department] = table4.subdept )
                 AND ( srb.entity = table4.entity );

After you get the query returning the correct number of rows, you can alter the SELECT list to return the columns you want. But the columns aren't really the issue until you can get the correct rows.

Upvotes: 1

Albert D. Kallal
Albert D. Kallal

Reputation: 49169

Best to just use the query builder. Drop in your main table. Choose the columns you want. Now for any of the other lookup values then simply drop in the other tables, draw the join line(s), double click and use a left join. You can do this for 2 or 30 columns that need to "grab" or lookup other values from other tables. The number of ORIGINAL rows in the base table returned should ALWAYS remain the same.

So just use the query builder and follow the above.

The problem with your posted SQL is you NESTED the joins inside (). Don't do that. (or let the query builder do this for you – they tend to be quite messy but will also work).

Just use this:

FROM   source1084 AS srb
LEFT JOIN r12caomappingtable AS rmt
       ON srb.account = rmt.account
LEFT JOIN table4
          ON ( srb.department = table4.dept )
             AND ( srb.[sub-department] = table4.subdept )
             AND ( srb.entity = table4.entity )

As noted, I don't see why you are "repeating" the conditions again in the where clause.

Upvotes: 0

Halfwarr
Halfwarr

Reputation: 8113

Without knowing your tables values it is hard to give a complete answer to your question. The issue that is causing you a problem based on how you described it. Is more then likely based on the type of joins you are using.

The best way I found to understand what type of joins you should be using would referencing a Venn diagram explaining the different type of joins that you can use.

enter image description here

Jeff Atwood also has a really good explanation of SQL joins on his site using the above method as well.

Upvotes: 0

Related Questions