Making a query for missing matches

Yes, thank you so much! I have figured out the first part and am working on
the details part. We have lost our resident access guru so thank you again.

"John Spencer" wrote:

> 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?
>

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.

"John Spencer" wrote:

> 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?
>

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
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.

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

PJ wrote:
> 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.
>
> "John Spencer" wrote:
>
>> 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?

ok. So i tried option #2 & 3 to no avail. Here is the SQL for Option #1.
Thanks.

SELECT [All Thin Clients and Monitors with AOPC ID].AOPCEquipmentName
FROM [All Thin Clients and Monitors with AOPC ID]
GROUP BY [All Thin Clients and Monitors with AOPC ID].AOPCEquipmentName,
[All Thin Clients and Monitors with AOPC ID].AOPCEquipmentName
HAVING ((("Count")<>"2"));


"John Spencer" wrote:

> 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
> 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.
>
> 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
>
> PJ wrote:
> > 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.
> >
> > "John Spencer" wrote:
> >
> >> 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?
>

John, I tried option #3 and received all of the records. I think the problem
is that the field name (ID# or rather "AOPCEquipmentName") only contains only
about 4-6 variables and the 1st, 2nd and 3rd variables are duplicated or
rather continually appear once per per another field (DJ#) about every 580
records. How could I add this exclusion in the query criteria? Right now
this is the in the query criteria:

In (SELECT [AOPCEquipmentName] FROM [All Thin Clients and Monitors with AOPC
ID] As Tmp GROUP BY [AOPCEquipmentName] HAVING Count(*)>1 )

Please help.

"PJ" wrote:

> ok. So i tried option #2 & 3 to no avail. Here is the SQL for Option #1.
> Thanks.
>
> SELECT [All Thin Clients and Monitors with AOPC ID].AOPCEquipmentName
> FROM [All Thin Clients and Monitors with AOPC ID]
> GROUP BY [All Thin Clients and Monitors with AOPC ID].AOPCEquipmentName,
> [All Thin Clients and Monitors with AOPC ID].AOPCEquipmentName
> HAVING ((("Count")<>"2"));
>
>
> "John Spencer" wrote:
>
> >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
> >
> > PJ wrote:
> > > 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.
> > >
> > > "John Spencer" wrote:
> > >
> > >> 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?
> >

I'm sorry but I am not sure that I understand your structure or your question?
If you need to use additional fields to determine if you have duplicates
then add them to the group by clause and to the Having clause where you
compare the fields in the main query with the fields in the subquery.

That would look something like the following.
SELECT E.AOPCEquipmentName, Field1, Field2, Field3
FROM [All Thin Clients and Monitors with AOPC ID] as E
In (SELECT [AOPCEquipmentName]
FROM [All Thin Clients and Monitors with AOPC ID] As Tmp
GROUP BY [AOPCEquipmentName], Field1, Field2, Field3
HAVING Count(*)>1 and Tmp.Field1 = E.Field1 and Tmp.Field2 = E.Field2 AND
Tmp.Field3 = E.Field3)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

PJ wrote:
> John, I tried option #3 and received all of the records. I think the problem
> is that the field name (ID# or rather "AOPCEquipmentName") only contains only
> about 4-6 variables and the 1st, 2nd and 3rd variables are duplicated or
> rather continually appear once per per another field (DJ#) about every 580
> records. How could I add this exclusion in the query criteria? Right now
> this is the in the query criteria:
>
> In (SELECT [AOPCEquipmentName] FROM [All Thin Clients and Monitors with AOPC
> ID] As Tmp GROUP BY [AOPCEquipmentName] HAVING Count(*)>1 )
>
> Please help.
>
> "PJ" wrote:
>
>> ok. So i tried option #2 & 3 to no avail. Here is the SQL for Option #1.
>> Thanks.
>>
>> SELECT [All Thin Clients and Monitors with AOPC ID].AOPCEquipmentName
>> FROM [All Thin Clients and Monitors with AOPC ID]
>> GROUP BY [All Thin Clients and Monitors with AOPC ID].AOPCEquipmentName,
>> [All Thin Clients and Monitors with AOPC ID].AOPCEquipmentName
>> HAVING ((("Count")<>"2"));
>>
>>
>> "John Spencer" wrote:
>>
>>> 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
>>>
>>> PJ wrote:
>>>> 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.
>>>>
>>>> "John Spencer" wrote:
>>>>
>>>>> 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?