Trying to append data of two tables

[TIbbs]

I have two tables, one Stock that holds all data on stock items. Another table, SupplierProducts holds additional data on stock items.
The reason why is that the first table is an appended sage table, and the second one is a table with additional data for stock items provided by the supplier through an Excel spreadsheet. Sage does not allow for those extra fields so they are kept in Access.
I need to combine some of the fields from the 2nd table into the stock table. They both have stock_code as an identifier.
It is messy and my append query is not working.

INSERT INTO StockTbl ( PACKER_QTY, CASE_QTY )
SELECT tblSupplierProductData.intPackerQty, tblSupplierProductData.intCaseQty
FROM StockTbl LEFT JOIN tblSupplierProductData ON StockTbl.STOCK_CODE = tblSupplierProductData.txtStockCode;


Will the solution mean I need to create a new table with all the stock data from both tables, deleting the data from the original tables?

 

[twoplustwo]

Is your db properly normalised?

 

[neileg]

If you already have the data in your second table, why transfer it to the first table? Just join the two tables in a select query.

 

[TIbbs]

I am linking 3 tables from Sage, Stock, Invoice_Item and Invoice so I had to come up with a new solution, using select queries to get the data out.
1. I linked the Stock and Invoice tables to Acess using their primary key with exception of Invoice Item. ( It had before a joint key but Sage is putting through duplicate values).
2. I created a select query, for Stock and the additional Stock details from the supplier on a 1 to 1 relationship.
3. I created a select query between INVOICE and INVOICE_ITEM and extracted data that way.
I want to query alll stock records but sage is not accepting SQL commands for left joins. I just come up stuck with an error message.

I will have then to append the data to two new tables and use them to run faster queries. Querying Sage directly is prone to many crashes. I will just have to look for duplicates and append the new data in, or delete it and then run append.
Anyone had experience in doing that to obtain data for reporting?
All help is greately appreciated.

 

[neileg]

Try creating a simple select query to return all the data from the Sage tables. Then use the query instead of the linked table to perform your tasks.

I'm pleased I don't have to struggle with Sage anymore!

 

[TIbbs]

That's what I tried to do I can create a select query that takes out all invoice data, but that is for the products that have orders .
I needed then to create a left join select query between stock and the invoice-invoice_item query that would take out all stock items that did not have any orders. That's when the problems started, it has crashed all the time and comes with an error message that it does not accept it, I tried using a stockquery instead of the linked table and still nothing, same error or crashing.
Same with using only the tables.
Am stuck at what to do next really

 

[TIbbs]

this is the sql code
SELECT STOCK.STOCK_CODE, INVOICE_ITEM.INVOICE_NUMBER
FROM STOCK LEFT JOIN INVOICE_ITEM ON STOCK.STOCK_CODE = INVOICE_ITEM.STOCK_CODE;

To get all stock items with and without invoice numbers.

 

[TIbbs]

I had a look at the report designer in Sage and for some reason the Join for the InvoiceItem-Stock is the other way round (with Invoice_Item having many Stock).
Probably that's why I am having so many problems. Need to get back to Sage and see what they say.