Hi,

I have a query that contains two fields; the name of a chemical and its function group. Some of the chemicals are used in more than one function group, I would like to list these chemicals and their different function groups separately from the chemicals that are only used in one function group.

Just to illustrate;

Original Query
chemical A Function 3
chemical B Function 1
chemical B Function 2
chemical C Function 3
chemical D Function 1
chemical D Function 4
chemical D Function 5

From this query I would like;

chemical B Function 1
chemical B Function 2
chemical D Function 1
chemical D Function 4
chemical D Function 5

It seems simple enough but I just can't figure it out...

Thank you :o)

well the way you spelled it out, you could simply check for a count > 1. if the data is the way you show it, try this simpleton:SELECT *

FROM query

WHERE ((([CHEMICAL]) In

(SELECT [CHEMICAL] FROM [query] As Tmp

GROUP BY [CHEMICAL] HAVING Count(*)>1 )))the SQL that looks like it was produced by access WAS. I made a FIND DUPLICATE query and got the code from that. from the looks of it, you can use the same thing here.

That did the job, thank you! I just have one more little problem with it though, I have another field that can cause there to be more than 1 record of the same chemical as well. I have managed to remove it from the query by changing the SQL that you gave me slightly:

SELECT query.Chemicals, query.Function
FROM query
WHERE ((([Chemicals]) In
(SELECT [Chemicals] FROM [query] As Tmp
GROUP BY [Chemicals] HAVING Count (*)>1)));

but even though its not shown in the query it is still influencing the chemical field. How do I just use the chemical and function fields in my query? I tried changing the * on the last line to (query.Chemicals, query.Function) but I got an error.

I managed to figure it out! I went into design view and saw that the third field wasn't selected at all so I turned on the 'Unique values' property and it has worked perfectly.

Thanks again for your help :-)