SQL Query based on count on Report Page

How are you getting duplicates if its a unique number?
try this link SQL Duplicates

Wildwally,
Thank you for your response. The "matchcode" is a unique number, but a number that can have duplicates. In fact, that is what we are looking for is any duplicates of the "matchcode" I looked at the web page you provided and it helped a bit, but I dont think that I am understanding GROUP BY in SQL. PHP Runner automatically groups the duplicate "matchcodes" on the Report List Page. What I want to do is take away all of the "matchcodes" that only are listed once in the database, and only list the "matchcodes" that have duplicates and are grouped together on the Report List Page. These will have a group count of 2 or more.
Thanks,
Brian

From that link provided there is a section explaining why the Where does not work with Group By.
With that in mind something like this should get you your desired results.
select MatchCode, count() from table group by matchcode HAVING count() > 1
Of course you'll need to make the changes accordingly.

WildWally,
Thank you for your help! I did get the results I was looking for. I am better understanding this now...for some reason GROUP BY is difficult for me. Now here is one more request. I have some other columns that I need displayed with regards to the results. In addition to grouping the matchcode, I have to display the rest of the columns in the table. Here is what the query looks like without grouping:
SELECT

table_id,

field1,

field2,

field3,

matchcode

FROM tablename
Here is what works with what you have told me:
SELECT

matchcode,

COUNT ()

FROM tablename

GROUP BY matchcode

HAVING COUNT () >1
I need to include field1, field2, field3 in the query keeping matchcode as the GROUP BY column. This has really been what I am having problems with.
I would like the results to look something like:
matchcode | table_id | field1 | field2 | field3
Thanks again in advance for your help on this!
Brian

Just add the fields you want to return.
SELECT

matchcode, table_id, field1, field2, field3,

COUNT ()

FROM tablename

GROUP BY matchcode

HAVING COUNT () >1

WildWally,
When I do that query, I get "IDispatch error #3092 Column 'table_id' is invalid in the select list becuase it is not contained in either an aggregate function for the GROUP BY cluase"
SELECT

matchcode,

table_id,

field1,

field2,

field3,

COUNT()

FROM tablename

GROUP BY matchcode

HAVING COUNT() >1
If I GROUP BY all of the columns, I dont get an error, but matchcode is no longer "grouped" and all of the matchcodes are counted as 1 and I get no results from the query because of the HAVING >1
Brian

Try this to see if you get the results your looking for:
SELECT

matchcode,

table_id,

field1,

field2,

field3,

COUNT(matchcode)

FROM tablename

GROUP BY matchcode,

table_id,

field1,

field2,

field

HAVING COUNT(matchcode) >1
Testing on my system I'm able to get results until I add a field that does not have matching results. So it will only pull back the results if all of those fields match.

WildWally,
That is the problem that I am coming across as well. Unfortunately, I need to group together the matchcodes that are duplicates, but some of the fields on that record do not match. I need to be able to display them. The weird thing is that when I run the report I created to do this, it works exactly like I want to, but the killer is trying to figure out how to display only the records that have the grouped together matchcodes...any thoughts Xlinesoft?
Thanks, you guys are all great for newbs like me!
Brian

WildWally,
Well after thinking a bit more on this, I tried something different and I was able to get it to work! This is what I did...with help from our friend Google:
Select

table_id,

field1,

field2,

field3,

matchcode

FROM tablename

WHERE (matchcode IN

(SELECT matchcode FROM tablename AS tablename_1

GROUP BY matchcode

HAVING (COUNT (*) > 1)))
Thank you for your help on this and hopefully this will help someone else. Now when I run the query I get 5 results back, all with the fields I need. I then group the matchcode field in the Group area of PHP Runner with Reports. I then selected matchcode as my group. When I run the report, I get back 2 results, one with 3 records grouped and one with 2 records grouped.
Brian

Ok, played around a little more and was able to test this in my db where i have similar conditions. I too am still learning this stuff - but have had a little more time behind SQL.
Select Matchcode, Table_id, feild1, feild2, field3 from tablename where matchcode = (select matchcode from tablename group by matchcode having count(matchcode)>1);
See your post that you were able to get it.

WildWally,
Read what I posted right before your last post. I think that yours will work as well!
Thanks again for your help!
Brian