Saving a record on a form to multiple places

[SeanMatyas]

Here is what I think will be an easy question, but im not sure...

Lets say I have a databasee, which i do. Lets say that on my main
form, I have a text box called "company". On the same form, I have
command buttons that link to other forms that have to do with this
company, but they are too numerous to put all on subforms within this
form, as I was instructed to make the forms less cluttered.

When I enter a new company's name in the text box, as if im adding it
for the first time, I would like it to update that field to other
tables as well, which all contain that companies name. How do I go
about this?

Thank you in advance for your assistance,
Sean

 

[Guest]

If you have the company name in more than one table, your database design
needs some rework. Additionally, the other tables don't yet have any data
related to the company, so there is nothing to put there.

It appears you need to do some reading on relational database design. The
correct way to handle this is to have a company table that has a primary key
field that is not the company name. That is what Autonumber fields are for.
The reason is, company names change (Singular is now AT&T), but the data
related to the company entity does not change. Primary keys should be a
vaule that does not change. That is because whereever in your database you
need to reference the company, you do not store the company name or any other
company information. You only store the primary key of the company's record
in the company table in a field. That field is known as a foreign key. That
is a key that points to where the data is. This is a basic relational
database design concept.

 

[SeanMatyas]

If you have the company name in more than one table, your database design
needs some rework. Additionally, the other tables don't yet have any data
related to the company, so there is nothing to put there.

It appears you need to do some reading on relational database design. The
correct way to handle this is to have a company table that has a primary key
field that is not the company name. That is what Autonumber fields are for.
The reason is, company names change (Singular is now AT&T), but the data
related to the company entity does not change. Primary keys should be a
vaule that does not change. That is because whereever in your database you
need to reference the company, you do not store the company name or any other
company information. You only store the primary key of the company's record
in the company table in a field. That field is known as a foreign key. That
is a key that points to where the data is. This is a basic relational
database design concept.

--
Dave Hargis, Microsoft Access MVP








- Show quoted text -

Thank you for the help... however, i feel i should clarify a little,
and hopefully I am not completely off base here...

There is a reason i have other tables without information pertaining
to the company itself. It may not be a good one, however. We store
the company's main contact information on one table. Next, we have a
table that lists whether or not this company has been inspected by our
department. We are not required to inspect every company, but it is
important to know which ones we have and have not inspected. Thus, on
a table known as "2007 inspections", it is important to have every
company listed, as that way we can see what percentage of the total
companies we have inspected. On another Table, I have all of the fee
information. Thus, it is important there to have the company listed
as well automatically. Is it suggested that I put these seemingly 3
unrelated pieces of information into one table?

I hope this clears things up... if im still way off base, which i just
might be, let me know as well.

 

[Guest]

No, not at all.
Each should be a separate table.
Your inspection table should have only the company Id, the date of the
inspection, and any other data pertenant to the inspection. You don't need
all the companys to determine the number inspected, or percentages thereof.
That you do with queries.

 

[SeanMatyas]

No, not at all.
Each should be a separate table.
Your inspection table should have only the company Id, the date of the
inspection, and any other data pertenant to the inspection. You don't need
all the companys to determine the number inspected, or percentages thereof.
That you do with queries.

--
Dave Hargis, Microsoft Access MVP








- Show quoted text -

Ok... I have the company ID in my company table as the primary key, as
suggested. I also have the company ID in my inpection and fee tables.
I have defined a relationship between the tables using the company
ID. Yet when i run a query that looks for companies (from my company
table) without an inspection (yes/no field on my inspection table), it
returns zero records because the company ID is not being transferred
to the Inspection table... which is really what my starting question
was, i think... help?

thanks again, i hope we can work this out
Sean

 

[Guest]

The value of the key will not transfer automatically. You can do it either
with a form/subform where the form is the customer and the subform is the
inspection. Then, when you add a new record, it will be done automatically.
Or, perhaps easier, would be to add a combo box to your existing inspection
form that would have two columns, one for the customer name (to satisfy
humans) and one for the key field. Then have the combo bound to the field in
the inspection table that is the foreign key.

This will seem very confusing, I know, but soon it will go off like a light
bulb and be perfectly clear.

 

[SeanMatyas]

The value of the key will not transfer automatically. You can do it either
with a form/subform where the form is the customer and the subform is the
inspection. Then, when you add a new record, it will be done automatically.
Or, perhaps easier, would be to add a combo box to your existing inspection
form that would have two columns, one for the customer name (to satisfy
humans) and one for the key field. Then have the combo bound to the field in
the inspection table that is the foreign key.

This will seem very confusing, I know, but soon it will go off like a light
bulb and be perfectly clear.
--
Dave Hargis, Microsoft Access MVP







- Show quoted text -

Ok... I have it working that way, i still run into a problem
however... when i go to run a query to find the Companies who have not
been inspected (using CompanyID from Company table and Inspection yes/
no or even inspection date from inspection table), it will not return
companies that appear in only the company table but not the inspection
table....even though what i am really in need of is to find out which
companies have no inspection records... where am i going wrong here??

Thank you for your efforts and patience,
Sean

 

[Guest]

You may need a Union query for this.