finding unique values within multiple groups

[buscher75]

Hello, I have a table that holds records of part numbers for different units
we build. Each time a part is used in a different unit another record is
created. So, there could be multiple records for a single part. Within the
hierarchy of the table we have “model#†that each unit belongs to.

I want to run a query that will show any part that it unique to a specific
model. The query should produce results for the specific model only and
should exclude any part that is used on another model even if is also used on
the specific model.

Here is my sample table:

Model# Unit# Part#
Model A ABC X456
Model A DEF Z123
Model A DEF X456
Model B GHI Z123
Model C JKL Z123

Hypothetically, I only want to see the first and third record because part
Z123 is used elsewhere in another Model.

Model A ABC X456
Model A DEF X456



Does anyone have a solution? Thanks in advance!

 

[John Spencer]

Two query solution

First query (saved) to get unique list of models and parts
SELECT DISTINCT [Model#], [Part#]
FROM [SampleTable]

Second query using the first query in a subquery in the WHERE clause
SELECT [Model#], [Unit#], [Part#]
FROM [SampleTable]
WHERE [Part#] in
(SELECT [Part#]
FROM Query1
GROUP BY [Part#]
HAVING Count([Part#])=1)

That could be done in one query with subqueries if your table name and field
names conform to the naming guidelines - only letters, numbers, and the
underscore character)

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

 

[rlambert]

Hi John,

I have a similar problem but being an SQL dunce I need a bit more detail
please.

I have table that holds electric meter readings by property so there is more
than one record per property.

I want to select the property details for the last meter reading.

The following query provides the right details, however I need to add all the
other data fields for the property. if I add this, I also need to add them to
the GROUP BY which then makes each record unique so that doesn't work.

(SELECT DISTINCT [Cust data].[Prop Ref] AS [Prop-Ref], Max([Cust data].[last
reading date]) AS [Max Of Read Date]
FROM [Cust data]
GROUP BY [Cust data].[Prop Ref]);

How can I use the results from this query to then reselect the correct rows
from table, I imagine it would be something like this ?

SELECT [Cust data].[Prop Ref] AS [Prop-Ref], [Cust data].[Prop stat] AS [Prop-
Stat], [Cust data].[Prop type] AS [Prop-Type], [Cust data].[Supp conn date]
AS [Supply-Connected-Date], [Cust data].[Prop stat date] AS [Prop-Stat-Date],
[Cust data].Area AS [Prop-Area], [Cust data].District AS [Prop-District],
[Cust data].[district desc] AS [District-Description], [Cust data].[Detailed
address2] AS [Prop-Address-2]
FROM [Cust data]
Where
[Prop Ref] from Query1 = [Cust data].[Prop Ref]
and [Max Of Read Date] from Query1 = [Cust data].[last reading date]


HELP please, this is driving me nuts trying to sort out. it should be simple !
!!!!


Cheers
Richard

Two query solution

First query (saved) to get unique list of models and parts
SELECT DISTINCT [Model#], [Part#]
FROM [SampleTable]

Second query using the first query in a subquery in the WHERE clause
SELECT [Model#], [Unit#], [Part#]
FROM [SampleTable]
WHERE [Part#] in
(SELECT [Part#]
FROM Query1
GROUP BY [Part#]
HAVING Count([Part#])=1)

That could be done in one query with subqueries if your table name and field
names conform to the naming guidelines - only letters, numbers, and the
underscore character)

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

Hello, I have a table that holds records of part numbers for different units
we build. Each time a part is used in a different unit another record is

[quoted text clipped - 22 lines]

Does anyone have a solution? Thanks in advance!

 

[buscher75]

Thank you, John. The query worked.

I do have one additional question though. I keyed in “ModelA†in the
criteria section for the Model# and got an error when trying to run the
query. It says: “HAVING clause ([Sample Table].[Model]=â€MODELAâ€) with out
grouping or aggregation.†What do I need to do in order to be able to filter
down to a specific model type. I have 20,000+ line items in my real table,
and who know how many unique values. Thanks again for the help, I really
appreciate it.

Two query solution

First query (saved) to get unique list of models and parts
SELECT DISTINCT [Model#], [Part#]
FROM [SampleTable]

Second query using the first query in a subquery in the WHERE clause
SELECT [Model#], [Unit#], [Part#]
FROM [SampleTable]
WHERE [Part#] in
(SELECT [Part#]
FROM Query1
GROUP BY [Part#]
HAVING Count([Part#])=1)

That could be done in one query with subqueries if your table name and field
names conform to the naming guidelines - only letters, numbers, and the
underscore character)

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

Hello, I have a table that holds records of part numbers for different units
we build. Each time a part is used in a different unit another record is
created. So, there could be multiple records for a single part. Within the
hierarchy of the table we have “model#†that each unit belongs to.

I want to run a query that will show any part that it unique to a specific
model. The query should produce results for the specific model only and
should exclude any part that is used on another model even if is also used on
the specific model.

Here is my sample table:

Model# Unit# Part#
Model A ABC X456
Model A DEF Z123
Model A DEF X456
Model B GHI Z123
Model C JKL Z123

Hypothetically, I only want to see the first and third record because part
Z123 is used elsewhere in another Model.

Model A ABC X456
Model A DEF X456



Does anyone have a solution? Thanks in advance!

 

[John Spencer]

The easiest way would be to use a join between the table and the query

SELECT [Cust data].[Prop Ref] AS [Prop-Ref]
, [Cust data].[Prop stat] AS [Prop-Stat]
, [Cust data].[Prop type] AS [Prop-Type]
, [Cust data].[Supp conn date]AS [Supply-Connected-Date]
, [Cust data].[Prop stat date] AS [Prop-Stat-Date]
, [Cust data].Area AS [Prop-Area]
, [Cust data].District AS [Prop-District]
, [Cust data].[district desc] AS [District-Description]
, [Cust data].[Detailedaddress2] AS [Prop-Address-2]
FROM [Cust data] INNER JOIN Query1
ON [Cust Data].[Prop Ref] = Query1.[Prop Ref]
AND [Cust data].[last reading date] = Query1.[Max Of Read Date]

If you needed this to be updateable, then you would have to change this to use
a correlated sub-query.

SELECT *
FROM [Cust Data]
WHERE [Cust data].[last reading date] =
((SELECT Max([Temp].[last reading date]) AS [Max Of Read Date]
FROM [Cust data] as Temp
WHERE Temp.[Prop Ref] = [Cust data].[Prop Ref])


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

Hi John,

I have a similar problem but being an SQL dunce I need a bit more detail
please.

I have table that holds electric meter readings by property so there is more
than one record per property.

I want to select the property details for the last meter reading.

The following query provides the right details, however I need to add all the
other data fields for the property. if I add this, I also need to add them to
the GROUP BY which then makes each record unique so that doesn't work.

(SELECT DISTINCT [Cust data].[Prop Ref] AS [Prop-Ref], Max([Cust data].[last
reading date]) AS [Max Of Read Date]
FROM [Cust data]
GROUP BY [Cust data].[Prop Ref]);

How can I use the results from this query to then reselect the correct rows
from table, I imagine it would be something like this ?

SELECT [Cust data].[Prop Ref] AS [Prop-Ref], [Cust data].[Prop stat] AS [Prop-
Stat], [Cust data].[Prop type] AS [Prop-Type], [Cust data].[Supp conn date]
AS [Supply-Connected-Date], [Cust data].[Prop stat date] AS [Prop-Stat-Date],
[Cust data].Area AS [Prop-Area], [Cust data].District AS [Prop-District],
[Cust data].[district desc] AS [District-Description], [Cust data].[Detailed
address2] AS [Prop-Address-2]
FROM [Cust data]
Where
[Prop Ref] from Query1 = [Cust data].[Prop Ref]
and [Max Of Read Date] from Query1 = [Cust data].[last reading date]


HELP please, this is driving me nuts trying to sort out. it should be simple !
!!!!


Cheers
Richard

Two query solution

First query (saved) to get unique list of models and parts
SELECT DISTINCT [Model#], [Part#]
FROM [SampleTable]
Second query using the first query in a subquery in the WHERE clause
SELECT [Model#], [Unit#], [Part#]
FROM [SampleTable]
WHERE [Part#] in
(SELECT [Part#]
FROM Query1
GROUP BY [Part#]
HAVING Count([Part#])=1)

That could be done in one query with subqueries if your table name and field
names conform to the naming guidelines - only letters, numbers, and the
underscore character)

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

Hello, I have a table that holds records of part numbers for different units
we build. Each time a part is used in a different unit another record is

[quoted text clipped - 22 lines]

Does anyone have a solution? Thanks in advance!

 

[rlambert]

Many thanks John. Worked a treat.

Cheers
Richard

The easiest way would be to use a join between the table and the query

SELECT [Cust data].[Prop Ref] AS [Prop-Ref]
, [Cust data].[Prop stat] AS [Prop-Stat]
, [Cust data].[Prop type] AS [Prop-Type]
, [Cust data].[Supp conn date]AS [Supply-Connected-Date]
, [Cust data].[Prop stat date] AS [Prop-Stat-Date]
, [Cust data].Area AS [Prop-Area]
, [Cust data].District AS [Prop-District]
, [Cust data].[district desc] AS [District-Description]
, [Cust data].[Detailedaddress2] AS [Prop-Address-2]
FROM [Cust data] INNER JOIN Query1
ON [Cust Data].[Prop Ref] = Query1.[Prop Ref]
AND [Cust data].[last reading date] = Query1.[Max Of Read Date]

If you needed this to be updateable, then you would have to change this to use
a correlated sub-query.

SELECT *
FROM [Cust Data]
WHERE [Cust data].[last reading date] =
((SELECT Max([Temp].[last reading date]) AS [Max Of Read Date]
FROM [Cust data] as Temp
WHERE Temp.[Prop Ref] = [Cust data].[Prop Ref])

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

Hi John,

[quoted text clipped - 62 lines]

[quoted text clipped - 22 lines]
Does anyone have a solution? Thanks in advance!

 

[buscher75]

I got it to work, I needed to change the value of the field to where and then
add the field again. Also, FYI to any reading, dont forget to change the
"group by" to "expression" on the added field. Thanks again, John.

Thank you, John. The query worked.

I do have one additional question though. I keyed in “ModelA†in the
criteria section for the Model# and got an error when trying to run the
query. It says: “HAVING clause ([Sample Table].[Model]=â€MODELAâ€) with out
grouping or aggregation.†What do I need to do in order to be able to filter
down to a specific model type. I have 20,000+ line items in my real table,
and who know how many unique values. Thanks again for the help, I really
appreciate it.

Two query solution

First query (saved) to get unique list of models and parts
SELECT DISTINCT [Model#], [Part#]
FROM [SampleTable]

Second query using the first query in a subquery in the WHERE clause
SELECT [Model#], [Unit#], [Part#]
FROM [SampleTable]
WHERE [Part#] in
(SELECT [Part#]
FROM Query1
GROUP BY [Part#]
HAVING Count([Part#])=1)

That could be done in one query with subqueries if your table name and field
names conform to the naming guidelines - only letters, numbers, and the
underscore character)

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

Hello, I have a table that holds records of part numbers for different units
we build. Each time a part is used in a different unit another record is
created. So, there could be multiple records for a single part. Within the
hierarchy of the table we have “model#†that each unit belongs to.

I want to run a query that will show any part that it unique to a specific
model. The query should produce results for the specific model only and
should exclude any part that is used on another model even if is also used on
the specific model.

Here is my sample table:

Model# Unit# Part#
Model A ABC X456
Model A DEF Z123
Model A DEF X456
Model B GHI Z123
Model C JKL Z123

Hypothetically, I only want to see the first and third record because part
Z123 is used elsewhere in another Model.

Model A ABC X456
Model A DEF X456



Does anyone have a solution? Thanks in advance!

 

[buscher75]

Hi John, the SQL statement you provided my has worked great. I tried to
alter you statement (go figure) to determine unique parts that are listed
between two models. So, I want parts that are used on both MODEL A and MODEL
B and no where else. This is what I have, and it does not work quite right:

SELECT [Sample Table].PhaseModel, [Sample Table].SystemNumb, [Sample
Table].SystemDesc, [Sample Table].PartNumb
FROM [Sample Table]
WHERE ((([Sample Table].PhaseModel)="MODEL A") AND (("MODEL B")<>False) AND
(([Sample Table].PartNumb) In (SELECT [PartNumb] FROM X123TestSub Group By
[Sample Table].[PartNumb] HAVING Count([X123TestSub].[PartNumb])=2)));

The results provide any part number listed twice where "MODEL A" is one of
the records. It does not exclusively pull the "MODEL B" record as the second
record. Any thoughts??

Two query solution

First query (saved) to get unique list of models and parts
SELECT DISTINCT [Model#], [Part#]
FROM [SampleTable]

Second query using the first query in a subquery in the WHERE clause
SELECT [Model#], [Unit#], [Part#]
FROM [SampleTable]
WHERE [Part#] in
(SELECT [Part#]
FROM Query1
GROUP BY [Part#]
HAVING Count([Part#])=1)

That could be done in one query with subqueries if your table name and field
names conform to the naming guidelines - only letters, numbers, and the
underscore character)

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

Hello, I have a table that holds records of part numbers for different units
we build. Each time a part is used in a different unit another record is
created. So, there could be multiple records for a single part. Within the
hierarchy of the table we have “model#†that each unit belongs to.

I want to run a query that will show any part that it unique to a specific
model. The query should produce results for the specific model only and
should exclude any part that is used on another model even if is also used on
the specific model.

Here is my sample table:

Model# Unit# Part#
Model A ABC X456
Model A DEF Z123
Model A DEF X456
Model B GHI Z123
Model C JKL Z123

Hypothetically, I only want to see the first and third record because part
Z123 is used elsewhere in another Model.

Model A ABC X456
Model A DEF X456



Does anyone have a solution? Thanks in advance!