I've got a database for creating cost estimates for construction jobs.
Within the database there are 2 tables
1. ProjectIdentifier Table
2. CostEstimate Table
The relationship is a 1 to many (projectidentifier to costestimate). There
is an autonumber for the key (ProjID) in the ProjectIdentifier.
During data entry, the user created a new project which the data gets added
as a field in the ProjectIdentifier table, then adds items to the
costestimate portion. This works fine.
They want to be able to copy items from an existing cost estimate into a new
cost estimate (for similar type jobs). I set up forms and queries. The
form lets the user choose which job they want to copy,
Query 1 appends the new Project to the ProjectIdentifier table
Query 2 should append the cost estimate data into the costestimate table.
The problem is, I cannot figure out how to get the new ProjID number which
is the number that ties the relationship together.
I need help figuring out the logic behind this so I can get it working.