How to update multiple tables with one field

[Phillipc1]

Hello All!

I am new with Access and learning, but I am stumped on something I feel like I may already know the answer but am overlooking. Essentially I work for an equipment rental company and I am trying to create a table with equipment information and the primary key is the Equipment #. I have two additional tables that will reference other information belonging to DOT and Registration information for that specific equipment #. What I am trying to accomplish is that if I input into the main table (Equipment List) a new piece of equipment that the equipment # I enter automatically is input into the DOT Inspection and Equipment Registration table. The purpose is so that I have the equipment list as my main table I enter all of our equipment into, and the two additional tables (Equipment Registration and DOT Inspection) will update with these equipment numbers so that I can now utilize these tables to input data on the registration and DOT for each item. I hope this question was asked properly, but if there are any other details I can provide please let me know. Thank you all in advance!

[Welshgasman]

If it is 1 to 1, and each record will have that information, then I believe that data should be in the Equipment table?

[ranman256]

Run N update queries.

[Micron]

I would caution against using meaningful data as the primary key and would use autonumber instead. To your answer, you accomplish this with main form/subform relationships because you should not be interacting with tables beyond what you might do for preliminary db testing. As an assist, you can create relationships between these tables so that records are properly related. I usually do.

[Gicu]

You shouldn't enter data into the (main) table, you should do it in the main form bound to the main data. On the main form you then add two subforms bound to your Registration and DOT tables and establish the link between the main form and each of the subforms via the Master\Child fields (which in your case will be the Equipment# from the main table).
https://support.microsoft.com/en-us/...a-1e74d6f5f06b

EDIT: see Micron's answer
Cheers,

[orange]

Phillipc1,

First Access does not work well with field/object names that contain spaces or special characters. Best to stick with alphanumerics -always starting with an alpha.

It would be helpful if you could describe to readers what relates these tables in your business.
What is it that relates "the DOT info", "Registration info" and "your Equipment info". I assure you that writing this info will help you clarify some thoughts also.

[Phillipc1]

Thank you all for the responses, this is very informative. In regards to @orange. The relationship for these three tables is essentially that I am with a rental company were each piece of equipment has an equipment # assigned to each piece of equipment. The equipment is trailer mounted and due to this, they require registration information (just like registration for your vehicle) and also DOT registration. So the equipment # is the main relationship to where if I look at my registration table I can verify if this equipment # has a valid registration on file. Same is to be said if I look at the DOT Table, by searching for the equipment # I can see if there is a valid DOT registration on file for this unit. As I said, my main goal is that If I put an equipment # on the main table (Equipment List) it will populate on the registration and DOT tables so that I can then input the registration and DOT information for each piece of equipment.

[Gicu]

That is exactly what the form\subform structure does; when you start a new record in the subform it will auto-populate the value of the child field from the master field of the main form. So if you change your main goal from adding the equipment # in the table to adding it in the form bound to the table then you're off to the races.

Cheers,

[orange]

You may want to review Micron and Vlad suggestions of Form and Subform(s).

??Is all Equipment on Trailers??

Consider, you don't care about DOT registrations that are not part of your equipment and
you don't have Registrations for equipment that isn't yours.
So you could think of things as follows:

I am interested ONLY in my equipment, and if this piece of equipment requires registration, I must Register it in my system, and ensure that appropriate DOT registration is on file. It would also seem necessary to have a mechanism to identify:
-Registration due date for each piece of Equipment

??Does every Registration involve DOT??