Dear Loungers:
I would like to move my phone number fields from one table to another table. Fields are going from the “many” side to the “one” side of the table relationships. There are many records in the Enrollment Table (“many” side) for each one Referral record (“one” side). The two tables are linked by the ReferralID field.
I am following the directions in the “Update Data from One Table to Another” in this Microsoft article:
http://office.microsoft.com/en-us/access-help/update-data-by-using-a-query-HA010076527.aspx#BM5
Microsoft broad steps outline:
1. Create an update query and add both the source and destination tables to the query.
2. Join those tables on the fields that contain the related information.
3. Add the names of your destination fields to the Field row of the query design grid.
4. Add the names of your source fields to the Update To row of the query design grid by using the following syntax: [source_table].[source_field].
When giving advice please be as detailed and specific as possible! (I don’t have a copy of the database with test people, just real people, so I can’t share it online without a lot of work deleting thousands of records in lots of tables.)
Here are the steps I am working on my test database, which is an exact copy of my production database.
1) Create new blank phone fields in the TBL_Referral. (done)
2) Is this correct: Create a query to selecting the max records of the enrollment date table, along with the referral id. It is called MaxEnrollmentbyRef (done)
SELECT Max(TBL_Enrollment.EnrollmentID) AS MaxOfEnrollmentID, TBL_Enrollment.ReferralID
FROM TBL_Enrollment
GROUP BY TBL_Enrollment.ReferralID;
3) Create a selection query MaxLatestEnrollmentbyRef that type one joins the previous table to TBL_Enrollment. It joins them on Enrollment ID from both tables.
It includes the three phone fields, home, cell and work and the referral ID field. This query now is supposed to only contain all the phone records for the most recent enrollment phones record for each referral.
3) Create a new update query “UpdateQry_TBL_Referral”. I added two data sources to it:
TBL_Referral (destination) and MaxLatestEnrollmentbyRef (source of the phone numbers).
This new update query includes the new blank phone number fields, and under “Update To” section has: [MaxLatestEnrollmentbyRef].[HomeNumberStatus]
4) Link the old and new phone number fields using a “type one” join.
5) When running the append query I get the error: :confused:
“Operation must use an updateable query.”
Thanks!
OffandOnAgain
