Alastair Give this a try PRINT report WHERE groupMemberName IN + (SEL GroupMemberName FROM sometable GROUP BY GroupMemberName HAVING COUNT(*) > 1)
Buddy -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Alastair Burr Sent: Wednesday, May 25, 2005 1:04 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Selecting Rows for a report using Count(ColName) I have a report that works fine but I want to limit the data reported to only those rows where the count of entries in a column is greater than 1. In other words, I only want to see the rows where there is more than one entry for a particular item (but they are not duplicates). My first attempt at the where clause was this: WHERE COUNT(GroupMemberName) > 1 which seems obvious but doesn't work - I get an error message about the syntax. To cut a long story short I ended up creating a temp table and then inserting the relevant codes: INSERT INTO Temp_Table SELECT GroupMemberName + FROM GroupRep_HTML_View + GROUP BY GroupMemberName + HAVING COUNT(GroupMemberName) > 1 GroupRep_HTML_View is the driving table for the report. (I couldn't get a project to work here, either, to do it in one command.) and then using this where clause to run the report: WHERE GroupMemberName IN + (SELECT GroupMemberName FROM Temp_Table) This gives me exactly what I want but seems a long-winded way around. Am I missing something obvious for a simpler solution? Thanks in advance for any enlightenment, Regards, Alastair. ---------------------------------- A D B Burr, St. Albans, UK. ---------------------------------- [EMAIL PROTECTED] ----------------------------------
