duplicate results

[Roberto]

Hello,

I have a problem I cannot fix. I have a parent table related to two other
child tables by a common ID field. When I run a query, it returns as many as
14 times the same record. I really do not know what is going on.

i.e. I want to query the first and last name of the parent table along with
the amount of funds leveraged by that indvidual which are in one of the child
tables. In a specific case, it should return two records. Instead it is
returning 28, 14 are of one record, and 14 of another.

Any suggestions?

 

[Golfinray]

There is generally only one thing that will cause that: The link is wrong.
Try clicking on the line that connects the tables together and there will be
a dialog box pop up. Try option 2 or 3. Also, make sure you have the
relationships set properly (Tools/relationships)

 

[Roberto]

Golfinray,

Thank you so much for your prompt response. I have one parent table and two
child tables. Both child tables are related to the parent table using a
one-to-many relationship. I tried changing the options of JOIN TYPE in both
relationships to options 2 and 3 and now the following error pops up:

"The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement"

This error did not happen when both one-to-many relationships were defined
in the JOIN TYPE with option one.

Please help!

 

[Bob Barrows [MVP]]

Hello,

I have a problem I cannot fix. I have a parent table related to two
other child tables by a common ID field. When I run a query, it
returns as many as 14 times the same record. I really do not know
what is going on.

i.e. I want to query the first and last name of the parent table
along with the amount of funds leveraged by that indvidual which are
in one of the child tables. In a specific case, it should return two
records. Instead it is returning 28, 14 are of one record, and 14 of
another.

Show us the sql statement (switch your query to SQL View using the View
menu, toolbar button, or right-click context menu)

 

[Roberto]

Golfinray,

I was able to eliminate the error message I mention in my previous post. It
disappeared when I only had one of the child tables in the query tables
views, instead of having both. Now that I changed the JOIN TYPE to the third
option and used only one child table, the results are no more duplicated.

I guess I thought I could have both child tables. Apparently I will have to
run a query for each child table that I need to extract data from.

Thanks man.