Duplicate record

Hi the below query returns the records i want but I also need it to return distinct values based upon one field.

I am using MSAccess

SELECT videovault.Path, ColourType.ColourPath, videovault.EffectType
FROM ColourType
INNER JOIN VideoVault ON ColourType.ColourName = VideoVault.EffectType
WHERE videovault.productCode = 'J72673'

There is a field called EffectType that needs to be distinct no matter what.

I have tried the distinct clause and group by function but it still returns the records with the field EffectType having duplicate entries.

Any ideas on what to do please?


yes, i do, but before we get into it, could you give some sample data for each table please

i will be asking you how to decide which values you want for the duplications

if you don’t really care, then you can simply run this –

SELECT [COLOR="Blue"]MIN(VideoVault.Path)[/COLOR] AS Path
     , [COLOR="blue"]MAX(ColourType.ColourPath)[/COLOR] AS ColourPath
     , VideoVault.EffectType
  FROM VideoVault
  JOIN ColourType
    ON ColourType.ColourName = VideoVault.EffectType
 WHERE VideoVault.productCode = 'J72673'
    BY VideoVault.EffectType


Here is some sample data from each table:




The query i posted returns the necessary records except it returns duplicate records for the field ColourType.ColourPath and videovault.EffectType.

For example two records that will display red in the field videovault.EffectType which means ColourType.ColourPath will display the absolute path to the red image button twice.

I hope this makes sense what I am trying to say.


i understand that you’re having a problem but you haven’t given enough information to solve it

did you try my query with the MIN and MAX?


Yes I have tried it with your query now and it seems to work.

I am going to test it further against more products in the database. Where did I go wrong? was it with the group by clause or min and max.

I spent all of yesterday trying different combos of queries and still couldnt get it :confused:

Sorry about the lack of information i must of misunderstood what you meant. Did you mean a record containing all fields from the tables in the query?

Thanks for your help, was there another query you was thinking of?

what i was looking for was to see actual sample data that would illustrate the duplicates problem

if the query i gave you works for you, then that’s good, yes?