Relationships...SomethingName to SomethingID (1 Viewer)

[littlegod]

Hi everybody,

i started 4 weeks ago to teach myself ms access in order to create a db that will enable me to store the results of a survey. whilst working on the db creating tbls for locations, divisions, subdivisions, teams etc etc i became aware of the capabilities of this beauty.
not only was i going to be able to record my survey findings, nah, by following all tutorials patiently i might even be able to create a lovely front end enabling staff to participate in the survey over the network. i'm also in the process of using the db to track and trace all the paper records in our company.
however, working through my tutorials i now became aware of a few errors in the design of the db.
i originally set up the majority of relationships between the tbls by using the lookup wizard whilst creating a tbl.
because of my ignorance at the time i asked to link to the TopicName of existing tbls instead of TopicID. later when i came to understand that using the ID is better, i still reasoned that inputting data (which unfortunately is also done by me) is much easier when linked to the Name rather than some ID that i would have to look up all the time.
so here are my questions
is there a way to change the TopicNames automatically to TopicID instead of having to input all the data again? i did try to change it in 'edit relationship' in the relationships window, but the data stayed the same in my tbls.
i'm still not sure of how to create the forms to make it easier for me to input data? i wouldn't want to have to sit there, trying to have two tables open, looking up IDs all the time.

i hope i explained everything, sorry about the very basic question

thank you
dan

 

[FoFa]

If done properly, you never have to know what the ID is to obtain the description. You will always just see the description, even if the ID is what is stored. That is pretty what comboboxes and list boxes are about. Show one thing, but store something else.
Changing the data at this point is pretty much an effort in update queries and changing your refereneces (most likly by breaking them, than re-creating them).

 

[Pat Hartman]

If you want to go from storing the text value to storing the ID value as the foreign key, you need to do several things.
1. Remove the existing relationship by deleting it (not the tables).
2. Add the SomeNameID column to the many-side table.
3. Create a query that joins the one-side table to the Many-side table on the "text" field.
4. Change the query type of step #3 from Select to Update.
5. Update the new SomeNameID column with the value from the joined one-side table.
6. Verify that all IDs have been updated.
7. Delete the now extraneous text field.
8. Recreate relationships and remember to enforce RI.

I know it seems like a lot of steps but it is really a simple process.

 

[littlegod]

Thank you both for taking the time to answer my question,

this helps tremendously,
if i may just ask one more thing (for now...lol)
Step 8 Recreate relationships and remember to enforce RI.
i dont seem to be able to do this if not all the fields in the child tbl are filled in,
let's say i've got a tblSubDivision in a relationship with tblTeam, if not every Team has a SubDivision assigned to it, i can't join them with RI, despite the Field in tblTeam set to NULL
whilst i'm building the db, i have to rely on the company and their structure, which is changing in the moment, so i can't yet decide on all SubDivisions...

i know i'm a pain, sorry

dan

 

[Pat Hartman]

You may already have bad data in the fk field if RI wasn't enforced previously. Use the query wizard to create an unmatched query so you can locate the many-side records that have invalid values in the fk field. Once you fix the data, you will be able to enforce RI.

 

[littlegod]

Hi Pat,

Sorry about this, but i can't get the update query to work,
I'm not sure where to join the tables and what to update to?
I'm very grateful for your help, you are helping everywhere on here and i feel quite guilty taking up your time.
I'd like to let you know that i am trying to get through my tutorials to come up with answers myself, i'm not just hoping for people to help me with every little problem i have. I try to be helpful to others too, if i can.

Thank you very much,

Dan

 

[FoFa]

Typically you use an autonumber in your lookup tables (such as your Topicnames table). Where today you are storing the actual TopicName in your master record, after the change you would be storing the TopicID instead to act as a POINTER back to the proper TopicName record.
So you need to 1 - setup a TopicID field (FK) in your master record to hold this value. (I assume you have an autonumber already in your Topics table). 2 - Update the master with the proper TopicID by using the current TopicName to join the tables. Once all the proper TopicID's are in place in your master record as a FK, then you can break the current relationship (if you have not already), and create a new one using the ID instead. Once that is all fine and good (you do have a backup before you started right) then you can remove the TopicName column from your master record as it is no longer needed.
TopicTable
Key Topicname
6 -- Main Topic 123
48 - Sub Topic 543

master table
Today stores "Main Topic 123"
Tomorrow stores 6 (FK pointing back to Main Topic 123)

 

[littlegod]

Thank you so much FiFo and Pat, i got it sorted now...

I am in the process of creating some forms now and then when i have decided on exactly what information i will need to collect, i will actually begin to work on the tracing application part of my db. Wish me luck...

Thank you so much,

Daniel