Update table from another table

[FurryGorilla]

Lo all.

I've had a look through the forums and have found some similar posts but nothing I seem to do makes a difference.

I have 2 tabels, tblReportTotals and tblReportPlanned. I want to update the field named PlannedTime in the tblReportTotals table with the TotalTime field from the table named tblReportPlanned. There are 25 records in the Totals table and 16 in the Planned field so what I want is a query which will compare the machine numbers in each field to check that they match and, if they do, update the tblReportTotals table.

Unfortunately I've tried using joins but I haven't had any joy. Any help would be appreciated as I can run an update query which sets the fields to a specific value, but not from another tables values.

Many thanks
Chris

 

[Maquis]

Heres the SQL code. Just replace "MachineNum with your primary key field and paste it into the the SQL view of your query design window.

UPDATE tblReportPlanned INNER JOIN tblReportTotals ON tblReportPlanned.MachineNum = tblReportTotals.MachineNum SET tblReportTotals.PlannedTime = [tblReportPlanned].[TotalTime];
Maq B-)
<insert witty signature here>

 

[FurryGorilla]

Thanks Maq.

I couldn't actually do it that way as the database I'm working on wasn't set up by myself and is absolutely terrible. Still, go it working after much fiddling

 

[BNPMike]

First experiment with getting the join to work. In the QBE Grid screen can you get both tables and can you drag and drop a join line between the two machine code fields?

If so you are only 7 seconds a way from solving your problem. just select the update query view and type the fieldname in the update to line. It should equal what Maquis has suggested. mike.stephens@bnpparibas.com

 

[FurryGorilla]

Thanks Mike.

See my last post. You wouldn't believe how much money this person got for producing this database :-0