JOIN Tables on <> Field

[jjr0319]

i have two tables in access, one labeled "3033" and the other labeled "3107". they both have the same fields, one in particualr called "Product #". i needed to create to queries, one which pulls the items from both tables where the "Product #" was equal. i got this working. now i want to pull the rest of the items where the "Product #" are not equal. i changed the syntax in the SQL from = to <>, but it returns over 1000000 records for some reason. the maximum number of records in the DB is 2021, so it shouldn't be returning that many. here's the SQL for the equals portion thats working:

SELECT [3107].[Product #], [3107].[Product Description], [3107].[Military Product #], [3107].[Sales Dollars] AS [3107_Sales Dollars], [3107].[Qty Shipped] AS [3107_Qty Shipped], [3107].[% to Sales] AS [3107_% to Sales], [3033].[Sales Dollars] AS [3033_Sales Dollars], [3033].[Qty Shipped] AS [3033_Qty Shipped], [3033].[% to Sales] AS [3033_% to Sales]
FROM 3033 INNER JOIN 3107 ON [3033].[Product #]=[3107].[Product #]
ORDER BY [3107].[Sales Dollars] DESC;

any ideas on how to make this work?
thanks in advance.

[Ubik]

SELECT [3107].[Product #], [3107].[Product Description], [3107].[Military Product #], [3107].[Sales Dollars] AS [3107_Sales Dollars], [3107].[Qty Shipped] AS [3107_Qty Shipped], [3107].[% to Sales] AS [3107_% to Sales], [3033].[Sales Dollars] AS [3033_Sales Dollars], [3033].[Qty Shipped] AS [3033_Qty Shipped], [3033].[% to Sales] AS [3033_% to Sales]
FROM 3107
WHERE [3107].[Product #] NOT IN (SELECT [3033].[Product #] FROM 3033);
ORDER BY [3107].[Sales Dollars] DESC;

This is assuming that 3107 is the primary table.

[jjr0319]

thanks a lot. i had to run two seperate queries for it to work, but i got it based on you SQL. much appreciation.

[Ubik]

Oops. I made some mistakes. Here is a fixed version, getting data out of both tables:


SELECT [3107].[Product #], [3107].[Product Description], [3107].[Military Product #], [3107].[Sales Dollars] AS [3107_Sales Dollars], [3107].[Qty Shipped] AS [3107_Qty Shipped], [3107].[% to Sales] AS [3107_% to Sales]
FROM 3107
WHERE [3107].[Product #] NOT IN (SELECT [3033].[Product #] FROM 3033);
UNION
SELECT [3033].[Sales Dollars] AS [3033_Sales Dollars], [3033].[Qty Shipped] AS [3033_Qty Shipped], [3033].[% to Sales] AS [3033_% to Sales]
FROM 3033
WHERE [3033].[Product #] NOT IN (SELECT [3107].[Product #] FROM 3107);