Currently I do something like the following in code, but was wondering if it can by done through just an action query? I have tblEquipment which has information on pieces of equipment, and I have tblCharacteristics which has characteristics that are required to describe a piece of equipment. For example if the piece of equipment is a vehicle I might need top speed, mile per gallon, seating capacity. If it is a rifle i need characteristics like muzzle velocity, range, round per minute.
When I add a new piece of equipment I base it on a similar piece of equipment. I make a copy of all the characterics of a car in tblCharacteristics, change the equipment foriegn key to equal the primary key of my new piece of equipment, and clear the characteristice value. Here is an example
tblEquipment
equipmentID equipmentName
1 Car One
2 Rifle One
tblCharacteristics
equipmentForiegnKey characteristicName characteristicVal
1 Speed 100mph
1 Fuel Efficiency 20 mpg
1 seating capacity 5
2 range 500 m
2 Firing Rate 100 rounds/min
Now If I add a new car, I have an interface to say that it is like item 1. The code copies the characteristics from item i and copies those characteristics back into tblCharacteristics. It thens changes the foriegn key to the new equipment ID. It also clears out the characteristic value. In other words it adds the following to tblCharacteristics:
equipmentForiegnKey characteristicName characteristicVal
3 Speed
3 Fuel Efficiency
3 seating capacity
The user now has the appropriate characteristics and just needs to fill in the values.
So my question is can I write a combination Append and Update query that appends the selected characteristics, and updates the foriegn key (and other fields)for those characteristics. What is the syntax for a conbination update append?
When I add a new piece of equipment I base it on a similar piece of equipment. I make a copy of all the characterics of a car in tblCharacteristics, change the equipment foriegn key to equal the primary key of my new piece of equipment, and clear the characteristice value. Here is an example
tblEquipment
equipmentID equipmentName
1 Car One
2 Rifle One
tblCharacteristics
equipmentForiegnKey characteristicName characteristicVal
1 Speed 100mph
1 Fuel Efficiency 20 mpg
1 seating capacity 5
2 range 500 m
2 Firing Rate 100 rounds/min
Now If I add a new car, I have an interface to say that it is like item 1. The code copies the characteristics from item i and copies those characteristics back into tblCharacteristics. It thens changes the foriegn key to the new equipment ID. It also clears out the characteristic value. In other words it adds the following to tblCharacteristics:
equipmentForiegnKey characteristicName characteristicVal
3 Speed
3 Fuel Efficiency
3 seating capacity
The user now has the appropriate characteristics and just needs to fill in the values.
So my question is can I write a combination Append and Update query that appends the selected characteristics, and updates the foriegn key (and other fields)for those characteristics. What is the syntax for a conbination update append?
