How to perform Countif horizontally in a Report? (1 Viewer)

[dudezzz]

Hi,

I have a report as described in my attachment.
The record source of this Report is a Query. There are 6 columns (col1 through col6) which are calculated values. The values of Col1 through Col6 vary between 0, 1 or N/A.

I want to calculate the Percentage for each ID with the following calculation:

Percentage = Total Number of 1s in Col1 through Col6 / (Total Number of 1s in Col1 through Col6 + Total Number of 0s in Col1 through Col6)

What is the easiest way to accomplish this? I have 15 reports like this that I need to calculate percentage for and I was trying to accomplish this via VBA but I am getting no where.

Can anyone show me how this can be accomplished please?

 

[Galaxiom]

Use a Domain Expression
DCount("fieldname", "queryname", "queryname.fieldname = 1")

This will return the count of 1s in a field.

With 15 reports all needing the same calculation and counting across columns there is a fair chance your database structure is not optimum. Usually returning information is easy if the structure is right.

Tell us more about your table structure. What is being recorded and what are the relationships between tables?

 

[dudezzz]

Thank you for your response.

The Dcount function gives me the count Vertically (column wise). I am looking for the count Horizontally (row wise).

I want to count the total number of 1s across Col1 through Col6.

 

[Galaxiom]

Counting across a record definitely indicates the database is incorrectly structured.

As a analogy image a fruit database. Say you have records with Supplier name, apples, oranges, peaches with quantities under each of the fruit names. Easy to get the information about a particular fruit. But now you want to count all fruit from a supplier, regardless of the fruit type. You need to count across fields.

Instead there should have been a FruitSupply table with a fields for SupplierID, FruitID and Quantity.

If you wanted to know how many apples from all suppliers you would sum Quantity where FruitID correseponded to Apples. To count all fruit from a supplier sum the Quantity where the SupplierID corresponds with the one you want to query.

You need to restructure your database in a similar way.

 

[HAMMAMABUARQOUB]

can't a cross-tab query do the job?!

 

[Eve-Line]

Use a Domain Expression
DCount("fieldname", "queryname", "queryname.fieldname = 1")

This will return the count of 1s in a field.

Could I jump on the bandwagon please... I'm looking for a formula that should be similar to the above (I think), but... I have a table with several fields, one of wich is the customer number. One customer can be listed up to 400 times if they have one billing address and 399 delivery addresses.... I would like to count the number of occurence of each customer but as I have roughly 30,000 records I don't want to have to put each record in the formula... In excel my countiff would have looked like : =countif(A:A;A2).

In access, in de Dcount you put "queryname.fieldname = 1" : how can I change the value "1" to make it count each different value... and also, where do I put my Dcount formula?

Thanks for the help!
Eve-Line