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 |
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."
Hey Shaun, awesome. Your suggestion works,
we are making progress. But not yet solved.
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.