asked on Count the number of times a value appears in a column using Ms Access
Hi experts,
How can we accomplish the following in Access.
Situation:
We have a column with an internal codes to identify trees. This internal code is unique for each tree.
But in cases where that tree is divided in 2 or 3 pieces, that internal code gets repeated in that column.
Now for reporting it is needed that we know how many trees there are and how many pieces.
How can we count each unique internal tree code?
I would like to see a column that shows for each record one of the following values: 1 or 0.5 or 0.33.
The values are determined by the counting of the internal tree code.
So if the counting result is
1, show value"1"
2, show value "0.5"
3, show value "0.33"
Am looking forward to a solution.
How can we accomplish the following in Access.
Situation:
We have a column with an internal codes to identify trees. This internal code is unique for each tree.
But in cases where that tree is divided in 2 or 3 pieces, that internal code gets repeated in that column.
Now for reporting it is needed that we know how many trees there are and how many pieces.
How can we count each unique internal tree code?
I would like to see a column that shows for each record one of the following values: 1 or 0.5 or 0.33.
The values are determined by the counting of the internal tree code.
So if the counting result is
1, show value"1"
2, show value "0.5"
3, show value "0.33"
Am looking forward to a solution.
Microsoft Access
Last Comment
Gilberto Sanches
SELECT Count(Table1.tree) AS CountOftree, 10/([table1].[tree code]*10) AS Expr1
FROM Table1
GROUP BY 10/([table1].[tree code]*10);
Assuming Simple Table
Table1
| tree code | tree |
|---|---|
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 2 | two again |
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Alright, the field "BoomCode" is the field with the unique internal tree code.
With your example, you can do a grouping on the BoomCode, and divide 1 by the count of occurrences.
ASKER
Hey Shaun, yeah, I did the grouping of BoomCodes before I posted the question but was stuck there.
How do I do this " divide 1 by the count of occurrences."
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
Hey Shaun, awesome. Your suggestion works,
we are making progress. But not yet solved.
ASKER
In the 1st field of the Query Designer I have the boomcode grouped, and in the second field I have code 1/COUNT(*). This gives the 3 values as wanted. But in that query it only shows the value per unique record.
Solution: Create another query with tables "original data" & the previous query. Match those 2 tables based field on BoomCode. Viola.
Thank you very much Shaun.
However, you may be able to use the replace function to "remove" the code from the string, then compare the length of the unaltered string against the length of the altered string to determine occurrences.
If your string where "alphabet", and you wanted to count the "a" letters in the string, you could do: Length("alphabet") - Length(Replace("alphabet",
For codes that are multiple letters, you would just need to divide the result of the above with the length of your code.