Link Fields in Table

K

Kenny Anderson

I apologize if this is in the incorrect NG or if this topic has been
covered. It didn't appear to fit in any other group and apparently OE Find
will only search read messages.

I want to create a simple link between two tables within the same DB. I
have tried the methods in Access Help, but none have accomplished what I'm
seeking. I have two types of data separated into two tables, but the data
applies to the same individuals. For example, in Table1 I have:

Name
Rank
Serial Number
Dummy1
Dummy2

In Table2 I have:

Name
Rank
Serial Number
Dummy3
Dummy4

I would like Table2 to read in the fields Name, Rank and Serial Number from
all records in Table1. The "Dummy" variables are then characteristics of
the individuals in Name, Rank and Serial Number, separated into two groups,
Table1 and Table2.

I think the answer lies in File|Get External Data>Link Tables... but I tried
this to no avail. Can anyone give me the steps to accomplish this? TIA.

Kenny Anderson
[email protected]
(remove e'x'es to reply)
 
J

John Vinson

I apologize if this is in the incorrect NG or if this topic has been
covered. It didn't appear to fit in any other group and apparently OE Find
will only search read messages.

I want to create a simple link between two tables within the same DB. I
have tried the methods in Access Help, but none have accomplished what I'm
seeking. I have two types of data separated into two tables, but the data
applies to the same individuals. For example, in Table1 I have:

Name
Rank
Serial Number
Dummy1
Dummy2

In Table2 I have:

Name
Rank
Serial Number
Dummy3
Dummy4

I would like Table2 to read in the fields Name, Rank and Serial Number from
all records in Table1. The "Dummy" variables are then characteristics of
the individuals in Name, Rank and Serial Number, separated into two groups,
Table1 and Table2.

I think the answer lies in File|Get External Data>Link Tables... but I tried
this to no avail. Can anyone give me the steps to accomplish this? TIA.
Click to expand...

No; the Get External Data options involve linking separate tables *in
different .mdb files* (or other database files) on your disk.

The Relationships window is where you specify relationships (sometimes
confusingly also called "links") between tables within the same
database.

HOWEVER! Your design has some *real problems*.

The whole POINT of relational databases is to prevent redundancy,
thereby preventing redundancy. If the Serial Number is unique within
Table1, then you do not need to - and in fact SHOULD NOT - store the
name and rank in *any* other table; instead, you would store the
Serial Number only, and use a Query joining the two tables to
determine that person's name and rank. If you store the name and rank
information twice, then it can be extremely difficult to ensure that
SerialNumber 333-33-3333 isn't Sgt. Marie Franck in Table1 and Capt.
Steve Sandoval in Table2 (because of a typo somewhere in the data
entry process).

And, if each person should have only one value of each of Dummy 1
through Dummy4, then (as suggested elsethread) there is no need for
two tables; just have the four fields in the same table.

John W. Vinson[MVP]
 
K

Kenny Anderson

After the two posts, here, it is obvious I didn't give enough information,
but I can draw on at least a couple recommendations, and I have more
questions as well.

I used a simplified model of my database, and I should have been more clear.
Obviously, if I only have four fields, it would be wise to have it located
in one table. In my case, I have 34 fields in one table and 22 in
another--excluding the "Name, rank, and serial number". Furthermore, these
fields can be broken into two different data groups, which is why I'd like
to separate them into separate tables. In table view, I'd like to be
looking at only one data type at a time.

I never intended to enter the name, rank and serial number twice. Rather, I
intended for Table2 to reference this information from Table1. This would
give the user a point of reference for entering data into the table or for
viewing the table. What I now realize is that this reference is better
achieved through the use of a query. That would force me, however, to
probably use a form for data entry, rather than just utilizing a table.
Still, I would need a way to link the information in Table2 to the correct
individuals in Table1, if I'm not referencing or repeating those three
primary fields.

I'm sure users here are dealing with tables with hundreds of fields, not
merely 59, so dividing the information into two separate may not seem
logical. I just thought it made sense, given that I'm dealing with two
fairly unrelated data groups, and it would create two simpler tables rather
than one very large one.

Any additional thoughts? Or should I just give up and make one table?

Thanks,
Kenny
 
K

Kenny Anderson

After the two posts, here, it is obvious I didn't give enough information,
but I can draw on at least a couple recommendations, and I have more
questions as well.

I used a simplified model of my database, and I should have been more clear.
Obviously, if I only have four fields, it would be wise to have it located
in one table. In my case, I have 34 fields in one table and 22 in
another--excluding the "Name, rank, and serial number". Furthermore, these
fields can be broken into two different data groups, which is why I'd like
to separate them into separate tables. In table view, I'd like to be
looking at only one data type at a time.

I never intended to enter the name, rank and serial number twice. Rather, I
intended for Table2 to reference this information from Table1. This would
give the user a point of reference for entering data into the table or for
viewing the table. What I now realize is that this reference is better
achieved through the use of a query. That would force me, however, to
probably use a form for data entry, rather than just utilizing a table.
Still, I would need a way to link the information in Table2 to the correct
individuals in Table1, if I'm not referencing or repeating those three
primary fields.

I'm sure users here are dealing with tables with hundreds of fields, not
merely 59, so dividing the information into two separate may not seem
logical. I just thought it made sense, given that I'm dealing with two
fairly unrelated data groups, and it would create two simpler tables rather
than one very large one.

Any additional thoughts? Or should I just give up and make one table?

Thanks,
Kenny
 
K

Kenny Anderson

It sounds like you need to do some redesigning. 59 units is
something that would be done in a spreadsheet, not often in a database. It
is possible however.
Click to expand...

I was beginning to suspect this. Thanks for your input. I'll move my data
to a spreadsheet and utilize the limited database features, there.

Thanks,
Kenny
 
K

Kenny Anderson

Joseph Meehan said:
I am not sure it is best done in a spreadsheet, but in a spread sheet
you are often forced into using 59 columns to do what you would normally use
a database for. Since you don't appear to be real familiar with Access, it
may be easier for you to use Excel however. Access has a rather steep
learning curve. Those first few applications are tough.
Click to expand...

Thanks for your help. You're right. I haven't touched databases since
Access 95 when I built one for a project I had eight years ago. I did a
search on google for database vs. spreadsheet, but it wasn't too productive.
Most articles were pro-db and didn't really say when to use a spreadsheet
instead. It seems you are suggesting that a database is the best choice
when you have few fields and many records. Is that correct?

Thanks,
Kenny
 
Top