Relationships & Autofill

[Jeff Bodin]

HELP!

I am building what could be a flat file database, but
because of the amount of data I will be tracking, it would
be better if it were multiple (5) smaller tables.

I have ONE common Field Name (Called "Address") between
what I will call the "Table1" table and the other 4 tables
("Table2", "Table3","Table4", and "Table5").

Because I need the information in the "address" key to be
specific to that Field Name across all tables, I would
like any tables to automatically create a record in all
other tables whenever I place a new address in one table
(consequntly, each table should have the same number of
records).

i.e., If enter the address "1313 Mockingbird Lane" into
the address in Table 1, Tables 2-5 all create a new record
with this "1313 Mockingbird Lane" in their respective
address fields).

Right now, I can't figure out how to do this without
having to type _the_ specific address in all other 4
tables. What this will lead to is a different number of
records in each

Is it going to be easier to just go to a flat file? Or am
I missing something simple here?

I tried making "Address" a primary key in each table, and
having a 1:1 relationship established between:
"Table1.Address" and Table2.Address",
"Table1.Address" and Table3.Address",
"Table1.Address" and Table4.Address", and
"Table1.Address" and Table5.Address".

But that doesn't seem to work - It allows me to enter any
data I want in Table1.address, but enter only what's in
table1.adress into the other table's.address fields. I
need this to basically autofill (or I will wind up with
more records in table 1 than I will tables 2-4).

Thanks,

- Jeff

 

[Drew]

Help is right!

Flat databases are troublesome, non edit freindly and
insane for anyone serious about storing data.

What you need is a single table that houses addresses.
Then, in each of the other table, you only store the ID
of that address (if so needed) Your address table should
have a Autonumber field called Address_ID. WHen you save
the address record, you simply need to open the other
table you want to related this address to and save the ID
number into the respective table.

I am not sure why you are concerned about the tables all
being the same size. This is rare and should never be a
concern to any database development.

Post the fields of all your database and I would be that
the Relational Gods would show a much better way to
organize your data.

ds

 

[John Vinson]

HELP!

I am building what could be a flat file database, but
because of the amount of data I will be tracking, it would
be better if it were multiple (5) smaller tables.

Five *identically structured* tables? Unless you have upwards of a
gigabyte of data in each of these five tables, I must strongly suspect
that this will NOT provide you with any advantage. Or if it's five
tables to get around the 255 field limit of a single table, you really
should consider either a) normalizing your data and getting away from
flat files or b) using some software other than Access.

I have ONE common Field Name (Called "Address") between
what I will call the "Table1" table and the other 4 tables
("Table2", "Table3","Table4", and "Table5").

Because I need the information in the "address" key to be
specific to that Field Name across all tables, I would
like any tables to automatically create a record in all
other tables whenever I place a new address in one table
(consequntly, each table should have the same number of
records).

Creating empty "placeholder" tables is essentially NEVER either
necessary nor a good idea.

But that doesn't seem to work - It allows me to enter any
data I want in Table1.address, but enter only what's in
table1.adress into the other table's.address fields. I
need this to basically autofill (or I will wind up with
more records in table 1 than I will tables 2-4).

That's not how relationships WORK. If you enforce relational integrity
you will be *prevented* from entering an invalid record in 2-4, but it
will not automagically create such a record.

I REALLY suspect that you need to stop, step back, and look at your
data structure! Do you have some one-to-many relationships embedded in
each record? What are some typical fields for these tables?

If you do want to work with this structure, consider using a Form
based on Table1 with three Subforms based on Table2, 3 and 4, using
Address as the master/child link field. You cannot (at least not at
all easily) ensure that data MUST be entered in all four tables (it's
a chicken or egg problem; you have to enter data into Table1 first,
before you put anything into the others, so there is a time when data
exists only in one table!)

If you work with Access (as a relational database) instead of
struggling against it, you'll find that it's perfectly capable of
solving your business problem - just maybe not the way you've chosen
to attack that problem!