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

[snag46ed25]

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!

[RuralGuy]

You may find this link helpful.

[snag46ed25]

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!

[RuralGuy]

That's great! It sounds like your are ready to apply the Solved thread tool.