update multiple tables at once

[Guest]

I was just wondering if it were possible to update another table after
inserting a new record into another table.

What I have is a table that has a list of names and information. In another
table I have those same names but with much different information. If I add a
record to the first is it possible to have that same name added to the other
table? or should I just consolidate the two tables?

Also I have a subform in one of my forms that gets information from a query.
When I created the subform it allows me to use text boxes and such but when I
run the subform it just come up in Datasheet view. Is there a way to make it
come up in a form view?

Thanks,

 

[John Vinson]

I was just wondering if it were possible to update another table after
inserting a new record into another table.

What I have is a table that has a list of names and information. In another
table I have those same names but with much different information. If I add a
record to the first is it possible to have that same name added to the other
table? or should I just consolidate the two tables?

Storing the name in two tables is in fact a Very Bad Idea. Adding a
record with just the name (and without the information) is perhaps
even worse.

Either consolidate the tables; or if the information is really of a
different kind between the two tables, be sure that you have a unique
Primary Key in the first table, and have a matching foreign key in the
second table. You can use a Subform to link the two.

Also I have a subform in one of my forms that gets information from a query.
When I created the subform it allows me to use text boxes and such but when I
run the subform it just come up in Datasheet view. Is there a way to make it
come up in a form view?

Open the Form you're using in the subform control; view its Properties
in design view; change the Default View property from Datasheet to
either Single or (probably the best of both worlds) Continuous.

John W. Vinson[MVP]

 

[Joseph Meehan]

I was just wondering if it were possible to update another table after
inserting a new record into another table.

What I have is a table that has a list of names and information. In
another table I have those same names but with much different
information. If I add a record to the first is it possible to have
that same name added to the other table? or should I just consolidate
the two tables?

Also I have a subform in one of my forms that gets information from a
query. When I created the subform it allows me to use text boxes and
such but when I run the subform it just come up in Datasheet view. Is
there a way to make it come up in a form view?

Thanks,

Yes it would be possible, but likely wrong.

It sure sounds like you have very poor data organization. It sounds
like you are using tables to store different types of data rather than to
normalize the data.

Since you have names, I guess those are people.

Your first table would normally include things like their name address
employer phone number, things about that person that are one on one. Each
person has one name, one address etc.

The second table might list their cars. Each person may own more than
one car, so you would have a table for cars.

Maybe a third table might be a record of maintenance (like oil changes)
for each car.

Using this you can find all the oil changes Mr. X has listed and which
car the oil changes were done and when. You could also quickly find all the
people who had oil change on December 12, 1997 and also owned green Fords.

The total efficiency of the database would be far better and faster as
well as easier to maintain.