Duplication of records in query?

[Tony Williams]

I have a query which is two queries joined.

The sql for the first query, RecoveriesA, is
SELECT Vehicle.AVCISCode, Vehicle.VRM, Vehicle.[Bottom Book Value],
Vehicle.[Recovery Date], Vehicle.[Vehicle Recovered], [Make] & " " &
[Model] AS Vehicle
FROM Vehicle
WHERE (((Vehicle.[Vehicle Recovered])=-1));

The sql for the second query, RecoveriesB, is
SELECT Agreement.AVCISCode, Agreement.[Ref Nbr], Agreement.[Finance
Company], Agreement.[Total Payments]
FROM Agreement;

The sql for the joined query is
SELECT RecoveriesB.[Ref Nbr], RecoveriesB.AVCISCode, RecoveriesB.[Finance
Company], RecoveriesA.[Bottom Book Value], RecoveriesA.VRM,
RecoveriesA.[Recovery Date], RecoveriesB.[Total Payments]
FROM RecoveriesA LEFT JOIN RecoveriesB ON RecoveriesA.AVCISCode =
RecoveriesB.AVCISCode;

If I run the two first queries on their own they give me the answer I
expect, but when I join them and run the third joined query I get a
duplication of reords. The duplication happens where I have two records in
Recoveries A with the same AVCISCode.

Can anyone suggest how to avoid this?
Thanks
Tony

 

[Marshall Barton]

I have a query which is two queries joined.

The sql for the first query, RecoveriesA, is
SELECT Vehicle.AVCISCode, Vehicle.VRM, Vehicle.[Bottom Book Value],
Vehicle.[Recovery Date], Vehicle.[Vehicle Recovered], [Make] & " " &
[Model] AS Vehicle
FROM Vehicle
WHERE (((Vehicle.[Vehicle Recovered])=-1));

The sql for the second query, RecoveriesB, is
SELECT Agreement.AVCISCode, Agreement.[Ref Nbr], Agreement.[Finance
Company], Agreement.[Total Payments]
FROM Agreement;

The sql for the joined query is
SELECT RecoveriesB.[Ref Nbr], RecoveriesB.AVCISCode, RecoveriesB.[Finance
Company], RecoveriesA.[Bottom Book Value], RecoveriesA.VRM,
RecoveriesA.[Recovery Date], RecoveriesB.[Total Payments]
FROM RecoveriesA LEFT JOIN RecoveriesB ON RecoveriesA.AVCISCode =
RecoveriesB.AVCISCode;

If I run the two first queries on their own they give me the answer I
expect, but when I join them and run the third joined query I get a
duplication of reords. The duplication happens where I have two records in
Recoveries A with the same AVCISCode.

Table A should not have any duplicate records. Therefore
the joined result should have no duplicates. If you are
only selecting some fields that are not sufficient to
determine uniqueness in the result, then your first two
queries should use the DISTINCT predicate (Unique Records
property). If that doesn't solve the problem, then use
DISTINCT in the third query instead.

 

[Tony Williams]

Thanks Marshall I'll have a go at that.
Thanks again for the pointer.
Tony

I have a query which is two queries joined.

The sql for the first query, RecoveriesA, is
SELECT Vehicle.AVCISCode, Vehicle.VRM, Vehicle.[Bottom Book Value],
Vehicle.[Recovery Date], Vehicle.[Vehicle Recovered], [Make] & " " &
[Model] AS Vehicle
FROM Vehicle
WHERE (((Vehicle.[Vehicle Recovered])=-1));

The sql for the second query, RecoveriesB, is
SELECT Agreement.AVCISCode, Agreement.[Ref Nbr], Agreement.[Finance
Company], Agreement.[Total Payments]
FROM Agreement;

The sql for the joined query is
SELECT RecoveriesB.[Ref Nbr], RecoveriesB.AVCISCode, RecoveriesB.[Finance
Company], RecoveriesA.[Bottom Book Value], RecoveriesA.VRM,
RecoveriesA.[Recovery Date], RecoveriesB.[Total Payments]
FROM RecoveriesA LEFT JOIN RecoveriesB ON RecoveriesA.AVCISCode =
RecoveriesB.AVCISCode;

If I run the two first queries on their own they give me the answer I
expect, but when I join them and run the third joined query I get a
duplication of reords. The duplication happens where I have two records in
Recoveries A with the same AVCISCode.

Table A should not have any duplicate records. Therefore
the joined result should have no duplicates. If you are
only selecting some fields that are not sufficient to
determine uniqueness in the result, then your first two
queries should use the DISTINCT predicate (Unique Records
property). If that doesn't solve the problem, then use
DISTINCT in the third query instead.