Pete- Like this:
SELECT [ID], [Product], [Order Date], [Cost] FROM [Order Table] WHERE ((([ID]) In (SELECT [ID] FROM [Order Table] As Tmp GROUP BY [ID], [Product] HAVING Count(*)>1 ))) ORDER BY [ID], [Order Date]; John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ > -----Original Message----- > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of Bradshaw, Peter A C301 > Sent: Wednesday, August 03, 2005 1:32 PM > To: [email protected] > Subject: [ms_access] Find Duplicate Query > > > Given the following simple example table: > > ID > Product > Order date > Cost > > If I wanted to find duplicate ID's, I would write a query like this: > > SELECT DISTINCTROW [ID], [Product], [Order Date], [Cost] > FROM [Order Table] > WHERE ((([ID]) In (SELECT [ID] FROM [Order Table] As Tmp GROUP BY [ID] > HAVING Count(*)>1 ))) > ORDER BY [ID], [Order Date]; > > How would I modify the query if I wanted to only find dupes if ID and > Product (together) show up twice. In other words, the following would > show up as a duplicate ont eh first query > > ID Product Order DT Cost > 001 Chips 01/01/2005 30.00 > 001 Chips 01/15/2005 30.00 > 001 Candy 02/10/2005 40.00 > 001 Beef 01/13/2005 100.00 > > I want to show > > ID Product Order DT Cost > 001 Chips 01/01/2005 30.00 > 001 Chips 01/15/2005 30.00 > > > Thanks, > Pete Bradshaw > Fulfillment Business Services Production Support > CIGNA HealthCare > 860.226.3019 (N:572.3019) > mailto:[EMAIL PROTECTED] > > > -------------------------------------------------------------- > ---------------- > CONFIDENTIALITY NOTICE: If you have received this email in > error, please immediately notify the sender by e-mail at the > address shown. This email transmission may contain > confidential information. This information is intended only > for the use of the individual(s) or entity to whom it is > intended even if addressed incorrectly. Please delete it > from your files if you are not the intended recipient. Thank > you for your compliance. Copyright 2005 CIGNA > ============================================================== > ================ > > > [Non-text portions of this message have been removed] > > > > ------------------------ Yahoo! Groups Sponsor > --------------------~--> > <font face=arial size=-1><a > href="http://us.ard.yahoo.com/SIG=12hl9ko34/M=362329.6886308.7 > 839368.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123101171 > /A=2894321/R=0/SIG=11dvsfulr/*http://youthnoise.com/page.php?p > age_id=1992 > ">Fair play? Video games influencing politics. Click and talk > back!</a>.</font> > -------------------------------------------------------------- > ------~-> > > > Yahoo! Groups Links > > > > > ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12hoecd5e/M=362329.6886308.7839368.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123102747/A=2894321/R=0/SIG=11dvsfulr/*http://youthnoise.com/page.php?page_id=1992 ">Fair play? Video games influencing politics. Click and talk back!</a>.</font> --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
