Join tables even if records don't match

[maw230]

I'm not sure my title was appropriate.
I have two tables - Table1 has This Year sales data, Table 2 has Last Year sales data.
The data is grouped by Line and Plcd.
So, when Line and Plcd from Table1 matches Table2, I need to subtract This Year Sales from Last Year Sales.
However, if there are a Line and Plcd from Table1 that aren't on Table2, I still need to display the sales data from Table1 (i.e. This Year's Sales) and vice versa.

How can I "join" these tables to perform calculations when they match, and then to show only the data available when they don't match? I don't see where and inner or outer join will work here.

 

[pbaldy]

Sounds like what you want is a FULL OUTER JOIN, which unfortunately Access does not support (SQL Server does). The usual workaround is to use a UNION query to join together 3 queries, with LEFT, RIGHT and INNER joins respectively.

 

[maw230]

Sounds like what you want is a FULL OUTER JOIN, which unfortunately Access does not support (SQL Server does). The usual workaround is to use a UNION query to join together 3 queries, with LEFT, RIGHT and INNER joins respectively.

Ok, I'll try the union method. Thanks.

 

[pbaldy]

No problem; post back if you get stuck.

 

[maw230]

Ok, the recordset looks good. Seems to be doing what I want it to. One issue, however:

Where records are populating without a Line and Plcd, but still have data. Not sure if I left something out...

If the embedded image doesn't work:

http://i.imgur.com/RdWnE.jpg

 

[pbaldy]

Can you post the db, or a representative sample?

 

[maw230]

How about the SQL to start?

SELECT tblGM_MTD_LY_LP.LINE, tblGM_MTD_LY_LP.PLCD, tblGM_MTD_LY_LP.[Cash GM] AS [LY Cash], tblGM_MTD_LY_LP.[Charge GM] AS [LY Charge], tblGM_MTD_LY_LP.[Total GM] AS [LY Total], tblGM_MTD_TY_LP.[Cash GM] AS [TY Cash], tblGM_MTD_TY_LP.[Charge GM] AS [TY Charge], tblGM_MTD_TY_LP.[Total GM] AS [TY Total]
FROM tblGM_MTD_LY_LP RIGHT JOIN tblGM_MTD_TY_LP ON (tblGM_MTD_LY_LP.LINE = tblGM_MTD_TY_LP.LINE) AND (tblGM_MTD_LY_LP.PLCD = tblGM_MTD_TY_LP.PLCD)

UNION SELECT tblGM_MTD_LY_LP.LINE, tblGM_MTD_LY_LP.PLCD, tblGM_MTD_LY_LP.[Cash GM] AS [LY Cash], tblGM_MTD_LY_LP.[Charge GM] AS [LY Charge], tblGM_MTD_LY_LP.[Total GM] AS [LY Total], tblGM_MTD_TY_LP.[Cash GM] AS [TY Cash], tblGM_MTD_TY_LP.[Charge GM] AS [TY Charge], tblGM_MTD_TY_LP.[Total GM] AS [TY Total]
FROM tblGM_MTD_LY_LP LEFT JOIN tblGM_MTD_TY_LP ON (tblGM_MTD_LY_LP.LINE = tblGM_MTD_TY_LP.LINE) AND (tblGM_MTD_LY_LP.PLCD = tblGM_MTD_TY_LP.PLCD)

UNION SELECT tblGM_MTD_LY_LP.LINE, tblGM_MTD_LY_LP.PLCD, tblGM_MTD_LY_LP.[Cash GM] AS [LY Cash], tblGM_MTD_LY_LP.[Charge GM] AS [LY Charge], tblGM_MTD_LY_LP.[Total GM] AS [LY Total], tblGM_MTD_TY_LP.[Cash GM] AS [TY Cash], tblGM_MTD_TY_LP.[Charge GM] AS [TY Charge], tblGM_MTD_TY_LP.[Total GM] AS [TY Total]
FROM tblGM_MTD_LY_LP INNER JOIN tblGM_MTD_TY_LP ON (tblGM_MTD_LY_LP.PLCD = tblGM_MTD_TY_LP.PLCD) AND (tblGM_MTD_LY_LP.LINE = tblGM_MTD_TY_LP.LINE);

 

[pbaldy]

You select "tblGM_MTD_LY_LP.LINE" when the RIGHT JOIN is on "tblGM_MTD_TY_LP", so when there are no records in the LY table you'll get a Null there. For the fields you want returned, you should use whatever table has the main (LEFT or RIGHT) join on it.

 

[maw230]

so when there are no records in the LY table you'll get a Null there. For the fields you want returned, you should use whatever table has the main (LEFT or RIGHT) join on it.

Ok, but if there are records in the TY table I need to see those Line, Plcds. So, how do I write the join query to only return records for the year in which there is data...?

Not sure if I am explaining this right so I'll add another picture...hopefully formatted properly this time

This shows my new Right Join query. I know it's not correct but it's closer to what I need. All I did was add Line Plcd from TY table, so that it will bring back either or depending on where the data is. Now, how do I tell it to only bring back fields containing data, and of course as the same name (i.e. Line, Plcd) vs. Line1, Plcd1.

 

[maw230]

^^ Nevermind

Thanks for letting me sort that one out. Damn you, Monday!

 

[pbaldy]

Glad you got it sorted out. Despite the accursed Monday.