Linking Data From 2 Different Tables

[Guest]

I am currently running Access 2000.

I have started a database with 2 tables. I would like to link data from
table 1 and fill in fields in table 2.

Table 1
Field A - Id
Field B - Name
Field C
Field D

Table 2
Field A -Id
Field B - Name
Field E
Field F

When I create a record in Table 2 I would like to put in the ID and have the
Name automatically filled in. I have created a relationship between the two
tables, but when I create a new record and fill in the ID the Name field does
not automatically fill in.

I am new to creating databases. Any guidance that can be offered is
appreciated.

Thanks,
Paul

 

[Guest]

What you are asking for isn't a generally accepted methodology in the DB
world i.e. one table "filling in" another table...

In Access one can set a Field's property to be a LookUp....and when you do
so the LookUp Wizard will launch....just follow it......and then one field in
a table can be a lookup of a field of another table. One common place for
something like this is a Customer Record table where to add a new customer
address one uses a LookUp feature for the state initials (i.e. AZ AL DE
etc etc) and the table of state initials is a separate static table....

But to merge data fields from two different tables, in general, one would
use a query. The query result can contain fields from multiple tables. Then
you can build Forms or Reports off the query.

hope this overview helps a little....

 

[Guest]

Paul:

You don't need to put the name in table 2. Moreover there are very good
reasons why you should not do so. It would be what's known in the jargon as
'redundancy' which is to be avoided as it leaves the door open to 'update
anomalies', i.e. the data could be inconsistent, for instance you could have
the same ID value in rows in each table with different names.

You simply need the ID value (Field A) in table 2. You can always pull in
the name from table 1 when need by joining the two tables in a query on the
Field A fields. When inputting data into table 2 via a form you'd normally
use a combo box bound to the Field A field. The RowSource of this combo box
would look up the names in table 1 like so:

SELECT [Field A], [Field B]
FROM [Table 1]
ORDER BY [Field B];

By setting the BoundColumn property of the combo box to 1, its ColumnCount
property to 2 and its ColumnWidths property to 0cm;8cm (or rough equivalent
in inches, but the first dimension must be zero) you see only the names in
the combo box's drop down list, so its simply a case of choosing a name. The
underlying value is the ID, however, but this is hidden by virtue of the
first column being zero width.

Ken Sheridan
Stafford, England

 

[Guest]

I appreciate your guidance!

Paul

What you are asking for isn't a generally accepted methodology in the DB
world i.e. one table "filling in" another table...

In Access one can set a Field's property to be a LookUp....and when you do
so the LookUp Wizard will launch....just follow it......and then one field in
a table can be a lookup of a field of another table. One common place for
something like this is a Customer Record table where to add a new customer
address one uses a LookUp feature for the state initials (i.e. AZ AL DE
etc etc) and the table of state initials is a separate static table....

But to merge data fields from two different tables, in general, one would
use a query. The query result can contain fields from multiple tables. Then
you can build Forms or Reports off the query.

hope this overview helps a little....

 

[Guest]

Thank you! I appreciate your guidance!

Paul

Paul:

You don't need to put the name in table 2. Moreover there are very good
reasons why you should not do so. It would be what's known in the jargon as
'redundancy' which is to be avoided as it leaves the door open to 'update
anomalies', i.e. the data could be inconsistent, for instance you could have
the same ID value in rows in each table with different names.

You simply need the ID value (Field A) in table 2. You can always pull in
the name from table 1 when need by joining the two tables in a query on the
Field A fields. When inputting data into table 2 via a form you'd normally
use a combo box bound to the Field A field. The RowSource of this combo box
would look up the names in table 1 like so:

SELECT [Field A], [Field B]
FROM [Table 1]
ORDER BY [Field B];

By setting the BoundColumn property of the combo box to 1, its ColumnCount
property to 2 and its ColumnWidths property to 0cm;8cm (or rough equivalent
in inches, but the first dimension must be zero) you see only the names in
the combo box's drop down list, so its simply a case of choosing a name. The
underlying value is the ID, however, but this is hidden by virtue of the
first column being zero width.

Ken Sheridan
Stafford, England

I am currently running Access 2000.

I have started a database with 2 tables. I would like to link data from
table 1 and fill in fields in table 2.

Table 1
Field A - Id
Field B - Name
Field C
Field D

Table 2
Field A -Id
Field B - Name
Field E
Field F

When I create a record in Table 2 I would like to put in the ID and have the
Name automatically filled in. I have created a relationship between the two
tables, but when I create a new record and fill in the ID the Name field does
not automatically fill in.

I am new to creating databases. Any guidance that can be offered is
appreciated.

Thanks,
Paul