Update Master Table with Temporary Table

[kagoodwin13]

I was given a project in which I must take a very inefficient method of gathering, organizing, and entering data, and make it work.

That being said, here is what I am faced with:

I have an Excel file that I must input into the master table. The Excel file, temporary table, and master table are all structured the same. The table is structured like this:

PART_NUMBER	OCT13	NOV13	DEC13
1234567890	1000	1000	1000
1234567891	2000	2000	2000
0	0	0	0
0	0	0	0
0	0	0	0
0	0	0	0

The Excel input form was designed to handle 100 part numbers, even if a particular input only required 1 or 2 part numbers. Therefore, there are a whole lot of zeros that must be stripped before I can work with it.

The best process I can think of is:

1. Make an input form with tblTEMP as the Recordset (Done)
2. Create a button to execute TransferSpreadsheet into a temporary table (Done)
3. Strip the records with "0" part number (DELETE * FROM tblTEMP WHERE PART_NUMBER = '0')
4. Update the master table with results that are left

So far I have this code, but I seem to be getting the syntax wrong:

UPDATE tblMASTER SET OCT13 = & Me.OCT13 &

I am also needing to set up a For loop, something like:

For Each PART_NUMBER in tblTEMP, run the update query

Thanks for any help you can provide! If you have any other ideas on how to input this data, please let me know.

[Dal Jeanis]

If you've already created the temp table with the records in it, you shouldn't have to loop anything. If you just want to update the MASTER tabel with the values in the TEMP table, then use something like this -

strSQL = "UPDATE tblMASTER, tblTEMP " & _
         "SET tblMASTER.OCT13 = tblTEMP.OCT13 " & _
         "WHERE tblMASTER.PART_NUMBER = tblTEMP.PART_NUMBER;"
docmd.runsql strSQL

There's a little more code you should have around it - error trapping and so on, but that's the basics.

Appending the temporary records into the master is just as easy.

[kagoodwin13]

Thanks, that worked!