well, "fixing" the existing data is a little trickier, then - but again, you
only have to do it once, so no need to go through a big automation thing,
and just be sure to back up your db before you begin the "fix".
so each inquiry record only has one related status record, however each
status record may have multiple status activity records. here's what i would
do:
1. start by opening the Relationships window and deleting the links between
the three tables.
add the fields from the Status table table to the Inquiry table.
include the primary key field, but if its' data type is Autonumber, change
it to Long Integer when you add it to the Inquiry table.
note: do NOT include the foreign key field that is currently linking the
Status table back to the Inquiry table.
the easy way to do this is to simply open the Status table in design view,
highlight the fields you want to add to the Inquiry table, press Ctrl+C.
then open the Inquiry table and paste (Ctrl+V) the fields wherever you want
them. this action puts the fields, including their properties, into the
table - but NOT the data. we'll get to that.
2. run an Append query to append the data from the Status table into the
Inquiry table. again, make sure you also append the primary key field of the
Status records.
3. in the StatusActivity table, add a new field to the table, i'll call it
fkInquiryID. this field will hold the primary key value from the Inquiry
table, as a foreign key. so you need to make sure the data type matches the
Inquiry table pk field's data type (remember, when the pk field is
Autonumber, the fk field is Long Integer).
4. create a SELECT query using the Inquiry table and the Status Activity
table. link the two tables on the Status pk field (that was originally in
the Status table) and the Status Activity's fk field. open the query in
datasheet view and review it to make sure the records are matched up
correctly.
5. when you're satisfied that the SELECT query is correct, go back to query
design view and turn the query into an Update query. Update the fkInquiryID
field with the primary key from the Inquiry table (the "real" primary key,
not the pk field from the Status table).
6. now the Status Activity records are linked directly to the Inquiry table.
delete the "old" foreign key from the table - the one that linked it to the
"old" Status table.
in the Inquiry table, delete the pk field from the "old" Status table.
delete the old Status table. go back to the Relationships window, and link
the Status Activity table to the Inquiry table.
if you understood all this, you can probably do the whole thing in about 10
minutes. but if it was clear as mud, let me know and i'll throw together a
demo db that i can email to you so you can see how to set it up.
hth