Compare with table coulumn count

[voro]

HI All



I have a small problem.

I have a union query that shows me types of errors and the number of them.
I laso have a table called Main which holds all the data.

Now lets say count of errors is 500 in the union query and i wish to present it as a % of total rows in the Main table 8000 so 500/8000 = 6%
Main table and union query are not anyhow joined, i could via Emp ID but i am not sure if that will help.

I made a query that shows all the columns from union query and only emp id column from Main as count. So now every line in union query has 8000 next to it representing count of all rows from Main table.

I tried but i cant show 500(errors) as a % of cell value (count of emp id - 8000) in pivot among things.

Additionally if it was possible to guide me how to show the break down of each error's constitution to the overall 6% it would just be fantastic.

Hope i am clear and if now plz let me know.

Thanks
Regards
Peter

[rpeare]

There are two ways you can do this

The first way is to make a query that simply has one field, and that field is a count of records in your 'main' table.

Add that table to your query that has the 500 errors and add the total error count field to your query.

do not link the tables

This is called a cartesian query, basically it creates a value for every possible combination of values from tablea and tableb, basically you multiply the number of items in each table together and you'll get your final record count.

Since you are after one value (total errors) this shouldn't cause you any problems.

Then in your third column you divide your error count for that line by the total error count to get the percentage.
---

The second way, which I would not recommend, is to use a dcount function in your existing query

dcount("*","[Error Table]")

to get the total records

domain functions are incredibly resource heavy and should not be used in queries, ever but good advice never stopped anyone from trying to do it anyway!

[voro]

Thanks alot for your support. Much appreciated !