Update Query help

[JimUK]

I have 2 tables as follows:

tblEquipment tblMM_PCN

PCN (serial number) OLD_PCN
New_PCN MM_PCN
Model
Location
Due_Cal
Comments

WHat I am trying to do is update New_PCN in tblEquipment with the new changed number (MM_PCN). tblEquipment.PCN and tblMM_PCN.OLD_PCN are the primary keys and equate to each other.
Basically, I was given a listing of the old_pcn and the new mm_pcn and told to update my table (tblEquipment).

I have tried a make table query and update query and nothing shows up after query. What is the best and easiest way to do this? Any help greatly appreciated.

Jim

 

[afryer]

Hi,

Try something similar to this:

dim l_rec_set as recordset
dim l_str_sql as string
dim l_counter as integer
dim l_key as integer
dim l_new_value as string

l_str_sql = "SELECT * FROM tblEquipment"
set l_rec_set = currentdb.openrecordset (l_str_sql)
l_rec_set.movelast
l_rec_set.movefirst

for l_counter = 1 to l_rec_set.recordcount
    l_key = l_rec_set.fields![PCN]
    l_new_value = dlookup ("MM_PCN", "tblMM_PCN", "OLD_PCN" = key)
    l_str_sql = "UPDATE tblEquipment SET NewPCN = " & l_new_value
    l_str_sql = l_str_sql & " WHERE PCN = " & l_key
    currentdb.execute l_str_sql
next l_counter

It will need tidying up, but should work.

Andrew

 

[BSman]

You mention that there is a relationship between the two tables. If by this you mean you have defined a relationship, then first you should drop the relationship.

Then create a update query something like this:

UPDATE tbl_Equipment SET New_PCN = tbl_MMPCN.MM_PCN WHERE tbl_Equipment.New_PCN = tbl_MMPCN.OLD_PCN

You can use the query builder grid by linking the two tables by New_PCN to OLD_PCN and setting it as an update query where New_PCN is set to the value in MM_PCN (which is what the query I've listed should do).

 

[BNPMike]

What would dropping a relationship do?

 

[BSman]

A relationship is used to control data entry and reduce errors. So a typical relationship would say that there is a one to many relationship between a "lookup" table and a data table. For example, you might have a table of equipment with an ID column and a second table containing maintenance data that includes the equipmentID. The relationship would insure that the equipment ID must be in the equipment table before it can be stored in the maintenance table. You can also make the relationship include cascade updates and cascade deletes, so if you change an equipment ID the new value is automatically posted to the records that contained the old value, and if you delete a record from the equipment table all corresponding records in the maintenance table are also deleted (this usually involves confirmation prompts before it actually occurs).

Your table with the old and new PCNs is really just a temporary table to enable you to update the PCN values. If you have defined a relationship, the relationship will prevent you from changing the New_PCN values because the values you enter must match the Old_PCN values in the conversion table. That's why you have to delete the relationship.

 

[BNPMike]

I see, you're talking about referential integrity. Incidentally I can't see where relationships were mentioned.

WHat I am trying to do is update New_PCN in tblEquipment with the new changed number (MM_PCN). tblEquipment.PCN and tblMM_PCN.OLD_PCN are the primary keys and equate to each other.

Jim says he is updating the field New_PCN which is not the key, so it is unlikely he would have set any integrity constraint on this. Not impossible, I agree.

Jim[
Show us your SQL.

 

[JimUK]

Thanks for the comments. Yes I did have a One to many relationship between Old_PCN and PCN and maybe this is why I was not getting anydata.

All i'm trying to do is update the Equipment tables New_Pcn field with the latest changes, which were unfortunately given to me in an excel spreadsheet with only OLD_PCN refering to MM_PCN (or what will become the New PCN). Any way that I can get this process done is fine by me, either creating new table or updating the existing Equipment table, but only some of the PCN's are changing and that's why I thought I would have to tie with the relationship.

Jim

 

[BNPMike]

Copy the SQL from the SQL window and paste it here.

 

[JimUK]

Sorry here's the sql I was using.

UPDATE MM_PCN LEFT JOIN Equipment ON MM_PCN.OLD_PCN = Equipment.PCN
SET MM_PCN.MM_PCN = [EQUIPMENT].[NEW_PCN];

When I did as a select query it returned the MM_PCN, but when I changed to update the tblEQUIPMENT.NEW_PCN is blank.


Jim

 

[BNPMike]

Dont you mean:

SET [EQUIPMENT].[NEW_PCN]=MM_PCN.MM_PCN ;

 

[JimUK]

DOH!

Yeah that might make the difference! Thanks I'll give it a go.

Jim

 

[BSman]

Which is the update query I suggested!