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
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 <font color='"Blue"'>MIN(VideoVault.Path)</font> AS Path
, <font color='"blue"'>MAX(ColourType.ColourPath)</font> AS ColourPath
ON ColourType.ColourName = VideoVault.EffectType
WHERE VideoVault.productCode = 'J72673'
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
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?