updating a table using an update query

[monplankton]

Hi, I'm using access 2003 and want to update my table with a time but first I need to calculate a total time from the results within the table.

The table consists of OK, NG, and rework numbers and I need to calculate how long the job took by adding the results together and then multiply it by a time per part which is stored in a seperate table.

This in my querie line:
Timings: ((([Qty_OK]+[Qty_NG])*[InspOKPArt])+([PartsReworked]*[InspReworkPart]))/60/60

The problem I have then is how to update the table with the result for each record!!!!! Every thing I've tried it just keeps saying invalid name. Make sure it doesn't contain invalid characters or punctuation, what does that mean!
:banghead:
Any help?

 

[Isskint]

The first problem is in your formula. You say the time per part is in seprate table so you would need to use DLOOKUP() to return the value.

(FieldA+Fieldb) * Dlookup("Rate","RatesTable","PartID=" & me.PartID)

This formula then needs to be written in the UpdateTO row of your UPDATE query

 

[monplankton]

Thanks for this. I'm still wandering how I get the total from seconds to hours?

 

[Isskint]

If you just want x number of hours, then divide by 3600. If you want to present the value as HH:MM etc then use TimeSerial(0,0,seconds)

 

[monplankton]

Thanks great help,

This is what I ended up with:

(([Qty_OK]+[Qty_NG])*DLookUp("[InspOKPArt]","[tbl_Supplier_Inspection_Request]","[NCMARNumber] ='" & [NCMAR] & "'")+[PartsReworked]*DLookUp("[InspReworkPart]","[tbl_Supplier_Inspection_Request]","[NCMARNumber] ='" & [NCMAR] & "'"))/3600