Count totals, yet do not count duplicates

  • Thread starter Thread starter raff98
  • Start date Start date
R

raff98

I have a very simple table. It contains company # and and type (Type is #1,2
or 3) I want a total count of "Type 1", yet in my database a company # can
appear more than once. Example:

Company #100 Type 1
Company #100 Type 1
Company #200 Type 1

I dont want duplicates in company #'s, just the total of "Type 1" companies.
When I run my query, it shows "3" "Type 1" companies, yet in reality there
is only "2" distinct "Type 1" companies. Also, how do I show "2" companies
in a report

Very new to access. Thanks!
 
I have a very simple table. It contains company # and and type (Type is #1,2
or 3) I want a total count of "Type 1", yet in my database a company # can
appear more than once. Example:

Company #100 Type 1
Company #100 Type 1
Company #200 Type 1

I dont want duplicates in company #'s, just the total of "Type 1" companies.
When I run my query, it shows "3" "Type 1" companies, yet in reality there
is only "2" distinct "Type 1" companies.
Click to expand...

Unfortunately Access doesn't support "COUNT DISTINCT" queries - you'll need to
build your own using a Subquery:

SELECT [Type], Count(*)
FROM (SELECT DISTINCT [Company], [Type] FROM yourtable);
Also, how do I show "2" companies
in a report
Click to expand...

Create a Query with a criterion of 2 (or "Type 2" depending on what's in your
table) on the Type field, and base he Report on this query. If you want to see
each company only once, set the Query's "Unique Values" property in the
query's Properties.
 
Back
Top