Duplicate results.

[Pjdelchi]

Does anyone know of any reason or scenario that would cause random duplicate
results to appear from the union of a left join query and a right join query?
I am getting thousands of correct records, but about 20 or 30 duplicates.
They are not different, they are exactly the same with two records containin
both the data from the left and right joins. Other similar records come out
correctly, one record with results from both the left and right joined
tables.

Thanks.

 

[Jerry Whittle]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.

Now if you just want to get rid of the dupes in the query, start out the SQL
statement with SELECT DISTINCT.

Also if you are saying this is a UNION query (and not a UNION ALL), then
there must be something different between the records even if it's something
as simple as a leading or trailing space in one of the fields.

 

[Pjdelchi]

Thanks Jerry. Let me give some backgroud to what I am tryign to do here. I
am trying to compare two tables. Each table contains the same basic
information, only it may be different in amounts, accts, groups, or cusips.
I am comparing the two tables because I need to see what differences there
are or what cusips, groups, etc have changed. I already taken the two
tables and summed them to get a grand total per cusip, per group, per acct.
I am now trying to combine the two summed tables into one table. Wherever
there are null fields, I need 0's. It is almost running perfectly, but as I
said before, there are 30 duplicates being returned. If it was a common
error, there would be hundreds of duplicates. Some are going away, while
some are not. Here is an example of a duplication, they are exactly the
same.

BDSKACCT GRP CUSP SumOfPARVALUE SumOfPRINCOST
SumOfBOOKVALUE SumOfPARVALUE1 SumOfPRINCOST1 SumOfBOOKVALUE1
704 91 31394PTR4 20000000 20075000 20067716.1 20000000
20075000 20067715.4
704 91 31394PTR4 20000000 20075000 20067716.1 20000000
20075000 20067715.4

Here is the sql:

SELECT InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP, Sum(Nz(BOLT.PARVALUE,
0)) AS
SumOfPARVALUE, Sum(Nz(BOLT.PRINCOST, 0)) AS SumOfPRINCOST,
Sum(Nz(BOLT.BOOKVALUE, 0)) AS
SumOfBOOKVALUE, Sum(Nz(InTrader.PARVALUE, 0)) AS SumOfPARVALUE1,
Sum(Nz(InTrader.PRINCOST, 0)) AS SumOfPRINCOST1, Sum(Nz(InTrader.BOOKVALUE,
0)) AS
SumOfBOOKVALUE1
FROM BOLT RIGHT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND
(BOLT.BDSKACCT = InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP
UNION SELECT BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP, Sum(Nz(BOLT.PARVALUE, 0)) AS
SumOfPARVALUE, Sum(Nz(BOLT.PRINCOST, 0)) AS SumOfPRINCOST,
Sum(Nz(BOLT.BOOKVALUE, 0)) AS
SumOfBOOKVALUE, Sum(Nz(InTrader.PARVALUE, 0)) AS SumOfPARVALUE1,
Sum(Nz(InTrader.PRINCOST, 0)) AS SumOfPRINCOST1, Sum(Nz(InTrader.BOOKVALUE,
0)) AS
SumOfBOOKVALUE1
FROM BOLT LEFT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND (BOLT.BDSKACCT
= InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
ORDER BY InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP;

 

[Jerry Whittle]

The UNION should get rid of any duplicates; therefore, Access doesn't believe
that there are any dupes. Also unless it's a formatting or cut/paste problem,
there is an extra space between the following in the second record. It really
helps to see it if you use a non-proportional font such as Courier.

20075000 20067715.4
20075000 20067715.4

If there is an extra space between these two fields, UNION or even DISTINCT
wouldn't see them as dupes.

You could try removing the following from the top and bottom and see what
happens.
Sum(Nz(InTrader.BOOKVALUE,0)) AS SumOfBOOKVALUE1