Making a query for missing matches

  • Thread starter Thread starter PJ
  • Start date Start date
P

PJ

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?
 
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
 
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 said:
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
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 said:
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
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
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 said:
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
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 said:
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
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 said:
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 said:
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 said:
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?
 
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
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 said:
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 said:
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.

:

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