help! problem with 2 tables

[amy]

I have 2 tables - "Table1" and "Table2".
Table1 and Table 2 have different fields.
Table 1 is a record of personal details with a primary key
field "LanID". There are no duplicates.
Table 2 will contain different fields than table1 but will
contain the common field "LanID". In table2 LanId can be
duplicated.
I want to match the LanId in Table2 to the LanId in
Table1. Then I want to paste into a new table all data
from Table2 with 3 new fields that will be filled in from
Table1 based on the match of LanID.

Eg. If the LanId field in Table2 matches one in Table1
then I want the details from 3 other fields in that record
in Table1 to paste with the details from the record in
Table2 into a brand new table as one new record.

 

[Graham Mandeno]

Hi Amy

You don't need to "paste new records into a table" to do this. In fact, you
*should not* do that.

What you have is a one-to-many relationship, which is a good basis for
database design. Each record in Table2 is related to a record in Table1 via
the LanID field. It seems you want to display all the fields in Table2
along with three fields in the related record from Table1.

To do this, you use a query, which behaves in almost every way like the
table you describe, but refers to the original data, not a copy of it.
Start by creating a new query in "design view" and add both tables (Table1
and Table2), then close the Show Table window. You should see your two
tables with a line between them connecting the two LanID fields. If there
is no line, then drag across from one LanID to the other one. Now,
double-click, one-by-one, on the three fields in Table1, and then
double-click on the "*" (which means "all fields") in Table2. Click on the
view button to see the results.

You can then save the query and use it as the basis for a form or report or
whatever.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[amy]

thank you very much i shall give it a go.
xxx

-----Original Message-----
Hi Amy

You don't need to "paste new records into a table" to do this. In fact, you
*should not* do that.

What you have is a one-to-many relationship, which is a good basis for
database design. Each record in Table2 is related to a record in Table1 via
the LanID field. It seems you want to display all the fields in Table2
along with three fields in the related record from Table1.

To do this, you use a query, which behaves in almost every way like the
table you describe, but refers to the original data, not a copy of it.
Start by creating a new query in "design view" and add both tables (Table1
and Table2), then close the Show Table window. You should see your two
tables with a line between them connecting the two LanID fields. If there
is no line, then drag across from one LanID to the other one. Now,
double-click, one-by-one, on the three fields in Table1, and then
double-click on the "*" (which means "all fields") in Table2. Click on the
view button to see the results.

You can then save the query and use it as the basis for a form or report or
whatever.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

I have 2 tables - "Table1" and "Table2".
Table1 and Table 2 have different fields.
Table 1 is a record of personal details with a primary key
field "LanID". There are no duplicates.
Table 2 will contain different fields than table1 but will
contain the common field "LanID". In table2 LanId can be
duplicated.
I want to match the LanId in Table2 to the LanId in
Table1. Then I want to paste into a new table all data
from Table2 with 3 new fields that will be filled in from
Table1 based on the match of LanID.

Eg. If the LanId field in Table2 matches one in Table1
then I want the details from 3 other fields in that record
in Table1 to paste with the details from the record in
Table2 into a brand new table as one new record.

.

 

[amy]

hi graham

i did this exactly as you said and got the table result
great as far as fields go but it doesnt show the data from
the 3 fields in table1 still ??
have you any idea why this is ?
thanks very much for your help

amy

-----Original Message-----
Hi Amy

You don't need to "paste new records into a table" to do this. In fact, you
*should not* do that.

What you have is a one-to-many relationship, which is a good basis for
database design. Each record in Table2 is related to a record in Table1 via
the LanID field. It seems you want to display all the fields in Table2
along with three fields in the related record from Table1.

To do this, you use a query, which behaves in almost every way like the
table you describe, but refers to the original data, not a copy of it.
Start by creating a new query in "design view" and add both tables (Table1
and Table2), then close the Show Table window. You should see your two
tables with a line between them connecting the two LanID fields. If there
is no line, then drag across from one LanID to the other one. Now,
double-click, one-by-one, on the three fields in Table1, and then
double-click on the "*" (which means "all fields") in Table2. Click on the
view button to see the results.

You can then save the query and use it as the basis for a form or report or
whatever.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

I have 2 tables - "Table1" and "Table2".
Table1 and Table 2 have different fields.
Table 1 is a record of personal details with a primary key
field "LanID". There are no duplicates.
Table 2 will contain different fields than table1 but will
contain the common field "LanID". In table2 LanId can be
duplicated.
I want to match the LanId in Table2 to the LanId in
Table1. Then I want to paste into a new table all data
from Table2 with 3 new fields that will be filled in from
Table1 based on the match of LanID.

Eg. If the LanId field in Table2 matches one in Table1
then I want the details from 3 other fields in that record
in Table1 to paste with the details from the record in
Table2 into a brand new table as one new record.

.

 

[Graham Mandeno]

Hi Amy

When your query is in design view (when you can see the two tables with the
joining line), do the three fields from Table1 appear at the top of three
grid columns below? There should be four "used" columns - one for each of
the three fields, and one for Table2.*. You can double-click or click and
drag the field names to the required cells.

If it still doesn't work, switch to SQL view (click the dropdown arrow in
the view button and choose "SQL", then copy the contents of the window and
paste it back here exactly as is in a reply message.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

hi graham

i did this exactly as you said and got the table result
great as far as fields go but it doesnt show the data from
the 3 fields in table1 still ??
have you any idea why this is ?
thanks very much for your help

amy

-----Original Message-----
Hi Amy

You don't need to "paste new records into a table" to do this. In fact, you
*should not* do that.

What you have is a one-to-many relationship, which is a good basis for
database design. Each record in Table2 is related to a record in Table1 via
the LanID field. It seems you want to display all the fields in Table2
along with three fields in the related record from Table1.

To do this, you use a query, which behaves in almost every way like the
table you describe, but refers to the original data, not a copy of it.
Start by creating a new query in "design view" and add both tables (Table1
and Table2), then close the Show Table window. You should see your two
tables with a line between them connecting the two LanID fields. If there
is no line, then drag across from one LanID to the other one. Now,
double-click, one-by-one, on the three fields in Table1, and then
double-click on the "*" (which means "all fields") in Table2. Click on the
view button to see the results.

You can then save the query and use it as the basis for a form or report or
whatever.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

I have 2 tables - "Table1" and "Table2".
Table1 and Table 2 have different fields.
Table 1 is a record of personal details with a primary key
field "LanID". There are no duplicates.
Table 2 will contain different fields than table1 but will
contain the common field "LanID". In table2 LanId can be
duplicated.
I want to match the LanId in Table2 to the LanId in
Table1. Then I want to paste into a new table all data
from Table2 with 3 new fields that will be filled in from
Table1 based on the match of LanID.

Eg. If the LanId field in Table2 matches one in Table1
then I want the details from 3 other fields in that record
in Table1 to paste with the details from the record in
Table2 into a brand new table as one new record.

.

 

[John Vinson]

i did this exactly as you said and got the table result
great as far as fields go but it doesnt show the data from
the 3 fields in table1 still ??

Amy, reread his suggestion. You are misunderstanding how relational
databases WORK.

A database uses the "Grandmother's Cupboard Principle": "a place - ONE
place! - for everything, everything in its place". If a field exists
in Table2 then it *SHOULD NOT* exist in Table1. Each table should have
its own relevant fields, and one field (occasionally more than one,
but you don't need to worry about that possibility at this stage) to
link the two tables.

If you want to see fields from Table1 and from Table2 together, don't
think about putting those fields into one of the tables. Instead,
create a Query joining Table1 and Table2 and look at the data *in that
Query*. This is how relational databases work!

 

[Guest]

Hi Amy,
Try using a Make Table Query. For the tables being used
add Table1 and Table2. Then join field LanID from Table2
to LanID in Table1 (click on LanID in Table2 and drag it
on top of LanID in Table1). A line should now show linking
the 2 tables together. If you want to include all rows
from Table2 whether or not there is a match in Table1, you
will need to right click on the Joining line and modify
the join criteria to say include all records from Table2.
Otherwise leave it as is and it will write rows to the new
table where LanID matches on both tables.

Now you need to select all columns from Table2 (double
click the asterisk at the top of the list in Table2). They
should now list in the grid below. (if it has only 1 entry
with Table2.* that is OK - it will include all columns).
Now for the fields you want from Table1, double click each
fieldname in that table and it will be placed in the grid
below. Run the query and you should have exactly what you
need in the new table.