Trouble with UPDATE query 1

[Glohamar]

I have two tables. PAMMain has an AutoNumber field, DwgNumID. My other table, PAMInfo, originally didn't have a field that I could use to join via the DwgNumID. I have a problem where I need to create the DwgNumID field on my table PAMInfo so that I can use that field to join instead of the DwgNum field. Currently I am using the DwgNum field to join the two tables for a one-to-many join.
So I have tried an Update query to update the DwgNumID field on table PAMInfo to match the DwgNumID field from table PAMMain where the DwgNum field are equal between the two tables.

This is my query

UPDATE PAMInfo
SET DwgNumID = PAMMain.DwgNumID
WHERE PAMInfo.DwgNum = PAMMain.DwgNum

Any help would be appreciated.

Dave

 

[dhookom]

You didn't tell us the results of your attempt. What is your error message?

Have you tried this same query after creating a unique index on PAMMain.DwgNum?

Duane
Hook'D on Access
MS Access MVP

 

[Glohamar]

Thanks for your help Duane.

When I go to view the results of the query I get a prompt to enter PAMMain.DwgNum. I leave it blank and nothing happens.

The unique index on PAMMain was DwgNum. I have changed it to be DwgNumID. I designed this table a about two years ago when I was learning, and I did not plan for the future where I could have a need to have the same DwgNum listed more than once in PAMMain. That is why I am needing the DwgNumID.

Thanks again for your help.

Dave

 

[dhookom]

Apparently you got rid of the field PAMMain.DwgNum so you can't use it to join to your other table. Did you get rid of the field in your main table that was used to link to your info table?

Duane
Hook'D on Access
MS Access MVP

 

[Glohamar]

No, it is still there. That is what is driving me crazy. See currently I am still using the DwgNum field to create the one-to-many relationship.

Thanks for helping.

Dave

 

[dhookom]

Assuming you have a field named DwgNum in PAMMain, can you make it a unique index? If not, your query will not be updateable.

Duane
Hook'D on Access
MS Access MVP

 

[Glohamar]

Thanks for you time Duane.

PAMMain.DwgNum is already an unique index. I am going to include a portion of my table design to hopefully help. My relationship is the DwgNum field from the PAMMain to the PAMInfo.

PAMMain
DwgNumID AutoNumber
DwgNum Text (Primary Key)
EC Text
Description Text

PAMInfo
RevID AutoNumer (Primary Key)
DwgNumID Number
DwgNum Text

 

[dhookom]

Looking back at your query, it doesn't include the PAMMain table in the FROM clause. Not only can't it find the field, it can't find the table...

Duane
Hook'D on Access
MS Access MVP

 

[Glohamar]

Thanks for you patience Duane.

I have added FROM PAMMain to the query.

UPDATE PAMInfo
SET DwgNumID = PAMMain.DwgNumID
FROM PAMMain
WHERE PAMInfo.DwgNum = PAMMain.DwgNum

I am now getting the following error and not sure how to resolve.

Syntax error (missing operator) in query expression 'PAMMain.DwgNumID FROM PAMMain'.

Any sugestions?

Thanks for your time.

Dave

 

[dhookom]

The tables must be joined. Try something like

UPDATE PAMMain INNER JOIN PAMInfor ON PAMMain.DwgNum = PAMInfo.DwgNum SET PAMInfo.DwgNumID = PAMMain.DwgNumID;

Duane
Hook'D on Access
MS Access MVP

 

[Glohamar]

When I ran the query, the DwgNumID fields were blank. I know it ran through all the rows because at the bottom it showed the total records of 7271. I am wondering why it didn't update the fields? Does it matter if the PAMMain.DwgNumID field is an AutoNumber type?

Thanks for you help.

Dave

 

[Glohamar]

Duane, I went ahead and ran the update query and it worked. The PAMInfo.DwgNumID field updated. Curious why it did not show when I just did the view of the query.

Anyway, I really appreciate your time in helping me solve this query. I learned something ans will be able to finish the other updates I need to do.

Many Thanks.

Dave

 

[dhookom]

When you were viewing the datasheet, it was showing the current contents of the field. They hadn't been updated yet.

Duane
Hook'D on Access
MS Access MVP