Query that Updates Table, but each line does different field...

[jimbofoxman]

I am trying to pull some information from our Quickbooks data. When I do that I get the information below, with the FOB field being the information already in the table. So I want to take the rest of the info and update the table. The biggest issue is that each record in "name" actually needs to be put in its own field. So in the table there are actually seperate fields for 43UT, 70, 43UM and USE. I am drawing a blank on the best way to do an update on the Main Table for each one of those lines, put the "amount" for each "name" into the appropriate field.

fob	doc_num_h	CreatedDate	due_date_h	amount	name
20367	502353	9/14/2012	10/14/2012	199.82	43UT
20367	502353	9/14/2012	10/14/2012	350	70
20367	502353	9/14/2012	10/14/2012	278.19	43UM
20367	502353	9/14/2012	10/14/2012	11.99	USE
20417	502354	9/14/2012	9/24/2012	200.26	43UT
20417	502354	9/14/2012	9/24/2012	300	70
20417	502354	9/14/2012	9/24/2012	183.72	43UM
20417	502354	9/14/2012	9/24/2012	12.02	USE

So for Example the record would be....

FOB: 20367
DOCNUM: 502353
INVOICE DATE: 9/14/2012
DUE DATE: 10/14/2012
43UT: 199.82


70: 350
43UM: 278.19
USE: 11.99
TOTAL: 840 >>> 199.82+350+278.19+11.99

Anyone have any suggestions on the best way to do this?

Thank you

[orange]

I don't know Quickbooks, but it seems to me you have a structure issue. I think you should review you requirement and then modify your query or process to extract and populate a table with exactly what you want.

If you bought Quickbooks, I've seen posts where there is an ODBC link that allows Access to interact with Quickbooks.
There must be some examples of how to do such/similar interaction.