1.) Post the SQL of the query you have that returns 56 records.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
OR
2.) Create a query that returns all the fields you want to see.
Under the id field, you will need to enter criteria that looks like the following
Field: ID#
Criteria: IN ( SELECT [ID#] FROM [SomeTable] GROUP BY [ID#] HAVING
Count([ID#]) <> 2 )
Replace SomeTable with the name of your table and if ID# is not the field name
then replace that.
3.) An alternative is to use the Duplicates Query wizard to find duplicated
records and edit it to find records that are not duplicated.
In the database window, on the queries tab
--Select Insert: Query from the Menu
--In the dialog box, Select Find Duplicates Query Wizard
--In the next window, select your Table or Query
--In the next window, select the field with duplicates
--In the next window, select any additional fields you want to display.
--In the next window, Name your query and click Finish
NOw change the where Count(SomeField) > 1 in the subquery to Count(somefield) <> 2
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Ok. I got the 1st part to work and received 56 records with the field
results but I can't seem to get the second part (details) to show. Do i have
to create a 2nd query? Mind that I don't understand how to write SQL so I am
working in the design view. Thanks again.
:
SELECT [ID#]
FROM SomeTable
GROUP BY [ID#]
HAVING Count([ID#]) <> 2
If you want details then
SELECT *
FROM SomeTable
WHERE [ID#] in
(SELECT [ID#]
FROM SomeTable
GROUP BY [ID#]
HAVING Count([ID#]) <> 2)
ORDER BY [Id#]
The above will return all ID# that don't appear exactly 2 times. So ID# that
are in 1 or 3 or 4 records will be returned. IF you want only those appearing
one time change <> 2 to =1.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
PJ wrote:
Say i have the following fields and sample data:
ID# 1
1
Manufacturer Dell
Wyse
Model Type Monitor
Thin Client
Serial numbers 22bb
6jtbf
There should be 2 of the same ID#'s for each model type and manufacturer.
How do i query to find out which serial numbers does not have a matching ID#?
Does that make sense?