Joining tables

[Guest]

I am looking for a way to join 2 tables together. Let me explain. I have 2 tables. One is called parcel_polygon and the other is called TAXDIGEST. I want to put the fields that are in TAXDIGEST into the parcel_polygon table based on the pin number relationship. The pin number represents each polygon's identification number. The 2 tables have a common field, which is a one-to-many relationship. The parcel_polygon has many records (for example, there are more than one record that has the same pin number) and the TAXDIGEST has a one record relatioinship (there is only one record with one pin number, no record has the same pin number where as the parcel_polygon table does.) Okay, I have a field where the two tables can relate to each other and I want to put all the fields that are in TAXDIGEST into the same table as parcel_polygon so that everything will be in one table. Is there a way to do this?

Please help,
Kresha

 

[Michel Walsh]

Hi,

Sure. Make a new query, bring the two tables, drag and drop the common
field from one of the table over the field of the other. Drag the desired
fields in the grid. Save the query. Use the query as if it was the "large"
table.


Hoping it may help,
Vanderghast, Access MVP

I am looking for a way to join 2 tables together. Let me explain. I have

2 tables. One is called parcel_polygon and the other is called TAXDIGEST.
I want to put the fields that are in TAXDIGEST into the parcel_polygon table
based on the pin number relationship. The pin number represents each
polygon's identification number. The 2 tables have a common field, which is
a one-to-many relationship. The parcel_polygon has many records (for
example, there are more than one record that has the same pin number) and
the TAXDIGEST has a one record relatioinship (there is only one record with
one pin number, no record has the same pin number where as the
parcel_polygon table does.) Okay, I have a field where the two tables can
relate to each other and I want to put all the fields that are in TAXDIGEST
into the same table as parcel_polygon so that everything will be in one
table. Is there a way to do this?

 

[Guest]

Thanks for the reply! I followed what you said and it worked, but I have another question. When I created a new query and got the new table open, there were less records than either one of my other 2 existing tables had. I had 20,640 records in the parcel_polygon table and 25,119 records in the TAXDIGEST table. The new query table had 20,403 records. It looks like the query made a new table of only those that matched. Is there a way for the query to keep all records that is in the parcel_polygon table including those that match and those that do not match?.......and those that do not match from the TAXDIGEST table dissapear

So thankful for the help

Kresha

 

[Michel Walsh]

Hi,


Sure, right click on the line making the join (in the upper half) and
use option 2 or option 3, as it fit.



Hoping it may help,
Vanderghast, Access MVP

Thanks for the reply! I followed what you said and it worked, but I have

another question. When I created a new query and got the new table open,
there were less records than either one of my other 2 existing tables had.
I had 20,640 records in the parcel_polygon table and 25,119 records in the
TAXDIGEST table. The new query table had 20,403 records. It looks like the
query made a new table of only those that matched. Is there a way for the
query to keep all records that is in the parcel_polygon table including
those that match and those that do not match?.......and those that do not
match from the TAXDIGEST table dissapear?

 

[Guest]

Thanks for the help! I didn't realize it was so simple to fix what records you wanted to keep. I know that I sound like a novice user, but I have one more question. In Access, my parcel_polygon table has 20,640 records and the TAXDIGEST table has 25,119 records,….. when I put them together and keep all of parcel_polygon records, shouldn’t I come up with 20,640 records???

Well, when I joined them, I got 20,667 records. Where could the other 27 records come from
I did a query where access finds out how many duplictes there are and it found 54 (27 times 2). For some reason Access is duplicating 27 records. Is there a reason for this? I don't need access adding in more records. What do I need to do to fix the problem

Inquiring minds want to know

K

 

[Michel Walsh]

Hi,


Indeed, a JOIN acts like an amplifier.

Table1 ' table name
f1 f2 ' fields
1 a
2 b
3 c ' data


Table2
g1 g2
1 one
1 un
1 uno
2 dve


SELECT TAble1.f1, Table1.f2, table2.g1, table2.g2
FROM table1 INNER JOIN table2
ON table1.f1=table2.g1

would return

f1 f2 g1 g2
1 a 1 one
1 a 1 un
1 a 1 uno
2 b 2 dve


we can "conclude" (it just a matter of how we look at the result, not really
a definition) that table1 first record had been amplified 3 times, since it
now appears three times in the result; its second record, amplified once, if
we can speak of amplification in such a case; and its third record, by 0
times ( eliminated ). Sure these "amplification" numbers, 3, 1, and 0, are
just the number of duplication of the MATCHING value ( table2.g1 ) as
specified by the ON clause.

So, as you found, 27 records were duplicated, so the amplification number
for those records was 2, and you get 27 additional extra rows in the
results.

How to solve the problem? First, are those duplicated values make sense? if
not, remove them from the table (and add a UNIQUE index on the field so that
this error won't occur anymore, since with the index, it will not allow the
presence of duplicated values).

If the duplicated data makes sense (for other reasons), you may try a
DISTINCT or a GROUP BY query. A GROUP BY query may selectively generated
groups (no duplicated values among the group) and take "some" representative
values out of the other fields not making the groups:


SELECT f1, MAX(f2), MAX(g2)
FROM table1 LEFT JOIN table2 ON table1.f1=Table2.g1
GROUP BY f1


would so produce

f1 f2 g2
1 a uno
2 b dve
3 c <null>



Hoping it may help,
Vanderghast, Access MVP

Thanks for the help! I didn't realize it was so simple to fix what

records you wanted to keep. I know that I sound like a novice user, but I
have one more question. In Access, my parcel_polygon table has 20,640
records and the TAXDIGEST table has 25,119 records,... when I put them
together and keep all of parcel_polygon records, shouldn't I come up with
20,640 records???

Well, when I joined them, I got 20,667 records. Where could the other 27 records come from?
I did a query where access finds out how many duplictes there are and it

found 54 (27 times 2). For some reason Access is duplicating 27 records.
Is there a reason for this? I don't need access adding in more records.
What do I need to do to fix the problem?