A query that shows repeats

[RobertM]

I recall a "Having Count" query that will show when there are duplicates in a
table. I have a table that has a feild where we can have a certain value once
or multiple times. I only want to see when the value repeats itself, not when
it only shows once.

 

[Duane Hookom]

SELECT [a feild], Count(*) as NumOf
FROM [a table]
GROUP BY [a feild]
HAVING Count(*) > 1;

 

[Rob]

Duane,

I am having the reverse problem. My query shows duplicates when I do not
want it to. Here is my situation. I have a table that has a yes/no field.
I want to only show the records from two tables that have matching data
(based on two fields matching) but only for those records where the yes/no
field is set to yes. If I create a query on the original table and tell it
to only show the yes answers it shows my 6293 records. The minute I add the
other table to the query and join them by the two fields that need to match
the total number of records changes. If I tell the two joins to show all
records from the first table and only those that match from the second
(option 2) I get a smaller amount but still many diplicates. If I try to
group I get about 700 records more than I should. I have even tried to show
unique records or values only but I still get the same result. Here is a
copy of my SQL:

SELECT DISTINCTROW [VIN] & "-" & [Ref/RO] AS VINRO, QCSVINRO.SPI,
QCSVINRO.SPIIssueDescription
FROM [GM VINRO] LEFT JOIN QCSVINRO ON [GM VINRO].GM = QCSVINRO.QCS
GROUP BY [VIN] & "-" & [Ref/RO], QCSVINRO.SPI, QCSVINRO.SPIIssueDescription;


Any help would be greatly appreciated.

Rob

SELECT [a feild], Count(*) as NumOf
FROM [a table]
GROUP BY [a feild]
HAVING Count(*) > 1;

--
Duane Hookom
Microsoft Access MVP

I recall a "Having Count" query that will show when there are duplicates in a
table. I have a table that has a feild where we can have a certain value once
or multiple times. I only want to see when the value repeats itself, not when
it only shows once.

 

[Duane Hookom]

Nearly impossible to provide a solution without seeing your data and a whole
lot more.

Learn how to troubleshoot by paring your records down to a very small set
and identifying issues in that smaller set. Then determine a fix and apply it
to your larger set.

--
Duane Hookom
Microsoft Access MVP

Duane,

I am having the reverse problem. My query shows duplicates when I do not
want it to. Here is my situation. I have a table that has a yes/no field.
I want to only show the records from two tables that have matching data
(based on two fields matching) but only for those records where the yes/no
field is set to yes. If I create a query on the original table and tell it
to only show the yes answers it shows my 6293 records. The minute I add the
other table to the query and join them by the two fields that need to match
the total number of records changes. If I tell the two joins to show all
records from the first table and only those that match from the second
(option 2) I get a smaller amount but still many diplicates. If I try to
group I get about 700 records more than I should. I have even tried to show
unique records or values only but I still get the same result. Here is a
copy of my SQL:

SELECT DISTINCTROW [VIN] & "-" & [Ref/RO] AS VINRO, QCSVINRO.SPI,
QCSVINRO.SPIIssueDescription
FROM [GM VINRO] LEFT JOIN QCSVINRO ON [GM VINRO].GM = QCSVINRO.QCS
GROUP BY [VIN] & "-" & [Ref/RO], QCSVINRO.SPI, QCSVINRO.SPIIssueDescription;


Any help would be greatly appreciated.

Rob

SELECT [a feild], Count(*) as NumOf
FROM [a table]
GROUP BY [a feild]
HAVING Count(*) > 1;

--
Duane Hookom
Microsoft Access MVP

I recall a "Having Count" query that will show when there are duplicates in a
table. I have a table that has a feild where we can have a certain value once
or multiple times. I only want to see when the value repeats itself, not when
it only shows once.