How to prevent duplicates second go around

[Hell-fire]

Hi,

I asked this before, but unfortunately the suggestions didn't work or wasn't
applicable.

I have a query that pulls data from two tables. The EMVCoID is a primary
key in one table, but not in the other. I have the sort on ReportID and
EMVCoID forthis query.

I would like to prevent any duplicate records from the EMVCoID. I use MS
Word mail merge to pull the data from this query and hope to have no
duplicates show up.

In SQL view, my query looks like this:

SELECT [T IWG Summary].[ReportOrder], [T IWG Summary].[EMVCoID], [T IWG
Summary].[ProblemStatus], [T IWG Summary].[Dateopened], [T IWG
Summary].[Dateclosed], [T Problem Report].[Description/Details], [T IWG
Summary].[CombineDescription/Detail], [T IWG Summary].[CardInformation], [T
IWG Summary].[TerminalInformation], [T IWG Summary].[CorrectiveAction], [T
IWG Summary].[PreventativeAction], [T Problem Report].[IWGResponse], [T IWG
Summary].[Classification]
FROM [T IWG Summary] INNER JOIN [T Problem Report] ON [T IWG
Summary].[EMVCoID]=[T Problem Report].[EMVCoID]
ORDER BY [T IWG Summary].[ReportOrder], [T IWG Summary].[EMVCoID] DESC;

It was suggested to use DISTINCT in this, but it didn't work. Is there
anything else I could try? Thank you

 

[Andy Hull]

Hi

We need a bit more info.

At the moment you're getting 2 or more records with the same EMVCoID but
with differing values in at least one other column.

(If all columns were the same then DISTINCT would have worked for you).

What are the rules for deciding which of the 2 differing records you want to
display?

Eg Do you want the one with the latest date or the lowest cost or is there
some other rule you have?

Regards

Andy Hull