action queries together

[slimjen]

can I use a update statement and append statement in the same query?

UPDATE SMALLPKG SET SMALLPKG.[Recipient Company] = "ATLAS COPCO COMPRESSORS"
WHERE (((SMALLPKG.[Recipient Company])="ATLAS COPCO (ORIGIN AIR)")) OR (((SMALLPKG.[Recipient Company])="NORTH AMERICAN SERVICE CENTER")) OR (((SMALLPKG.[Recipient Company])="ATLAS COPCO ASAP")) OR (((SMALLPKG.[Recipient Company])="ATLAS COPCO COMPRESSORS LLC")) OR (((SMALLPKG.[Recipient Company])="ATLAS COPCO COMPRESSORS, LLC"))
UPDATE SMALLPKG SET SMALLPKG.[Shipper Company] = "ATLAS COPCO COMPRESSORS"
WHERE (((SMALLPKG.[Shipper Company])="ATLAS COPCO (ORIGIN AIR)")) OR (((SMALLPKG.[Shipper Company])="NORTH AMERICAN SERVICE CENTER")) OR (((SMALLPKG.[Shipper Company])="ATLAS COPCO ASAP")) OR (((SMALLPKG.[Shipper Company])="ATLAS COPCO COMPRESSORS LLC")) OR (((SMALLPKG.[Shipper Company])="ATLAS COPCO COMPRESSORS, LLC"))

INSERT INTO tblSP_SHIPMENT_temp_TEST ( INVOICENUM, TRACKINGNUM, SHIPDATE, TRANSCHG, SALESORDNUM, SENDERNAME, SENDERCITY, SENDERSTATE, SENDERZIP, RECIPIENTNAME, RECIPIENTCITY, RECIPIENTSTATE, RECIPIENTZIP, WEIGHT, SERVICETYPE )
SELECT SMALLPKG.[Invoice Number], SMALLPKG.[Express or Ground Tracking ID], SMALLPKG.[Shipment Date], SMALLPKG.[Net Charge Amount], SMALLPKG.[Original Customer Reference], SMALLPKG.[Shipper Company], SMALLPKG.[Shipper City], SMALLPKG.[Shipper State], SMALLPKG.[Shipper Zip Code], SMALLPKG.[Recipient Company], SMALLPKG.[Recipient City], SMALLPKG.[Recipient State], SMALLPKG.[Recipient Zip Code], SMALLPKG.[Actual Weight Amount], SMALLPKG.[Service Type]
FROM SMALLPKG;

If yes; what am I missing here?


Thanks

[ssanfu]

AFAIK, Access allows only one 'verb' (Select/Insert/Update) per query.

Since the two Update queries are on the same table, you can combine them:

UPDATE SMALLPKG SET SMALLPKG.[Recipient Company] = "ATLAS COPCO COMPRESSORS", SMALLPKG.[Shipper Company] = "ATLAS COPCO COMPRESSORS"
WHERE (((SMALLPKG.[Recipient Company])="ATLAS COPCO (ORIGIN AIR)")) OR (((SMALLPKG.[Recipient Company])="NORTH AMERICAN SERVICE CENTER")) OR (((SMALLPKG.[Recipient Company])="ATLAS COPCO ASAP")) OR (((SMALLPKG.[Recipient Company])="ATLAS COPCO COMPRESSORS LLC")) OR (((SMALLPKG.[Recipient Company])="ATLAS COPCO COMPRESSORS, LLC"))

Observation......
I don't know your requirements, but you might look at having a Companies table and store the CompanyPK in a FK field for "Recipient Company and "Shipper Company", instead of storing the name of the companies. Using a combo box to select the company name ensures a consistent name.

[slimjen]

do I put this before or after the append query?

[ssanfu]

do I put this before or after the append query?

You have two queries: an Update query and an Append query. You execute one (the order is up to you); when that one is done, execute the other.

You cannot combine an Update query with an Append query into one query.