Count Unique Values (1 Viewer)

[JMZ]

In a report Form Footer I placed a text box and =Count([Item]) in the control source, works like it should.

Now I'm trying to change it so I get a count of only the unique value, not counting the same item twice.

Any ideas?

jim

 

[plog]

You will need to do this in a new query. You would get DISTINCT values of whatever you want in your query and then do a DCOUNT into that query to get the total you want.

If you can post the table/query your Report is based on and then demonstrate what unique values you want I can help you more specifically.

 

[JMZ]

You will need to do this in a new query. You would get DISTINCT values of whatever you want in your query and then do a DCOUNT into that query to get the total you want.

If you can post the table/query your Report is based on and then demonstrate what unique values you want I can help you more specifically.

Sorry, can not post anything. But this is what I need, in my report footer I have a text box to count a character in the Detail Section, Exp: A, A, A, B, C, D, D and using Count() it gives a value of "7" but what I really need is a value of "4". Only count the three A's as 1 not 3, the D's as 1 not 2.

Jim

 

[GPGeorge]

You should create a query that counts distinct values and use the result of that query in your text box on the report footer.

BTW: Food for thought on using forums like this.

 

[plog]

I understood your general situation perfectly and provided the correct general answer. I cannot provide a specific answer without specific details.

 

[JMZ]

Thanks gentleman. I'll leave it at that, I've wasted enough of your time.

GPGeorge
Thank you for the, Food for thought Link, I'll read it right after I solve this simple problem I created.

 

[Pat Hartman]

@JMZ You've been a member for a couple of years but haven't posted much. We're happy to help but if you want people to code for you, you need to provide details. It is also preferable if you at least take a stab at following the directions given on how to solve this problem.

SQL Server and other RDBMS have a way to do this with a single query. With Access you need a second query or a sub query or a single query called by a dCount() as plog suggested