Results 1 to 4 of 4
  1. #1
    snag46ed25 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    2

    Exclamation Using JOIN to connect two tables then update common fields CAN'T EDIT HEADACHE

    Hi all,

    So I have a major problem, PLEASE someone lend a brain.

    I have two tables that are used to log our vehicle assists. These tables come from two sources, table main_SurveyAdded is "our" table that we made changes too, and main is our client's table that we do not want to make changes too, EXCEPT to add two columns, "Ratings" and "Waittime", that are in main_SurveyAdded.

    These two tables have the same information in them (i.e. they are identical tables) except that I have added Ratings and Waittime to main_SurveyAdded, in addition to 15 or so records that are not in main.

    These tables relate to vehicle assists. Everytime one of our vehicles assists a client, they give out a survey. Therefore, there are two columns, both named SurveyNum in each table. You would think that these would be unique, but the database guys who set up the original table did not do this, and its making things difficult. If a driver is lazy he can enter 1234 or 0 to skip entering the unique survey number, so there is a lot of this "bad" data. Again, I did not create this DB.

    What I want to do is take the Ratings and Waittime information, with respect to SurveyNum, in main_SurveyAppend and add it to the newly created empty columns in main where main_SurveyAdded.SurveyNum=main.SurveyNum

    main_SurveyAdded has an autonumber for a PK, and main does not have a PK.

    Here is my SQL statement for the JOIN:
    SELECT *
    FROM main RIGHT JOIN main_SurveyAdded20090731 ON main.SurveyNum = main_SurveyAdded20090731.SurveyNum
    WHERE main_SurveyAdded20090731.Ratings <> Null OR main_SurveyAdded20090731.Waittime <> Null
    ORDER BY main_SurveyAdded20090731.SurveyNum;


    My problem is that I can't edit the table in the datasheet view (which I was really hoping to be able to do) to fill in the empty columns with the columns right next to it that show Ratings and Waittimes...

    Everytime I try to type in the sheet it donks and doesn't do anything...I set the Properties of the Query so Unique Values is NO, but its still not working...?

    I also tried to run an update from the above join, setting main.Ratings=main_SurveyAdded.Ratings Where main_SurveyAdded.Ratings <> Null and main_SurveyAdded.Waittime <> Null, but this doesnt work either...



    HELP!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You may find this link helpful.

  3. #3
    snag46ed25 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    2

    Thumbs up

    Thanks rural guy, that helped clear up my confusion. I tried an Update query with:

    UPDATE main RIGHT JOIN main_SurveyAdded20090731 ON main.SurveyNum=main_SurveyAdded20090731.SurveyNum SET main.Ratings = main_SurveyAdded20090731.Ratings
    WHERE main_SurveyAdded20090731.Ratings Is Not Null;


    and that did exactly what I wanted!

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That's great! It sounds like your are ready to apply the Solved thread tool.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Common Dialog control / ShowOpen method
    By phuile in forum Forms
    Replies: 0
    Last Post: 04-10-2009, 12:16 AM
  2. Replies: 5
    Last Post: 03-29-2009, 07:20 AM
  3. update tables via forms
    By jazoo in forum Forms
    Replies: 0
    Last Post: 09-16-2008, 05:54 AM
  4. Update with LEFT JOIN
    By mcarthey in forum Access
    Replies: 1
    Last Post: 08-27-2008, 10:49 AM
  5. Update Tables
    By lschlitt in forum Programming
    Replies: 2
    Last Post: 05-28-2006, 02:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums