I'm still learning -Query to join 2 tables

[Guest]

Have 2 tables I want to join to make 1 table in order to append records so as to create a single table. Table one has 30 fields and table two has 10 fields.

I want the 10 fields from the 2nd table to be inserted into table 1 starting from fields 10-20 followed by the balance of the fields from table 1. Both tables have the same unique identifer (primary key)

I did the standard Query which did return the information but not in order that I want. Plus, the field and file names were displayed as one name - way to confusing to review.

Would appreciate all suggestions on how to combine tables in desired field sequence with correct names of fields

 

[John Vinson]

Have 2 tables I want to join to make 1 table in order to append records so as to create a single table. Table one has 30 fields and table two has 10 fields.

I want the 10 fields from the 2nd table to be inserted into table 1 starting from fields 10-20 followed by the balance of the fields from table 1. Both tables have the same unique identifer (primary key).

I did the standard Query which did return the information but not in order that I want. Plus, the field and file names were displayed as one name - way to confusing to review.

Would appreciate all suggestions on how to combine tables in desired field sequence with correct names of fields.

Let's see if I understand correctly. Let's say you have 100 records in
Table1, and 100 records in Table2. You want to create a NEW table with
100 records; each record in this Table3 should contain fields 1-9 and
21-30 from Table1, but fields 10-20 from Table2; whatever was in
fields 10-20 in Table1 is to be discarded. Is that correct?

If so, I'd suggest just making a copy of Table1, calling it Table3,
and then running an Update query. Create a Query joining Table2 to the
new Table3 by the Primary Key; change it to an Update query; and on
the Update To line under Table3.Field10 put

[Table2].[Field10]

and the same for fields 11 through 20.

Run the query by clicking the ! icon and you will overwrite whatever
was in those fields in Table1 with the values from the corresponding
record in Table2.

Note that when you create a Select Query you are perfectly free to put
the fields into the grid in any order that you like; just don't choose
the * "all fields" option. You can select Field1 through Field10, then
the ten fields from Table2, then fields Field21 through Field30 - just
doubleclick the fieldnames in the table icon one by one, or select a
block of fields and drag them into the grid. Note also that query (or
table) datasheets are NOT designed for production display and
management of data; they're really for debugging only. If you create a
Form based on the query you can put the fields in any order you like,
with any captions you wish.

 

[Guest]

Thank you for your suggestions which I will use.

-----Original Message-----

append records so as to create a single table. Table one
has 30 fields and table two has 10 fields.into table 1 starting from fields 10-20 followed by the
balance of the fields from table 1. Both tables have the
same unique identifer (primary key).information but not in order that I want. Plus, the field
and file names were displayed as one name - way to
confusing to review.tables in desired field sequence with correct names of
fields.

Let's see if I understand correctly. Let's say you have 100 records in
Table1, and 100 records in Table2. You want to create a NEW table with
100 records; each record in this Table3 should contain fields 1-9 and
21-30 from Table1, but fields 10-20 from Table2; whatever was in
fields 10-20 in Table1 is to be discarded. Is that correct?

If so, I'd suggest just making a copy of Table1, calling it Table3,
and then running an Update query. Create a Query joining Table2 to the
new Table3 by the Primary Key; change it to an Update query; and on
the Update To line under Table3.Field10 put

[Table2].[Field10]

and the same for fields 11 through 20.

Run the query by clicking the ! icon and you will overwrite whatever
was in those fields in Table1 with the values from the corresponding
record in Table2.

Note that when you create a Select Query you are perfectly free to put
the fields into the grid in any order that you like; just don't choose
the * "all fields" option. You can select Field1 through Field10, then
the ten fields from Table2, then fields Field21 through Field30 - just
doubleclick the fieldnames in the table icon one by one, or select a
block of fields and drag them into the grid. Note also that query (or
table) datasheets are NOT designed for production display and
management of data; they're really for debugging only. If you create a
Form based on the query you can put the fields in any order you like,
with any captions you wish.


.