Show only unique records in duplicated field

[Matthew]

Hello,

I need to limit repeated (but not duplicated) records in a query. Here's my
structure:

tblCompanies
*CompanyID
CompanyName

tblCategories
*CategoryID
CategoryName

tblCategoryAssignment
*CatAssignmentID
CompanyID
CategoryID

This structure allows a company to be assigned more than one category.

I want to be able run a query searching for companies that belong several
categories, i.e. show me all companies that have category assignments of
either software or nonprofit. Something like TechSoup would come up twice.
I'm interested in viewing the company names, not necessarily their
CategoryAssignmentIDs. Those ID's will be unique, but company names will be
duplicated. How do I get the query results to show only uniqe company
names?

Many thanks!!

Matthew

 

[SteveM]

A Totals query - click View/Totals
Company Name should be 'Group By', AssignmentID's should be 'Where'...

Steve

 

[John W. Vinson]

I want to be able run a query searching for companies that belong several
categories, i.e. show me all companies that have category assignments of
either software or nonprofit. Something like TechSoup would come up twice.
I'm interested in viewing the company names, not necessarily their
CategoryAssignmentIDs. Those ID's will be unique, but company names will be
duplicated. How do I get the query results to show only uniqe company
names?

A couple of ways: either uncheck the Show checkbox in the query grid under the
category fields (or, equivalently, don't include the category field in the
SELECT clause of the query, only in the WHERE clause); or, use a Subquery:

SELECT tblCompanies.CompanyName
FROM tblCompanies
WHERE CompanyID In
(SELECT CompanyID FROM tblCategoryAssignment INNER JOIN tblCategories
ON tblCategoryAssignment.CategoryID = tblCategories.CategoryID
WHERE tblCategories.CategoryName = [Enter category:])

John W. Vinson [MVP]

 

[Matthew]

YES!!

THANK YOU!!

I've never fully grasped how/when to use Totals!

Matthew

 

[Matthew]

Hmm, I'm intrigued.

If I uncheck the Show checkbox in the category fields, it still shows
company names twice.

But the Subquery works!! Subqueries are new to me!!

Thank you!!

Matthew

I want to be able run a query searching for companies that belong several
categories, i.e. show me all companies that have category assignments of
either software or nonprofit. Something like TechSoup would come up
twice.
I'm interested in viewing the company names, not necessarily their
CategoryAssignmentIDs. Those ID's will be unique, but company names will
be
duplicated. How do I get the query results to show only uniqe company
names?

A couple of ways: either uncheck the Show checkbox in the query grid under
the
category fields (or, equivalently, don't include the category field in the
SELECT clause of the query, only in the WHERE clause); or, use a
Subquery:

SELECT tblCompanies.CompanyName
FROM tblCompanies
WHERE CompanyID In
(SELECT CompanyID FROM tblCategoryAssignment INNER JOIN tblCategories
ON tblCategoryAssignment.CategoryID = tblCategories.CategoryID
WHERE tblCategories.CategoryName = [Enter category:])

John W. Vinson [MVP]

 

[John W. Vinson]

Hmm, I'm intrigued.

If I uncheck the Show checkbox in the category fields, it still shows
company names twice.

sorry... should have added that you *also* need to set the Query's Unique
Values property to Yes.

But the Subquery works!! Subqueries are new to me!!

And pretty useful in many contexts. They're not always ideally efficient
though, the query optimizer can't always handle them well.

John W. Vinson [MVP]