I need to create a distinct query with the following peculiarities.

Idcode, Source, description.

The Source can have repetitive Idcodes making for apparent duplicates. So I then combine the two with a query creating a unique code.

ucode : [idcode] & [source]

Ucode, Idcode, Source, Description

That then provides me with a unique key. Other than creating a temporary table with no duplicates allowed and doing a make table query how can I have destinct on the Ucode but include the other fields. The total with min, max, last etc. do not work. Who has any ideas please.

Show us the sql of what you have tried please.

Create another query with this as the source

Have you tried creating a distinct query that fetches Idcode and Source as separate fields? That would give you one row for each different combination, too. Then another query that joins this query back to the table on the Idcode and source fields to get the rest of the fields that you need.

I did just that.

Once I have the query result with just one unique code, I then use that as the basis for my next query, When joining them I end up with all the records again.

This is the initial code combine query structure


Query name "Ucode" (Returns some 1,090,000 records)

SELECT [IDcode] & [Source] AS Ucode, system.Source, system.IDcode, system.description
FROM system;


Then to obtain the Unique code I use

Query name "Ucode Distinct" (Retuns some 480 Records)

SELECT DISTINCT Ucode.Ucode
FROM Ucode;

Now to combine them again into this

Ucode, Idcode, Source, Description

Final query (Returns all the 1,090,000 records)

SELECT [ucode distinct].Ucode, Ucode.EventSource, Ucode.EventID, Ucode.EventText
FROM [ucode distinct] LEFT JOIN Ucode ON [ucode distinct].Ucode = Ucode.Ucode;


This is where things just dont go my way.

I'm talking about doing this:

SELECT DISTINCT [TableName].IDCode, [TableName].Source FROM [TableName]

SELECT blah blah blah FROM DistinctQuery JOIN [TableName] ON [DistinctQuery].IDCode = [TableName].IDCode AND [DistinctQuery].Source = [TableName].Source