Link Fields in Table

  • Thread starter Thread starter Kenny Anderson
  • Start date Start date
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
(e-mail address removed)
(remove e'x'es to reply)
 
Kenny said:
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...

First you set up a relationship between tables in the same database.

However I suggest you may want to combine the two tables so you don't
repeat Name and Rank in two tables. Just have all four Dummy fields in the
same table. You can set up the relationship add the new blank fields in one
table and use an update query to add the other tables data to it.
 
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]
 
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
 
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
 
Kenny said:
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.
Click to expand...

Not knowing your database I have no way of really knowing, but when I
see any table with 22 fields I suspect poor database design, if I see it
related to another table with 34 fields it seems almost certain.
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.
Click to expand...

It is almost always far better and fewer problems to do just that, or
use the query. Tables are best left alone.
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?
Click to expand...

Yea, combine the tables and tell us more about why you need all those
fields. 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.
 
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
 
Kenny said:
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
Click to expand...

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.
 
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
 
Kenny said:
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?
Click to expand...

Yes. In fact Excel will only hold something like 65,536 rows (records)
For Access that is small stuff. The big files have millions of records.
 
Back
Top