Hi, I have a project where I have imported some Peachtree accounting data into an Access table called "tblPeachtree". I now need to get the "tblPeachtree" data (there are 45 fields) into a larger table (135 total fields) called "tblMOM" so I can transmit a .cvs file to a vendor using Mail Order Manager.
I have already defined which fields correlate with each other (i.e. "Customer ID" in Peachtree needs to go into a field called "CUSTNUM" for the MOM system). I'm just stuck on how I can merge this data. What would be the best practices? Create a new table? Use an Append Query? Sheesh.
I tried the below append query and it acts like it is working (no errors) but Clippy tells me I'm about to append zero rows. I want to update/append about 40 rows!
Here is the SQL if it helps. Hopefully I was clear enough in my description
The [SO-HW2] table is the one I imported from Peachtree and the [tblExport2] table is the one I created with all the MOM fields that need updating.
INSERT INTO tblExport2 ( CUSTNUM, COMPANY, ALTNUM, ORDER_DATE, SHIP_WHEN, CUSTOM01, CUSTOM02, ORDERTYPE, SCOMPANY, SADDRESS1, SADDRESS2, SCITY, SSTATE, SZIPCODE, SCOUNTRY, PONUMBER, SHIPVIA, SALES_ID, ORDNOTE1, QUANTITY01, PRODUCT01, PRICE01 )
SELECT [SO-HW2].[Customer ID], [SO-HW2].[Customer Name], [SO-HW2].[Sales Order/Proposal Num], [SO-HW2].Date, [SO-HW2].[Ship By], [SO-HW2].Proposal, [SO-HW2].[Proposal Accepted], [SO-HW2].[Drop Ship], [SO-HW2].[Ship to Name], [SO-HW2].[Ship to Address-Line One], [SO-HW2].[Ship to Address-Line Two], [SO-HW2].[Ship to City], [SO-HW2].[Ship to State], [SO-HW2].[Ship to Zipcode], [SO-HW2].[Ship to Country], [SO-HW2].[Customer PO], [SO-HW2].[Ship Via], [SO-HW2].[Sales Representative ID], [SO-HW2].[Invoice Note], [SO-HW2].Quantity, [SO-HW2].[Item ID], [SO-HW2].[Unit Price]
FROM [SO-HW2], tblExport2;
I have already defined which fields correlate with each other (i.e. "Customer ID" in Peachtree needs to go into a field called "CUSTNUM" for the MOM system). I'm just stuck on how I can merge this data. What would be the best practices? Create a new table? Use an Append Query? Sheesh.
I tried the below append query and it acts like it is working (no errors) but Clippy tells me I'm about to append zero rows. I want to update/append about 40 rows!
Here is the SQL if it helps. Hopefully I was clear enough in my description
The [SO-HW2] table is the one I imported from Peachtree and the [tblExport2] table is the one I created with all the MOM fields that need updating.INSERT INTO tblExport2 ( CUSTNUM, COMPANY, ALTNUM, ORDER_DATE, SHIP_WHEN, CUSTOM01, CUSTOM02, ORDERTYPE, SCOMPANY, SADDRESS1, SADDRESS2, SCITY, SSTATE, SZIPCODE, SCOUNTRY, PONUMBER, SHIPVIA, SALES_ID, ORDNOTE1, QUANTITY01, PRODUCT01, PRICE01 )
SELECT [SO-HW2].[Customer ID], [SO-HW2].[Customer Name], [SO-HW2].[Sales Order/Proposal Num], [SO-HW2].Date, [SO-HW2].[Ship By], [SO-HW2].Proposal, [SO-HW2].[Proposal Accepted], [SO-HW2].[Drop Ship], [SO-HW2].[Ship to Name], [SO-HW2].[Ship to Address-Line One], [SO-HW2].[Ship to Address-Line Two], [SO-HW2].[Ship to City], [SO-HW2].[Ship to State], [SO-HW2].[Ship to Zipcode], [SO-HW2].[Ship to Country], [SO-HW2].[Customer PO], [SO-HW2].[Ship Via], [SO-HW2].[Sales Representative ID], [SO-HW2].[Invoice Note], [SO-HW2].Quantity, [SO-HW2].[Item ID], [SO-HW2].[Unit Price]
FROM [SO-HW2], tblExport2;
