Create a query from 2 different tables

J

Jenn Civello

I am trying to create a query with data from 2 different tables. After I
select the fields from the 2 tables and run the query I get duplicate values
for each of the entries. How do I get rid of the duplicate values in the
query? I have tried creating a report and selecting "Hide Duplicates" but
that still doesn't work.

Thanks for your help.
 
R

Rick Brandt

Jenn said:
I am trying to create a query with data from 2 different tables.
After I select the fields from the 2 tables and run the query I get
duplicate values for each of the entries. How do I get rid of the
duplicate values in the query? I have tried creating a report and
selecting "Hide Duplicates" but that still doesn't work.

Thanks for your help.
Click to expand...

Did you create a join betwen the tables on a common field or fields? If not
you get a "Cartesian Result" which is every row in both tables duplicated
once for every row in the other table.
 
F

FSt1

hi,
usually this problem occurs when you don't have enought links between the
tables.
1 link may not be enough. look for additional common fields in both tables
and link them as well.
In some of the tables i work with, i sometimes require 3 and 4 links before
the duplicate data goes away.
you may still have some duplication on certain fields. this occures in a one
to many relationship where the one repeats for each many.

regards

FSt1
 
J

Jenn Civello

I have created a subdata sheet with the information I am looking for, can I
create a report that will show both the information from the 2 tables?
 
J

Jenn Civello

Hello Again,

I tried to create a query based on the table with the subdata sheet, and
when I view the query, it doesn't show the subdata information.
 
J

John Vinson

I tried to create a query based on the table with the subdata sheet, and
when I view the query, it doesn't show the subdata information.
Click to expand...

Subdatasheets are of VERY limited usefulness. You can't use a
subdatasheet "in a query" per se; instead, create a new Query, and add
both the main table and the related table to the query grid. Include
whichever fields you want to see from the two tables.

John W. Vinson[MVP]
 
E

expertware

If there are 1-N relationships duplicate values occur due to the
relationship.
If replication affects a report (for instance a function sensitive to
duplication as SUM applied on side 1)
you need to make union of subqueries.

This tool (free) does it automatically:
http://151.100.3.84/technicalpreview/
 
Top