Joining two tables to create a make-table

  • Thread starter Thread starter Jenn
  • Start date Start date
J

Jenn

In the query we are working on; there are two tables and we want to keep the
fields from table A and then add the info from table B that matches to those
lines to the same line as table A, also while keeping the info that does not
have anything else from table B.

Example - Table A has 402 lines. Table B has 167 lines. When we do the
make-table it is narrowing the table down to only items that match and
bringing in the 167 lines. We have tried to change the join properties
however no matter which option we choose it brings in the 167 lines instead
of the 402 lines, with 167 lines having matching information. We want to keep
the 402(table A) lines just add the information from the 167(table B) lines
to them.

Essentially of the 402 lines 167 of them will have information added to
their row.

Please advise as to whether or not there is a way to complete this.


Thank you
 
Jenn

First of all, the phrase "make table" implies creation of a new table. If
you can create a query that returns the information from your two existing
tables, you probably don't need to create a third table that holds duplicate
data (and it's usually a bad idea to duplicate data).

Create a query in design view, add TableA, add TableB, and join them on
their common field(s).

Now highlight the join line, right click and set the type of join to be "All
from TableA and any that match from TableB".

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Thanks for your suggestion, however my boss and I have already tried joining
the properties. There are three different options to choose from when
creating a join between the fields, and we have tried all three. It is not
bringing the information in how we would like for it to.

Simply stated we are Essentially wanting to take of the 402 (table A) and
167 of the lines will have information added to their row from Table B.

Any other suggestions?

Please advise.
 
Jeff,

Thanks for your suggestion, however my boss and I have already tried joining
the properties. There are three different options to choose from when
creating a join between the fields, and we have tried all three. It is not
bringing the information in how we would like for it to.

Simply stated we are Essentially wanting to take of the 402 (table A) and
167 of the lines will have information added to their row from Table B.
Click to expand...

Do TableA and TableB have the same fields, and do you want to actually
*update* (permanently change) the contents of TableA by updating the existing
fields in TableA to the matching fields in TableB? If so you'll need an Update
query.
 
Jenn

I can't tell from your description if you want to "add" the 402 rows and the
167 rows, to have 569 rows, or if you want 402 rows with the related data in
167 of them.

If the former, look into creating a UNION query.

If the latter, please post the SQL statements you are using, as a LEFT (or
RIGHT) join should do it...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
We are in the processes of creating a union query. Something we were trying
not to do, but it looks like there is no way around it.

Thank you for your assistance with this questions as well. It is
appreaciated.
 
When joining Table A and Table B, through a union query, should each table
have the same exact number of fields (columns) and the exact field names?

Please advise.
 
Jenn

Check Access HELP for more specific information on UNION queries.

The two "sides" of the UNION need to have the same number of fields, but
they don't need the same names.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
When joining Table A and Table B, through a union query, should each table
have the same exact number of fields (columns) and the exact field names?
Click to expand...

The number of fields must match, the datatypes of the paired fields must match
- but the fieldnames can be anything.
 
Back
Top