Hi- I have a query to append new invoice records to a table I name master Invoice. I am having problems getting this query to look at query CustomerDetails and only append records that do not exist in Master Invoice. I have included my query below. This query with the criteria included will not append records, however, when I remove the criteria, it appends all records, including any that exist in the master invoice. Any thoughts into how to resolve this?
Thanks-
Anissa
INSERT INTO [Master Invoice] ( [Customer Name], [Address 1], [Address 2], CUSTOMER_City, CUSTOMER_State, CUSTOMER_Zip, Weekending, Quantity, [ITEM ID], Description, [UNIT PRICE], Amount, WHSE_DESC, [Customer ID], [Displayed Terms], [Due Date], [CUSTOMER PO], SalesTax, Payment )
SELECT CustomerDetails.[Customer Name], CustomerDetails.[Address 1], CustomerDetails.[Address 2], CustomerDetails.CUSTOMER_City, CustomerDetails.CUSTOMER_State, CustomerDetails.CUSTOMER_Zip, CustomerDetails.Weekending, CustomerDetails.Quantity, CustomerDetails.[ITEM ID], CustomerDetails.Description, CustomerDetails.[UNIT PRICE], CustomerDetails.Amount, CustomerDetails.WHSE_DESC, CustomerDetails.[Customer ID], CustomerDetails.[Displayed Terms], CustomerDetails.[Due Date], CustomerDetails.[CUSTOMER PO], CustomerDetails.SalesTax, CustomerDetails.Payment
FROM CustomerDetails LEFT JOIN [Master Invoice] ON (CustomerDetails.[CUSTOMER PO] = [Master Invoice].[CUSTOMER PO]) AND (CustomerDetails.WHSE_DESC = [Master Invoice].WHSE_DESC) AND (CustomerDetails.Weekending = [Master Invoice].Weekending) AND (CustomerDetails.[Customer Name] = [Master Invoice].[Customer Name])
WHERE (((CustomerDetails.[Customer Name])<>[Master Invoice].[Customer Name]) AND ((CustomerDetails.Weekending)<>[Master Invoice].[Weekending]) AND ((CustomerDetails.WHSE_DESC)<>[Master Invoice].[WHSE_DESC]) AND ((CustomerDetails.[CUSTOMER PO])<>[Master Invoice].[CUSTOMER PO]))
GROUP BY CustomerDetails.[Customer Name], CustomerDetails.[Address 1], CustomerDetails.[Address 2], CustomerDetails.CUSTOMER_City, CustomerDetails.CUSTOMER_State, CustomerDetails.CUSTOMER_Zip, CustomerDetails.Weekending, CustomerDetails.Quantity, CustomerDetails.[ITEM ID], CustomerDetails.Description, CustomerDetails.[UNIT PRICE], CustomerDetails.Amount, CustomerDetails.WHSE_DESC, CustomerDetails.[Customer ID], CustomerDetails.[Displayed Terms], CustomerDetails.[Due Date], CustomerDetails.[CUSTOMER PO], CustomerDetails.SalesTax, CustomerDetails.Payment
ORDER BY CustomerDetails.[Customer Name], CustomerDetails.Weekending;
Thanks-
Anissa
INSERT INTO [Master Invoice] ( [Customer Name], [Address 1], [Address 2], CUSTOMER_City, CUSTOMER_State, CUSTOMER_Zip, Weekending, Quantity, [ITEM ID], Description, [UNIT PRICE], Amount, WHSE_DESC, [Customer ID], [Displayed Terms], [Due Date], [CUSTOMER PO], SalesTax, Payment )
SELECT CustomerDetails.[Customer Name], CustomerDetails.[Address 1], CustomerDetails.[Address 2], CustomerDetails.CUSTOMER_City, CustomerDetails.CUSTOMER_State, CustomerDetails.CUSTOMER_Zip, CustomerDetails.Weekending, CustomerDetails.Quantity, CustomerDetails.[ITEM ID], CustomerDetails.Description, CustomerDetails.[UNIT PRICE], CustomerDetails.Amount, CustomerDetails.WHSE_DESC, CustomerDetails.[Customer ID], CustomerDetails.[Displayed Terms], CustomerDetails.[Due Date], CustomerDetails.[CUSTOMER PO], CustomerDetails.SalesTax, CustomerDetails.Payment
FROM CustomerDetails LEFT JOIN [Master Invoice] ON (CustomerDetails.[CUSTOMER PO] = [Master Invoice].[CUSTOMER PO]) AND (CustomerDetails.WHSE_DESC = [Master Invoice].WHSE_DESC) AND (CustomerDetails.Weekending = [Master Invoice].Weekending) AND (CustomerDetails.[Customer Name] = [Master Invoice].[Customer Name])
WHERE (((CustomerDetails.[Customer Name])<>[Master Invoice].[Customer Name]) AND ((CustomerDetails.Weekending)<>[Master Invoice].[Weekending]) AND ((CustomerDetails.WHSE_DESC)<>[Master Invoice].[WHSE_DESC]) AND ((CustomerDetails.[CUSTOMER PO])<>[Master Invoice].[CUSTOMER PO]))
GROUP BY CustomerDetails.[Customer Name], CustomerDetails.[Address 1], CustomerDetails.[Address 2], CustomerDetails.CUSTOMER_City, CustomerDetails.CUSTOMER_State, CustomerDetails.CUSTOMER_Zip, CustomerDetails.Weekending, CustomerDetails.Quantity, CustomerDetails.[ITEM ID], CustomerDetails.Description, CustomerDetails.[UNIT PRICE], CustomerDetails.Amount, CustomerDetails.WHSE_DESC, CustomerDetails.[Customer ID], CustomerDetails.[Displayed Terms], CustomerDetails.[Due Date], CustomerDetails.[CUSTOMER PO], CustomerDetails.SalesTax, CustomerDetails.Payment
ORDER BY CustomerDetails.[Customer Name], CustomerDetails.Weekending;