Append updated/new records to a table (1 Viewer)

[anissw]

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;

 

[Isskint]

When you say "with the criteria included will not append records", is it just that nothing happens or do you get some kind of error?

In general terms, if a query with criteria does not return any records, then the criteria is not being found. So, check the criteria is correct, are the field types the same, do you physically have data to match your criteria?

 

[spikepl]

Further to Isskint's comments: if you have concocted something complex consisting of many elements, and it doesn't perform as desired, then "deconcoct" it. Rebuild the thing step by step - add one element at a time - and make sure that each step works as desired.

 

[StarGrabber]

Further to Isskint's and spikepl's comments:

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]) Not In (SELECT [Master Invoice].[Customer Name] FROM [Master Invoice])))
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, CustomerDetails.Weekending, CustomerDetails.WHSE_DESC, CustomerDetails.[CUSTOMER PO]
HAVING (((CustomerDetails.Weekending) Not In (SELECT [Master Invoice].[Weekending] FROM [Master Invoice])) AND ((CustomerDetails.WHSE_DESC) Not In (SELECT [Master Invoice].[WHSE_DESC] FROM [Master Invoice])) AND ((CustomerDetails.[CUSTOMER PO]) Not In (SELECT [Master Invoice].[CUSTOMER PO] FROM [Master Invoice])))
ORDER BY CustomerDetails.[Customer Name], CustomerDetails.Weekending;

 

[anissw]

Hi. I want to thank you for replying. The criteria does produce records from the CustomerDetails query as listed in the code but only without the criteria included. I am looking to have data upload doesn't exist in the master invoice. If I click to append without the criteria in the code, it will append records with no problems. However, there is a chance that someone can click the append button more than once. If this happens, the same records will append to the master table, creating duplicates. Is there anyway that I can apply the restrictions either in the append query or maybe the update query or maybe I have to change the parameters of my query altogether? I haven't tried the update query since I am interested in all fields to be uploaded to the master invoice.

Thanks again for replying and any suggestions would help.

 

[StarGrabber]

Please upload your database with a few (non critical) sample records. The reason I'm asking you this is that I know nothing about an "update query".

 

[anissw]

Thank you so much. Please find attached the database with sample data in the order final table as well as the append and select queries. Let me know if you are unable to open it.

 

[StarGrabber]

Anissa, don't take this wrong but with your post (# 5) you got me confused. I'm not quite sure if I understand you well. Originally I thought your intention is to append certain records from ORDER_FINAL to Master Invoice. The only criteria for this are that these records should not exist in Master Invoice already and the Quantity has to be > 0.

If I am right then you can see the result in attached file. The append query I made is named "qappInvoicesToMaster".

Please note that there is no danger (and never was) of creating duplicates (you should have tried it). - And there is more:

- I removed all the joins, all the grouping and the ORDER BY condition as I don't see the need.

- The query CustomerDetails is useless - at least for the append action. By the way, you should not name an append query "UpdateMasterInvoice". This is really irritating.

And I recommend you to remove any customer data from both tables except the Customer ID field! Create a separate table (e.g. tblCustomers) wich has a relationship to that field.

Try to keep things as simple as possible.

But if I understood your scenario wrong please provide more details.

 

[anissw]

Thanks so much!!

This was just what I needed. The master invoice table will provide a list of invoices generated to for validation or means of regenerating an invoice per request. I couldn't tell if my CustomerDetails query would work in this process and was stuck trying to resolve it.

Again thanks for you resolution as well as you suggestions. I know what I provided confused you.

Anissa

 

[StarGrabber]

Alright then... good luck with your project!