How to get IDs For AFRs Table ?

[billgyrotech1]

I seem to have redundancy according to the experts here and improper normalization.

In my AFRs table there are for instance ClerkID and Clerk and probably should just be a field for ClerkID. There are others also.

My question is how to get the ID numbers for these fields from the other tables? I will eventually remove the similar fields that are not IDs.



Thank you

[billgyrotech1]

I am sorry for the trouble. I will export to Excel and use formulas to get the IDs.

Thanks anyway

[rpeare]

An example

If you want to populate the TechnicianID field based on the Technician text on the AFR table you would run this query:

UPDATE AFRs INNER JOIN Technicians ON AFRs.Technician = Technicians.Technician SET AFRs.TechnicianID = [Technicians]![ID];

You'd just have to run something similar for each foreign key you wanted to update.

[billgyrotech1]

Thanks but I have no idea what that is or how to run a query.

[rpeare]

... how can you be building a database and not want to deal with queries? They are an essential part of any database.

Your database has a series of tables and a couple of forms that's just scratching the surface of what a database can do and if you limit yourself to those two items you're not going to have a robust system.

To create a query click the CREATE menu option to bring up the create ribbon
Select QUERY DESIGN
Click CLOSE on the SHOW TABLE window pop up if you get it
Click the SQL VIEW in the upper right corner of your query window
Paste in the code I gave you
Change to the DESIGN VIEW of the query in the upper left corner of the query building window

you'll see a very simple query design for updating the technicianID field.

[billgyrotech1]

Thank you that worked very well. I need to do this for the other fields too. It may take some time and hopefully I can accomplish the edit.

Cheers!

[billgyrotech1]

Okay I made queries for the other fields and now the AFRs table has all of the IDs.

Thank you very much for the help.

[rpeare]

You bet, make sure to mark your thread solved