Need to merge two queries (or tables)

[gebli]

Hi,

I need to (sort of) combine LEFT and RIGHT JOINs, or something like that.
Better with an example:

Table1:
ID Name Value1 Check1
1 John Book Invalid
3 Mary Pencil Invalid

Table2:
ID Name Value2 Check2
2 Rick North Invalid
3 Mary South Invalid

Needed Result:
ID Name Value1 Check1 Value2 Check2
1 John Book Invalid (blank) (blank)
2 Rick (blank) (blank) North Invalid
3 Mary Pencil Invalid South Invalid

LEFT and Right JOINs will miss one record from. INNER JOIN will miss two records. I tried UNION but I don't think it is the way to go...

Can anybody help me with this?

Thanks in advance,

Gerry

PS: sorry for the poor formatting but I don't know how to paste tables in posts...

 

[MSAccessRookie]

Hi,

I need to (sort of) combine LEFT and RIGHT JOINs, or something like that.
Better with an example:

Table1:
ID Name Value1 Check1
1 John Book Invalid
3 Mary Pencil Invalid

Table2:
ID Name Value2 Check2
2 Rick North Invalid
3 Mary South Invalid

Needed Result:
ID Name Value1 Check1 Value2 Check2
1 John Book Invalid (blank) (blank)
2 Rick (blank) (blank) North Invalid
3 Mary Pencil Invalid South Invalid

LEFT and Right JOINs will miss one record from. INNER JOIN will miss two records. I tried UNION but I don't think it is the way to go...

Can anybody help me with this?

Thanks in advance,

Gerry

PS: sorry for the poor formatting but I don't know how to paste tables in posts...

A Union might work here, since a Right Join gets most of the data, and a Left Join gets the rest.

Select Table1.ID, 
    Table1.Name, 
    Table1.Value1, 
    Table1.Check1, 
    Table2.Value2, 
    Table2.Check2
From Table1 Left Outer Join Table2
On Table1.ID = Table2.ID
UNION
Select Table1.ID, 
    Table1.Name, 
    Table1.Value1, 
    Table1.Check1, 
    Table2.Value2, 
    Table2.Check2
From Table1 Right Outer Join Table2
On Table1.ID = Table2.ID

PS. To format posts, define them as Code or Quotes. To Do this, surround any text you want formatted as follows:

• "[ c o d e ] {Your Text} [ / c o d e ]"
• "[ q u o t e ] {Your Text} [ / q u o t e ]"

The spaces between the brackets must be removed before it will work . I had to do it that way, because leaving them out causes the words {Your Text} to appear in a format block.

 

[gebli]

YES! It did work (just some minor adjustments to the field names).

Thank you very much!

Gerry

PS: Thanks for the formatting advice

 

[MSAccessRookie]

YES! It did work (just some minor adjustments to the field names).

Thank you very much!

Gerry

PS: Thanks for the formatting advice

Glad that I could help