Alter Table to Two Tables 1

[joatmofn]

My tblWorkOrders table has a vehicleID field which is a lookup field to the tblVehicle table. It simply associates a truck number with the work order. This is fine for a one truck per work order job. Now we are assigning multiple trucks to one job/workOrder.

I created a table called tblWorkOrderTrucks and created a 1:M relationship from the tblWorkOrders table to the tblWorkOrderTrucks table. The link between the two tables is the WorkOrderID.

So, for each record in tblWorkOrders that has a truck assignment, I need to create a record in tblWorkOrderTrucks table. The created record will have three fields (ID, type autonumber, WorkOrderID, and VehicleID.

In other words, I need to copy the existing vehicleID and WorkOrderID from the tblWorkOrders table to the tblWorkOrderTrucks table but am not sure how to proceed. Update query, append query, combination of update & append query, vba,?

Just not sure about this, so any input would be appreciated.

Thanks.

 

[jrbarnett]

This is a fairly simple INSERT query:

insert into tblWorkOrderTrucks (WorkOrderID, VehicleID)
select WorkOrderID, VehicleID
from tblWorkOrders

Just copy this and paste it into a query design SQL window, then run it. It assumes the table is currently empty.

John

 

[joatmofn]

I added a criteria to your query and that did the trick. Thanks a bunch - here's a star!