Query with Unique Criterion

  • Thread starter Thread starter Jason Morin
  • Start date Start date
J

Jason Morin

Hello. I have a table of invoices with multiple products
shipped on each invoice. These products span a bunch of
categories. I need a query that will pull in all invoices
that only have *1* category. Thx! Example:

Table
Invoice Product Category
001 Wire Electronic
001 Battery Electronic
002 Table Furniture
002 Wire Electronic
002 Dryer Appliance
009 T-shirt Apparel

Desired Outcome:
Invoice Product Category
001 Wire Electronic
001 Battery Electronic
009 T-shirt Apparel
 
You might do this using three queries:

1. One query (say, named "Query1") that finds distinct combinations of
Invoice and Category. The SQL might look something like this:

SELECT DISTINCT
[Your Table].[Invoice],
[Your Table].[Category]
FROM
[Your Table

2. A second query (say, named "Query2") based on the first one that finds
Invoices that have exactly one distinct Category. The SQL might look
something like this:

SELECT
[Query1].[Invoice]
FROM
[Query1]
GROUP BY
[Query1].[Invoice]
HAVING
Count(*) = 1

3. A third query based on the second that retrieves the other information
for the Invoices that have exactly one distinct Category. The SQL might
look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
INNER JOIN
[Query2]
ON
[Your Table].[Invoice] = [Query2].[Invoice]
 
Dear Jason:

Step 1: Before counting the number of Catagories for each Invoice,
drop all duplications.

SELECT DISTINCT Invoice, Category FROM Table

Step 2: Filter to Invoices with one Category:

SELECT Invoice
FROM (SELECT DISTINCT Invoice, Category FROM Table) X
GROUP BY Invoice
HAVING COUNT(*) = 1

Step 3: Limit to these invoices:

SELECT Invoice, Product, Category
FROM Table
WHERE Invoice IN (SELECT Invoice
FROM (SELECT DISTINCT Invoice, Category FROM Table) X
GROUP BY Invoice
HAVING COUNT(*) = 1)

Substitute the actual name of your table where it says Table.

Any luck?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks guys. That worked! Appreciate it.
Jason
-----Original Message-----
You might do this using three queries:

1. One query (say, named "Query1") that finds distinct combinations of
Invoice and Category. The SQL might look something like this:

SELECT DISTINCT
[Your Table].[Invoice],
[Your Table].[Category]
FROM
[Your Table

2. A second query (say, named "Query2") based on the first one that finds
Invoices that have exactly one distinct Category. The SQL might look
something like this:

SELECT
[Query1].[Invoice]
FROM
[Query1]
GROUP BY
[Query1].[Invoice]
HAVING
Count(*) = 1

3. A third query based on the second that retrieves the other information
for the Invoices that have exactly one distinct Category. The SQL might
look something like this:

SELECT
[Your Table].*
FROM
[Your Table]
INNER JOIN
[Query2]
ON
[Your Table].[Invoice] = [Query2].[Invoice]


Hello. I have a table of invoices with multiple products
shipped on each invoice. These products span a bunch of
categories. I need a query that will pull in all invoices
that only have *1* category. Thx! Example:

Table
Invoice Product Category
001 Wire Electronic
001 Battery Electronic
002 Table Furniture
002 Wire Electronic
002 Dryer Appliance
009 T-shirt Apparel

Desired Outcome:
Invoice Product Category
001 Wire Electronic
001 Battery Electronic
009 T-shirt Apparel
Click to expand...


.
Click to expand...
 
Back
Top