Results 1 to 3 of 3
  1. #1
    Star Lounger Techie's Avatar
    Join Date
    Dec 2009
    Location
    Philadelphia, PA, USA
    Posts
    58
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Question Moving fields from the many side table of a relationship to the one side using an Update query?

    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/ac...76527.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:

    "Operation must use an updateable query."

    Thanks!

    OffandOnAgain
    Peter
    A one-man support team for a small nonprofit.
    (Meaning everyone in this forum is my best option for help and system support.)
    Techpraise.com - A collection of posts I have written for Windows Secrets Lounge and other things.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,494
    Thanks
    3
    Thanked 42 Times in 42 Posts
    That usually means that one of your tables does not have a primary key - in this case it is probably the query that is getting the latest phone number. To solve that issue, you will probably need to change the MaxLatestEnrollmentbyRef to be a make table query, run it and then set a primary key in that table and join to that table.
    Wendell

  4. The Following User Says Thank You to WendellB For This Useful Post:

    Techie (2014-02-12)

  5. #3
    Star Lounger Techie's Avatar
    Join Date
    Dec 2009
    Location
    Philadelphia, PA, USA
    Posts
    58
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Thanks, it worked. Never seen that documented anywhere...
    Peter
    A one-man support team for a small nonprofit.
    (Meaning everyone in this forum is my best option for help and system support.)
    Techpraise.com - A collection of posts I have written for Windows Secrets Lounge and other things.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •