What formulas for UniqCount in query for Ms Access

[sseiharath]

Hi all,

Please could you tell me! there are some uniquecount/countUnique formulas something like that can be use in query to count unique text?
because I have a table that contain Unit Name (Text) some name are repeated, but I want to count only one time for that name in query, what formula can I use for that?
Thanks.

 

[The_Doc_Man]

Usually you accomplish this using a GROUP BY verb on the text field (you called it Unit Name) and then have a COUNT somewhere in the query.

Using the query design grid, you could make this happen by building a select query but then clicking the icon in the ribbon that looks like Greek letter Sigma (the calculus SUMMATION symbol). You can do a COUNT of * (asterisk) but need to do the GROUP BY. Then when you open that query you see the unique strings and the counts for each. None of the rows can be zero because Access produces "sparse" result sets. That is, if something wasn't in the input, it isn't in the output either. Also, a GROUP BY usually implies an ORDER BY on the same field.

You will probably notice almost immediately that the similar fields will still show up as being different.

One last comment: Don't name your field Unit Name (as two words with a space in between). Name it as UnitName perhaps. The problems of having embedded spaces are further compounded when one of the words is a reserved word ("Name").

 

[sseiharath]

Hi The Doc Man,

I did the same you told me in the field [UnitName], first I group it by press sigma letter and in below I count by using the formula Count(*), but still appear text not number and make another fields repeat value or text, what I should do now?
Thanks.

 

[sseiharath]

Hi all,

sorry! may be I was wrong on explanation, I have continuing relationship One to Many between 3 tables that relation fields are [ServiceName], [UnitName] and [StaffName].
I would like to know in 1 Service how many unit?, but I would'n like to know how many staff, but when I made calculation in query Group by [ServiceName] and put count(*) formula in field [UnitName] the results look like count number of staff that I don't want its,

Please could you tell me! there are exist some ways or some formulas that can be use in query to count in field [UnitName] the result will be number of Unit not number of Staff?
Thanks toward hope some one help me.
Please look attach file.

 

[JHB]

First pick out the DISTINCT values, then count them, see attached database.