Counting duplicate/non duplicate records (1 Viewer)

[bclmx]

Hey guys ,

I need to get a count of values from one column. The only problem is that each record appears more than once. Let’s call it Customer ID.
How would I tell access to get a count of unique Customer ID’s?
Here is an example.

111
111
111
222
333
444
444
444

The result I need is “4”. I only want to count one Customer ID from the ones that appear on multiple rows regardless how many duplicates there are.
Any help would be greatly appreciated.

Andy

 

[WayneRyan]

bclmx,

This will list them giving the total at the bottom

Select Distinct [Customer ID]
From   YourTable

This will list them giving the count for each

Select [Customer ID], Count(*)
From   YourTable
Group By [Customer ID]

Wayne

 

[bclmx]

Thanks Wayne.
There is one think I forgot to mention though.
I need to set this up as a criteria in a query where I would sum order amt.
Lets say I need to query 3 columns. Affiliate ID / Count of Customer ID /Sum of Order amt.
So I have a table:

Affiliate ID Customer ID Order amt

111 999 100

111 999 75

111 888 50

222 777 40

The results I am looking for should look like this:

Affiliate ID Count of Customer ID Sum of Order Amt

111 2 $225
222 1 40

How do I enter this as a criteria in the query where I am already working with totals and doing a sum of "Order amt"? I am not sure that what I am doing here is correct. By I think I would get the results I want if I was able to plug in your code into criteria row.

Andy

 

[WayneRyan]

bclmx,

Select [Affiliate ID], Count([Customer ID]), Sum([Order Amt])
From   YourTable
Group By [Affiliate ID]

Wayne

 

[Khalil Islamzada]

Hi,

Check out this youtube link, it will help you.

https://www.youtube.com/watch?v=CKUYHqZQhiw


Khalil