Need help modifying query

[redwagontoy]

I need help modifying the following query:

**************

INSERT INTO UPSWorldshipInfo ( OrderNum, Company, Street, City, State, CountryCode, Zip, Phone, Null1, Null2, EmailAdr, OrderValue, ShipmentCode, ShipmentMethod )
SELECT Shipinfo.OrderNum, Shipinfo.Company, Shipinfo.Street, Shipinfo.City, Shipinfo.State, Shipinfo.CountryCode, Shipinfo.Zip, Shipinfo.Phone, Shipinfo.Null1, Shipinfo.Null2, Shipinfo.EmailAdr, Shipinfo.OrderValue, Q.[Shipment Code], Q.Method
FROM [Select S.[Order ID], S.[Shipment Code],
Mid(S.[Shipment Method],InStr(S.[Shipment Method],'USPS'),4) AS Method
from Shipments S where InStr(S.[Shipment Method],'USPS') >0


UNION ALL


Select S.[Order ID], S.[Shipment Code],
Mid(S.[Shipment Method],InStr(S.[Shipment Method],'UPS'),3) AS Method
from Shipments S where InStr(S.[Shipment Method],'UPS') >0
]. AS Q, Shipinfo
WHERE (((Shipinfo.OrderNum)=Q.[Order ID]));



************************
This query takes two data from two tables shipinfo.txt and shipments.csv and puts it into UPSWorldshipInfo table. This query works fine right now but now my shipments.csv has two extra columns and does not work when those two extra columns are there.

ORDER_ID

SHIPMENT_CODE

WEIGHT

ADDRESS

CITY

STATE

ZIP

SHIPPING_METHOD

ORDER_ID

SHIPMENT_CODE

WEIGHT

NAME

ADDRESS

CITY

STATE

ZIP

SHIPPING_METHOD

PROD_COUNT

So the NAME and PROD_COUNT are new and the query does not work with these two new fields. I don't know how to program this and and can't get a hold of the guy who did it. I tried making one little change and then tried saving the macro and I get the following error:

Invalid bracketing of name 'Select S.[Oder_Id'

So not able to save the existing working query and need to know how to fix that and what to change to make query work with new columns

Thanks for any help.

Scott

[June7]

That SQL doesn't make sense to me. Appears to be an extra FROM clause before the UNION ALL line. Is there supposed to be another UNION ALL in there?

Post the attempted SQL statement that fails.

[redwagontoy]

That SQL doesn't make sense to me. Appears to be an extra FROM clause before the UNION ALL line. Is there supposed to be another UNION ALL in there?

Post the attempted SQL statement that fails.

Not sure but it does work the way it is right now so not sure why it gives error.

[June7]

Never seen anything like that. I would have to see the SQL that fails.

Also, if you want to provide db for analysis, follow instructions at bottom of my post.

[redwagontoy]

The database and the tables are attached in zip file.


LindedToWebOrder.mdb (setup to autoexecute so will not run completely if the two tables are present in shared documents folder)

shipinfo.txt (1 of 2 tables query pulls from)
shipments.csv (2 of 2 tables query pulls from)
shipments-with-newcolumns.csv (new shipments.csv that has new columns in it)

Scott

[June7]

I relinked the files with Linked Table Manager. The linked csv is messed up. The first record looks like field names and the data is not under the correct table fields. For instance, city values show under State field. The linked file is shipments-with-new-columns.csv, can't even open shipments.csv.

[redwagontoy]

These should be the correct files.

[June7]

Okay, now I can see that FROM clause is not an extra line. I was confused because your posted SQL above shows a [ where should be (.

I don't see any fields anywhere called [Name] and [Prod_Count].

'Name' is a reserved word. Should avoid names that are reserved words. Also advise no spaces or special characters/punctuation (underscore is exception) in names.

[redwagontoy]

So the table with NAME and PROD_COUNT not in the query. I have been using this with the shipments.csv that does not have those fields and comes from a shopping cart output. My shopping cart provider changed the output on me and added the two extra fields which I cannot change and I am stuck with the output the module exports.

So the the query needs to be modified somehow to work with the new fields but the existing working query can't be saved as it is even because it gives an error when saving.

Scott

[June7]

Version of shipments.csv provided does not have those fields but future versions of that file will have those fields?

I don't understand the issue. If you don't have those fields in the destination table, why do they need to be referenced in the query? Just ignore the unneeded fields in the csv file.

[redwagontoy]

All future versions will have those two new fields and the query does not need to reference them, but the query as is will not run with NAME field in there. I think it runs with the PROD_COUNT. So I just need the query to run with those fields but again they are not needed.

Scott

[June7]

Lost me.

Even if the fields are in the downloaded and linked file, if they are aren't need in UPSWorldshipInfo (the fields are not there to receive data) then ignore them. Don't put them in the INSERT action.

[redwagontoy]

I am not trying to insert those two new fields, but the query does not work with those two new fields.

Scott

[June7]

If the fields are not included anywhere in any part of that SQL statement it should run. Just because the fields are in source data should not affect the query.

I do get errors when running the APPEND. The query can't find fields and says there is a datatype mismatch. The first row of shipments.csv is coming in as a data row. Does that happen in your copy? Maybe that happened when I used the Linked Table Manager.

I individually relinked the shipinfo.txt and shipments-with-new-columns.csv files and made sure field names were consistent (without spaces) and field types did not conflict. Rewrote the SQL and it works:

INSERT INTO UPSWorldshipInfo (OrderNum, Company, Street, City, State, CountryCode, Zip, Phone, Null1, Null2, EmailAdr, OrderValue, ShipmentCode, ShipmentMethod)
SELECT Shipinfo.OrderNum, Shipinfo.Company, Shipinfo.Street, Shipinfo.City, Shipinfo.State, Shipinfo.CountryCode, Shipinfo.Zip, Shipinfo.Phone, Shipinfo.Null1, Shipinfo.Null2, Shipinfo.EmailAdr, Shipinfo.OrderValue, Q.ShipmentCode, Q.Method
FROM (SELECT OrderNum, ShipmentCode, Mid([ShipmentMethod],InStr([ShipmentMethod],'USPS'),4) AS Method
FROM Shipments WHERE InStr([ShipmentMethod],'USPS') >0
UNION ALL SELECT OrderNum, ShipmentCode,
Mid([ShipmentMethod],InStr([ShipmentMethod],'UPS'),3) AS Method
FROM Shipments WHERE InStr([ShipmentMethod],'UPS') >0) AS Q, Shipinfo
WHERE (((Shipinfo.OrderNum)=Q.OrderNum));