Query Resulting in Duplicate Records

[Singinbeauty]

Hello All,
Ok, below is the SQL statement for a query I want to run to find the list of
open disputes and combine the current list of disputes with the old list that
has the updated comments and such. When I run this, the resulting records are
duplicating. For example:

TMX WO TMX LAST UPDATED TMX Comments
G9054
G9054 5/14/2008 this was a pick up order;
they used to
have a truck they
paid to come and get
orders once a week

As you can see, the first line is blank (because that is how it is on one
table even though I have put it into the design to show the other table for
the 'TMX Last Updated' and 'TMX Comments') and the other is populated with
info because that is how it is on the table that I want it to show. Please
see below and help me see what I am missing! Thank you so much!!!

SELECT [CurrentDispute - Export].[TMX WO], [CurrentDispute - Export].[TMX
CSTMR], [CurrentDispute - Export].[SOLD TO], [CurrentDispute - Export].[DATE
DSPTD], [Current Focal Dispute File].[Metal Type], [CurrentDispute -
Export].[Dispute Code], [CurrentDispute - Export].[Amount of Dispute],
[CurrentDispute - Export].COMMENT, [CurrentDispute - Export].[DISPUTED BY],
[CurrentDispute - Export].[PHONE/EMAIL], [CurrentDispute - Export].[CREDIT#],
[Current Focal Dispute File].[TMX STATUS], [Current Focal Dispute File].[TMX
CLOSED DATE], [Current Focal Dispute File].[TMX LAST UPDATED], [Current Focal
Dispute File].[TMX Comments], [Current Focal Dispute File].RESP INTO [EXPORT
- CurrentFocalDisputeFile]
FROM [CurrentDispute - Export] LEFT JOIN [Current Focal Dispute File] ON
[CurrentDispute - Export].[TMX WO] = [Current Focal Dispute File].[TMX WO]
WHERE ((([Current Focal Dispute File].[TMX STATUS])<>"closed"));

 

[NetworkTrade]

have you defined the query for 'Unique Values' ...?

in View, Properties in query's design view

 

[Singinbeauty]

Yes, I did try that and it didn't work...

have you defined the query for 'Unique Values' ...?

in View, Properties in query's design view
--
NTC

Hello All,
Ok, below is the SQL statement for a query I want to run to find the list of
open disputes and combine the current list of disputes with the old list that
has the updated comments and such. When I run this, the resulting records are
duplicating. For example:

TMX WO TMX LAST UPDATED TMX Comments
G9054
G9054 5/14/2008 this was a pick up order;
they used to
have a truck they
paid to come and get
orders once a week

As you can see, the first line is blank (because that is how it is on one
table even though I have put it into the design to show the other table for
the 'TMX Last Updated' and 'TMX Comments') and the other is populated with
info because that is how it is on the table that I want it to show. Please
see below and help me see what I am missing! Thank you so much!!!

SELECT [CurrentDispute - Export].[TMX WO], [CurrentDispute - Export].[TMX
CSTMR], [CurrentDispute - Export].[SOLD TO], [CurrentDispute - Export].[DATE
DSPTD], [Current Focal Dispute File].[Metal Type], [CurrentDispute -
Export].[Dispute Code], [CurrentDispute - Export].[Amount of Dispute],
[CurrentDispute - Export].COMMENT, [CurrentDispute - Export].[DISPUTED BY],
[CurrentDispute - Export].[PHONE/EMAIL], [CurrentDispute - Export].[CREDIT#],
[Current Focal Dispute File].[TMX STATUS], [Current Focal Dispute File].[TMX
CLOSED DATE], [Current Focal Dispute File].[TMX LAST UPDATED], [Current Focal
Dispute File].[TMX Comments], [Current Focal Dispute File].RESP INTO [EXPORT
- CurrentFocalDisputeFile]
FROM [CurrentDispute - Export] LEFT JOIN [Current Focal Dispute File] ON
[CurrentDispute - Export].[TMX WO] = [Current Focal Dispute File].[TMX WO]
WHERE ((([Current Focal Dispute File].[TMX STATUS])<>"closed"));

 

[NetworkTrade]

that would only not work if there is indeed not totally duplicate
records....perhaps the key field?

if you can strip down the query to eliminate the field with the variation
then the Unique Value feature should work.... putting it in SQL view you
will see that a DISTINCT clause was added....

--
NTC

Yes, I did try that and it didn't work...

have you defined the query for 'Unique Values' ...?

in View, Properties in query's design view
--
NTC

Hello All,
Ok, below is the SQL statement for a query I want to run to find the list of
open disputes and combine the current list of disputes with the old list that
has the updated comments and such. When I run this, the resulting records are
duplicating. For example:

TMX WO TMX LAST UPDATED TMX Comments
G9054
G9054 5/14/2008 this was a pick up order;
they used to
have a truck they
paid to come and get
orders once a week

As you can see, the first line is blank (because that is how it is on one
table even though I have put it into the design to show the other table for
the 'TMX Last Updated' and 'TMX Comments') and the other is populated with
info because that is how it is on the table that I want it to show. Please
see below and help me see what I am missing! Thank you so much!!!

SELECT [CurrentDispute - Export].[TMX WO], [CurrentDispute - Export].[TMX
CSTMR], [CurrentDispute - Export].[SOLD TO], [CurrentDispute - Export].[DATE
DSPTD], [Current Focal Dispute File].[Metal Type], [CurrentDispute -
Export].[Dispute Code], [CurrentDispute - Export].[Amount of Dispute],
[CurrentDispute - Export].COMMENT, [CurrentDispute - Export].[DISPUTED BY],
[CurrentDispute - Export].[PHONE/EMAIL], [CurrentDispute - Export].[CREDIT#],
[Current Focal Dispute File].[TMX STATUS], [Current Focal Dispute File].[TMX
CLOSED DATE], [Current Focal Dispute File].[TMX LAST UPDATED], [Current Focal
Dispute File].[TMX Comments], [Current Focal Dispute File].RESP INTO [EXPORT
- CurrentFocalDisputeFile]
FROM [CurrentDispute - Export] LEFT JOIN [Current Focal Dispute File] ON
[CurrentDispute - Export].[TMX WO] = [Current Focal Dispute File].[TMX WO]
WHERE ((([Current Focal Dispute File].[TMX STATUS])<>"closed"));