Finding Duplicates in Table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pete - 20 Feb 2007 22:40 GMT

Hey
Got a situation where I need to provide a list of Software Descriptions
(swSoftwareDesc) for each asset (swAsset) where there is more than 1 Version
(swVersion) An e.g. would be Abode Reader V4 & Adobe Reader V5 found on
Freds PC. Any suggestions. I'm a bit of a newbe at this so easy is good :)

Pete

Reply to this Message

Rolls - 21 Feb 2007 05:47 GMT

QBE window: Select the fields you're interested in, add one of the fields
you used again, select "group" (sigma sign) then change "group by" to
"count" then add >1 to the crireria which will count the number of records
where the comvination of fields you selected appears more than once.

Reply to this Message

John Spencer - 21 Feb 2007 12:25 GMT

SELECT swAsset, swSoftwareDesc, Count(swVersion) as VersionCount
FROM YourTable
GROUP BY swAsset, swSoftwareDesc
HAVING Count(swVersion) > 1

In Design view,
-- Set up a query to return the three fields
-- Select View: Totals from the menu
-- Change GROUP BY to Count under swVersion
-- Enter >1 in the criteria under swVersion

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Hey
> Got a situation where I need to provide a list of Software Descriptions
[quoted text clipped - 4 lines]
>
> Pete

Reply to this Message