How to get count of unique records in one-to-many query

[Randall Arnold]

I have a SQL query that builds a list of shipping order errors. Each error
report has one Purchase Order Number (PON) but may have many errors
associated with that order. I aggregate errors based on Description and get
totals of each type. Date ranges are automatically filtered based on dates
on a user form. I can also filter based on Product Type and Area of
Responsibility off of this same user form. This part of my query works as
expected. I have a chart on a report that utilizes this query and it also
functions as desired.

However, I also need to determine the total number of unique purchase orders
and have that number show up in the query rows. I need this because I want
to be able to show, row by row, how many defects occurred out of how many
unique purchase orders (ie, opportunities for error).

I have tried numerous ways to get this to work and all have failed.
Microsoft Access does not seem to follow SQL convention 100% in that I should
be able to use a DISTINCT aggregate with the COUNT function but this produces
a syntax error. The method I expect to work is (simplified):

Select COUNT(DISTINCT PON)...

My actual current SQL is:

SELECT First([Order Setup Defects Query].[Defect Description]) AS [Defect
Description Field], Count([Order Setup Defects Query].[Defect Description])
AS DefectCounts, [Order Setup Defects Query].Responsibility
FROM [Order Setup Defects Query]
WHERE ((([Order Setup Defects Query].[Order Date]) Between
[Forms]![Main]![FromDate_combo] And [Forms]![Main]![ToDate_combo]) AND
(([Forms]![Main]![Product_combo])=[Order Setup Defects Query].[Product Name]
Or ([Forms]![Main]![Product_combo]) Is Null) AND
(([Forms]![Main]![Responsibility_combo])=[Order Setup Defects
Query].[Responsibility] Or ([Forms]![Main]![Responsibility_combo]) Is Null))
OR ((([Forms]![Main]![Product_combo])=[Order Setup Defects Query].[Product
Name] Or ([Forms]![Main]![Product_combo]) Is Null) AND
(([Forms]![Main]![Responsibility_combo])=[Order Setup Defects
Query].[Responsibility] Or ([Forms]![Main]![Responsibility_combo]) Is Null)
AND (([Forms]![Main]![FromDate_combo]) Is Null) AND
(([Forms]![Main]![ToDate_combo]) Is Null))
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup Defects
Query].[Defect ID], [Order Setup Defects Query].[Defect Description]
HAVING (((Count([Order Setup Defects Query].[Defect Description]))>=1))
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];


Anyone have any idea how I can produce the unique purchase order count?

Thanks,

Randall Arnold

 

[S.Clark [Access MVP]]

If this is just an Access query, and not in SQL Server, then...

In the query design view, open the property window for the query, and toggle
the Unique Records and Unique Values properties.

Steve Clark, Access MVP
FMS, Inc.

I have a SQL query that builds a list of shipping order errors. Each error
report has one Purchase Order Number (PON) but may have many errors
associated with that order. I aggregate errors based on Description and get
totals of each type. Date ranges are automatically filtered based on dates
on a user form. I can also filter based on Product Type and Area of
Responsibility off of this same user form. This part of my query works as
expected. I have a chart on a report that utilizes this query and it also
functions as desired.

However, I also need to determine the total number of unique purchase orders
and have that number show up in the query rows. I need this because I want
to be able to show, row by row, how many defects occurred out of how many
unique purchase orders (ie, opportunities for error).

I have tried numerous ways to get this to work and all have failed.
Microsoft Access does not seem to follow SQL convention 100% in that I should
be able to use a DISTINCT aggregate with the COUNT function but this produces
a syntax error. The method I expect to work is (simplified):

Select COUNT(DISTINCT PON)...

My actual current SQL is:

SELECT First([Order Setup Defects Query].[Defect Description]) AS [Defect
Description Field], Count([Order Setup Defects Query].[Defect Description])
AS DefectCounts, [Order Setup Defects Query].Responsibility
FROM [Order Setup Defects Query]
WHERE ((([Order Setup Defects Query].[Order Date]) Between
[Forms]![Main]![FromDate_combo] And [Forms]![Main]![ToDate_combo]) AND
(([Forms]![Main]![Product_combo])=[Order Setup Defects Query].[Product Name]
Or ([Forms]![Main]![Product_combo]) Is Null) AND
(([Forms]![Main]![Responsibility_combo])=[Order Setup Defects
Query].[Responsibility] Or ([Forms]![Main]![Responsibility_combo]) Is Null))
OR ((([Forms]![Main]![Product_combo])=[Order Setup Defects Query].[Product
Name] Or ([Forms]![Main]![Product_combo]) Is Null) AND
(([Forms]![Main]![Responsibility_combo])=[Order Setup Defects
Query].[Responsibility] Or ([Forms]![Main]![Responsibility_combo]) Is Null)
AND (([Forms]![Main]![FromDate_combo]) Is Null) AND
(([Forms]![Main]![ToDate_combo]) Is Null))
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup Defects
Query].[Defect ID], [Order Setup Defects Query].[Defect Description]
HAVING (((Count([Order Setup Defects Query].[Defect Description]))>=1))
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];


Anyone have any idea how I can produce the unique purchase order count?

Thanks,

Randall Arnold

 

[Randall Arnold]

Thanks for taking the time to reply Steve.

Both values were set to No; I switched them to yes and retried what I'd done
before with the same results: no go.

I'm thinking I need 2 queries for what I want to do but I can't even get
that to work. Essentially I'm trying to use MS Graph to create a Pareto
Diagram as shown in MSKB #208373. I tried to apply the instructions specific
to the Northwinds database to my own database without success and can't
figure out what I'm doing wrong.

Right now my query retruns a list of the possible defects. Each defect type
(column 1) is followed by a count of such defects (column 2), ordered
descending by defect count. That part works fine. But what I also need
(column 3) is a running cumulative percentage so that each row of column 3
takes into account the sum of the previous column 2 values. That's the part
that's making my hair fall out. I just flat can't get it working.

Randall Arnold

If this is just an Access query, and not in SQL Server, then...

In the query design view, open the property window for the query, and toggle
the Unique Records and Unique Values properties.

Steve Clark, Access MVP
FMS, Inc.

I have a SQL query that builds a list of shipping order errors. Each error
report has one Purchase Order Number (PON) but may have many errors
associated with that order. I aggregate errors based on Description and get
totals of each type. Date ranges are automatically filtered based on dates
on a user form. I can also filter based on Product Type and Area of
Responsibility off of this same user form. This part of my query works as
expected. I have a chart on a report that utilizes this query and it also
functions as desired.

However, I also need to determine the total number of unique purchase orders
and have that number show up in the query rows. I need this because I want
to be able to show, row by row, how many defects occurred out of how many
unique purchase orders (ie, opportunities for error).

I have tried numerous ways to get this to work and all have failed.
Microsoft Access does not seem to follow SQL convention 100% in that I should
be able to use a DISTINCT aggregate with the COUNT function but this produces
a syntax error. The method I expect to work is (simplified):

Select COUNT(DISTINCT PON)...

My actual current SQL is:

SELECT First([Order Setup Defects Query].[Defect Description]) AS [Defect
Description Field], Count([Order Setup Defects Query].[Defect Description])
AS DefectCounts, [Order Setup Defects Query].Responsibility
FROM [Order Setup Defects Query]
WHERE ((([Order Setup Defects Query].[Order Date]) Between
[Forms]![Main]![FromDate_combo] And [Forms]![Main]![ToDate_combo]) AND
(([Forms]![Main]![Product_combo])=[Order Setup Defects Query].[Product Name]
Or ([Forms]![Main]![Product_combo]) Is Null) AND
(([Forms]![Main]![Responsibility_combo])=[Order Setup Defects
Query].[Responsibility] Or ([Forms]![Main]![Responsibility_combo]) Is Null))
OR ((([Forms]![Main]![Product_combo])=[Order Setup Defects Query].[Product
Name] Or ([Forms]![Main]![Product_combo]) Is Null) AND
(([Forms]![Main]![Responsibility_combo])=[Order Setup Defects
Query].[Responsibility] Or ([Forms]![Main]![Responsibility_combo]) Is Null)
AND (([Forms]![Main]![FromDate_combo]) Is Null) AND
(([Forms]![Main]![ToDate_combo]) Is Null))
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup Defects
Query].[Defect ID], [Order Setup Defects Query].[Defect Description]
HAVING (((Count([Order Setup Defects Query].[Defect Description]))>=1))
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];


Anyone have any idea how I can produce the unique purchase order count?

Thanks,

Randall Arnold

 

[Randall Arnold]

I got it to work!

Here's the SQL without the filter code:

SELECT First([Order Setup Defects Query].[Defect Description]) AS [Defect
Description Field], Count([Order Setup Defects Query].[Defect Description])
AS DefectCounts, Int((DSum("[DefectCounts]","[Order Setup Defects Report
Query]","[DefectCounts]>=" & Count([Defect Description]) &
"")/DSum("[DefectCounts]","[Order Setup Defects Report Query]"))*100) AS
CumPct
FROM [Order Setup Defects Query]
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup Defects
Query].[Defect ID]
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];

It runs slow, but I now get the cumulative percentage in a single query.
Now to add the code that counts the number of errors, and I have nice pareto
charts!

Randall Arnold

If this is just an Access query, and not in SQL Server, then...

In the query design view, open the property window for the query, and toggle
the Unique Records and Unique Values properties.

Steve Clark, Access MVP
FMS, Inc.

I have a SQL query that builds a list of shipping order errors. Each error
report has one Purchase Order Number (PON) but may have many errors
associated with that order. I aggregate errors based on Description and get
totals of each type. Date ranges are automatically filtered based on dates
on a user form. I can also filter based on Product Type and Area of
Responsibility off of this same user form. This part of my query works as
expected. I have a chart on a report that utilizes this query and it also
functions as desired.

However, I also need to determine the total number of unique purchase orders
and have that number show up in the query rows. I need this because I want
to be able to show, row by row, how many defects occurred out of how many
unique purchase orders (ie, opportunities for error).

I have tried numerous ways to get this to work and all have failed.
Microsoft Access does not seem to follow SQL convention 100% in that I should
be able to use a DISTINCT aggregate with the COUNT function but this produces
a syntax error. The method I expect to work is (simplified):

Select COUNT(DISTINCT PON)...

My actual current SQL is:

SELECT First([Order Setup Defects Query].[Defect Description]) AS [Defect
Description Field], Count([Order Setup Defects Query].[Defect Description])
AS DefectCounts, [Order Setup Defects Query].Responsibility
FROM [Order Setup Defects Query]
WHERE ((([Order Setup Defects Query].[Order Date]) Between
[Forms]![Main]![FromDate_combo] And [Forms]![Main]![ToDate_combo]) AND
(([Forms]![Main]![Product_combo])=[Order Setup Defects Query].[Product Name]
Or ([Forms]![Main]![Product_combo]) Is Null) AND
(([Forms]![Main]![Responsibility_combo])=[Order Setup Defects
Query].[Responsibility] Or ([Forms]![Main]![Responsibility_combo]) Is Null))
OR ((([Forms]![Main]![Product_combo])=[Order Setup Defects Query].[Product
Name] Or ([Forms]![Main]![Product_combo]) Is Null) AND
(([Forms]![Main]![Responsibility_combo])=[Order Setup Defects
Query].[Responsibility] Or ([Forms]![Main]![Responsibility_combo]) Is Null)
AND (([Forms]![Main]![FromDate_combo]) Is Null) AND
(([Forms]![Main]![ToDate_combo]) Is Null))
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup Defects
Query].[Defect ID], [Order Setup Defects Query].[Defect Description]
HAVING (((Count([Order Setup Defects Query].[Defect Description]))>=1))
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];


Anyone have any idea how I can produce the unique purchase order count?

Thanks,

Randall Arnold