How do I combine two tables in this simple way?

[wakup]

I am trying to combine two tables and I do not know enough about Access to even search for the solution to this (though I have spent many, many hours trying to search for it).

I have included a good example of what I am trying to do (just with much more data). Basically, I want the end result to not create duplicates (joining will automatically fill out the blank cells and add rows) while including Descr1 and Descr2 on the same row.

I want the end result to be a look exactly like Query1. If this can be done by a report, or by a combination of methods in Access, I am all ears.

I will clarify and answer any questions you have. If you can produce something that looks like Query1 from these two tables then it would be perfect. Thanks for your help!

 Table 1:
 ID   Descr1
 1    one
 2    two
 2    twoa
 2    twob
 4    four
 
 Table 2:
 ID   Descr2
 1    A
 2    B
 2    BB
 3    C
 

 Query1:
 ID   Descr1   Descr2
 1    one      A
 2    two      B
 2    twoa     BB
 2    twob    (Blank)
 3   (Blank)   C
 4    four    (Blank)

 

[Pat Hartman]

Welcome aboard

What you are looking for is a "full outer join". Jet does not support this directly but you can simulate it easily enough by using a Union query. The idea is to union two queries the first will do a left join and the second will do a right join and the union will be all the rows from tblA that have no match in tblB, all the rows in tblB that have no batch in tblA, and all the rows that have a match in both tables.

Select tblA.fld1, tblA.fld2, tblB.fld3, tblB.fld4 From tblA Left Join tblB on tblA.fld1 = tblB.fld3
Union Select tblA.fld1, tblA.fld2, tblB.fld3, tblB.fld4 From tblA Right Join tblB on tblA.fld1 = tblB.fld3;

 

[wakup]

Using your exact code (and renaming the table) this is what I got from Access:

Select tblA.fld1, tblA.fld2, tblB.fld3, tblB.fld4 From tblA Left Join tblB on tblA.fld1 = tblB.fld3
Union Select tblA.fld1, tblA.fld2, tblB.fld3, tblB.fld4 From tblA Right Join tblB on tblA.fld1 = tblB.fld3;
 
 fld1      fld2   fld3      fld4
 
[FONT=Courier New]                  3          C  [/FONT]
[FONT=Courier New]1          one    1          A [/FONT]
[FONT=Courier New]2          two    2          B [/FONT]
[FONT=Courier New]2          two    2          BB [/FONT]
[FONT=Courier New]2          twoa   2          B  [/FONT]
[FONT=Courier New]2          twoa   2          BB [/FONT]
[FONT=Courier New]2          twob   2          B [/FONT]
[FONT=Courier New]2          twob   2          BB[/FONT]
[FONT=Courier New]4          d        [/FONT]

I dont know enough about Access to explain why it feels the need to create 6 lines with ID "2" (looks like 3 items from table1 X 2 items from table2 = 6) when I only want 3 lines with ID "2". That is the duplication that I am trying to avoid (Some people seem to call that cartesian).

How do I get rid of that?

 

[wakup]

This may be more of a VBA or Report question. I have not found anyone who can figure this out.

Any ideas at all would be helpful.

-Colin

 

[Pat Hartman]

You need to make the left and right join queries into totals queries to eliminate the duplicates. Then when you union them, you will have the results you need.

 

[MSAccessRookie]

I am trying to combine two tables and I do not know enough about Access to even search for the solution to this (though I have spent many, many hours trying to search for it).

I have included a good example of what I am trying to do (just with much more data). Basically, I want the end result to not create duplicates (joining will automatically fill out the blank cells and add rows) while including Descr1 and Descr2 on the same row.

I want the end result to be a look exactly like Query1. If this can be done by a report, or by a combination of methods in Access, I am all ears.

I will clarify and answer any questions you have. If you can produce something that looks like Query1 from these two tables then it would be perfect. Thanks for your help!

 Table 1:
 ID   Descr1
 1    one
 2    two
 2    twoa
 2    twob
 4    four
 
 Table 2:
 ID   Descr2
 1    A
 2    B
 2    BB
 3    C
 
 
 Query1:
 ID   Descr1   Descr2
 1    one      A
 2    two      B
 2    twoa     BB
 2    twob    (Blank)
 3   (Blank)   C
 4    four    (Blank)

Pat Hartman is correct about the Union. Try it something like this:

Select Table1.ID, Table1.Descr, Table2.Descr From Table1 Left Join Table2 On Table1.ID=Table2.ID
Union
Select Table2.ID, Table1.Descr, Table2.Descr From Table2 Left Join Table1 On Table2.ID=Table1.ID

 

[redneckgeek]

Here's my stab at it. A little more complex than you'd expect, but it yields the exact results you ask for, using the data you supplied. The thing is, you need a "count" field for each id/desc. Then, as Pat Hartman said, you need to use both left and right joins to get everything together. The DISTINCT is needed to eliminate duplicates.

SELECT DISTINCT ID, DESC1, DESC2
FROM (
      SELECT IIf(IsNull([A].[id]),[B].[ID],[B].[ID]) AS id, A.desc1, B.desc2
      FROM (SELECT Table1.id, table1.desc1, count(Table1.desc1) AS CNT
                    FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.desc1 >=Table1_1.desc1 
                    AND Table1.id = Table1_1.id
                   group by table1.id, table1.desc1) A 
      RIGHT JOIN (SELECT Table2.id, table2.desc2, count(Table2.desc2) AS CNT
                            FROM TablE2 INNER JOIN Table2 AS Table2_1 ON Table2.desc2 >=Table2_1.desc2
                            AND Table2.id = Table2_1.id
                           group by table2.id, table2.desc2) B 
      ON A.id = B.id AND A.cnt = B.cnt
      UNION ALL
       SELECT IIf(IsNull([B].[id]),[A].[ID],[B].[ID]) AS id, A.desc1, B.desc2
       FROM (SELECT Table1.id, table1.desc1, count(Table1.desc1) AS CNT
                    FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.desc1 >=Table1_1.desc1 
  AND Table1.id = Table1_1.id
  group by table1.id, table1.desc1) A 
      LEFT JOIN (SELECT Table2.id, table2.desc2, count(Table2.desc2) AS CNT
                          FROM TablE2 INNER JOIN Table2 AS Table2_1 ON Table2.desc2 >=Table2_1.desc2
                           AND Table2.id = Table2_1.id
                          group by table2.id, table2.desc2) B 
      ON A.id = B.id AND A.CNT= B.CNT
) C
ORDER BY ID, DESC1

 

[redneckgeek]

Now that I've thought about it a little....

I've assumed you wanted to match the "first" ID 2 record in table1 with the first ID 2 record in table2 and the second to the second and so on. Your example implies ordering by orderid, and then by descr. But unless those 2 fields are indexed that way, you'd better put an order by clause in the innermost queries above.

Hopefully, another poster will find a way to shorten my query...

 

[wakup]

Redneckgeek - Yes I see exactly what you are saying, I changed it a little but followed your thought process. Thank you so much for your help. I wrote some code to create the running count by ID and it works great.

The full outer join does not work by itself, but it is needed to pick everything up. I realize now that joins only work with unique identifiers, and thats why I needed to create one (seems obvious to me now, but certainly not a week ago).

Thanks guys!

 

[Pat Hartman]

redneckgeek - the "Union" statement eliminates duplicates. "Union All" returns all rows, even duplicates that is why you needed the distinct keyword in the outer query.