Replacing 'Name' with 'ID' taken from another table

[quiksilver1984]

I have a main table which contains data on Oilfields. This is set up with an auto number for ID and a name of each oil field. I have other tables which contain things such as Pipelines, Wells and Platforms, which are linked back the the main table.

The problem is that the database was given to me with the links being between the Oilfield_Name, and not using the Oilfield_ID (which is not present in the 'satellite' tables). How can i add the oilfield_ID column to the 'satellite' tables so i can link them properly?! (i can obvioulsy physically add an 'Oilfield_ID' column to the 'satellite field', but how do i populate it with all the correct IDs from the main table, to suit what is in the 'Name' column of the 'satellite' field- does that make sense?). I suppose I need to replace all of the Names with the IDs?

This has been giving me such a headache. The Oilfields table has 2500ish records, with as many as 1200 in the other tables linked to it.

 

[mhartman]

Hello:

Here you go..............
http://www.utteraccess.com/forums/showthreaded.php?Cat=&Number=1459102&page=&view=&sb=5&o=&vc=1

Regards
Mark

 

[quiksilver1984]

Thanks very much for your reply Mark, unfortunately i dont think that quite gets at what im asking. Perhaps i havent explained my problem very well-

I want to add the auto number primary key of main table, as a column in the smaller tables, to use to link to. The smaller tables currenly are using a 'Name' as the link. How can i add this column to tables, in effect, replaing the names in the smaller tables with the correct ID number from the main table?

 

[Pauldohert]

Try - I assume youve backed it up first!

Creat a fkOilfieldID field in the Sattelite table to hold the linkID to the Oilfield table - then a query

UPDATE Oilfield INNER JOIN Sattelite ON Oilfield.OilfieldName = Sattelite.OilFieldName SET Sattelite.fkOilFieldID = [Oilfield].[OilfieldID];

Hopefully the above field names are obviuos enough.

 

[quiksilver1984]

Thanks Pauldohert, ill give that a try later today and let you know how i get on. Some of the records in the satellite tables are given field names which arent present in the main table of oil fields. Will this query assign them a blank ID value where a matching name isnt found in the main table?

 

[Pauldohert]

If I'm understanding correctly it should do. As I say - take a backup and give it a whirl.

The other question to this new bit of info is,why are there satelites with no oilfield? Anyhow...

 

[quiksilver1984]

I've been lumped with a bit of a nightmare database. Full of bugs, mistakes and inconsistencies. Not helped by its sheer size. I'm not a particularly advanced user, but im doing my best to straighten it out. A lot of people in the office have been adding to it over the years, and quite often not in the right way i.e. bypassing the forms to enter various pipelines for a field that doesnt exist, manually into the table.

Could you give me a few more details on the query you've given? Where exactly do i stick that expression?! Like i said, i am not a particularly advanced user... hence me asking lots of questions.

Thanks for your help to this point.

 

[Pauldohert]

Make a new query, one of the options on the toolbar should be SQL view (left most button on the query design toolbar on my XP) - click that and paste the SQL there (with the correct fild table names) - click back to the design window to see whats going on in a graphical view.