Update query???? or something else

[equaliser]

I have a Db up and running. It logs parts in batch No's. amongst other things. I now want to import extra historical batch data that is in excel to populate fields in the Db. The batch No. links the 2 together. I've imported the info into a temporary table with no problems. How do I now get it update the batch specific info from the one table to the other.

Ie batch 111 shows 3 parts were rejected in table 1 (temporary table). I want to update the rejected field for batch 111 in table 2 to 3.

I dont want to link the table because it means I'll have to go in and adjust a lot of other settings in the Db.

Many thanks

EQ

 

[Epic]

you do not have to adjust anything... just create the update query and link the tables at qry level.
Addıtionally, If you data in excel is in the same format always, link the excel fıle and will behave like any other table. You can also write back to the spreadsheet from ms access.

 

[equaliser]

Thanks answering

Ive selected the field I want to update. IN the update to I've put the following

[GRN INPUT excel info]![Total Parts] I get the error "TYPE MISMATCH IN EXPRESSION" On other sites they have suggested replacing the ! with . still the same error.

 

[neileg]

Canyou post the SQL of your query.

 

[equaliser]

UPDATE [GRN INPUT_excel_info] INNER JOIN [GRN_INPUT] ON [GRN_INPUT_excel_ info].[TSTL_Job_Number] = [GRN_INPUT].[TSTL_BATCH_NUMBER] SET [GRN_INPUT].[QUANTITY_RECEIVED] = [GRN_INPUT_excel_info]![Total_Parts];


I hope this means more to you than it does me????? LOL

 

[Ryko]

Make sure the two fields you use to join both tables together are of the same data type.

 

[equaliser]

God I feel an idiot the linked batch numbers one was a text field one was a number. Works now. Many thanks to you all.

Another question though on the same theme, I have a field that is either live or completed. How do i build an expression to look at the date field if its populated change the first field to completed?