Solved How do you Count how many times something appears in a column for multiple columns (1 Viewer)

[jdlewin1]

Hi,

I have some data in a table with gives a value of "Y", "C" or blank for ~20 different columns.

How do i get the query to count how many times "Y" appears in each column separately.

I can get it to do it for one column by adding the field to the query twice and adding a Sum Total with Group By and Criteria set to "Y". If i repeat this for subsequent columns it doesn't do the count independently of the first etc so give the wrong number of "Y" for the rest of the columns.

Thanks,

 

[CJ_London]

For each column you need to put something like

sum(-columnname=‘Y’)

 

[Uncle Gizmo]

Because you have both Y and C in every column, it sounds like your fundamental design is incorrect.

It would be prudent to inspect your design and reasons for it.

Posting your database is not usually a good idea because you probably have confidential information. Also s no one here wants to spend ages trawling through a massive database!

The best approach is to create a sample database with the minimum amount of tables and forms which allows people to see your issue. Add some dummy data so that you are not revealing confidential stuff, and the members here can experiment in creating queries that can solve your problem.

 

[arnelgp]

i would create a UDF in a Module, to count those Fields/Columns:

Public Function CountValueInColumns(ByVal theValueToCount As Variant, _
            ParamArray theColumns() As Variant) As Integer
''''''''''
' arnelgp
'
' parameters:
'
' theValueToCount        = string, number or Null to count
' theColumns             = the columnNames to count, eg:
'
' To Count how many "Y" value in the fields: Field1, Field2, Field3:
'
' =CountColumns("Y",[Field1], [Field2], [Field3])
'
Dim var As Variant, iCount As Integer
For Each var In theColumns
    If IsNull(theValueToCount) Then
        If IsNull(var) Then
            iCount = iCount + 1
        End If
    Else
        If var = theValueToCount Then
            iCount = iCount + 1
        End If
    End If
Next var
CountValueInColumns = iCount
End Function

You can create a Query to use the function:

Select "Y" As [The Value], CountValueInColumns("Y",[Field1], [Field2], [Field3], [Field4], [Field5], ...., [Field20]) As [Count Of Y] From yourTable;

 

[jdlewin1]

thanks for the response, i put your code into the module and set the sql for the query as:

SELECT "Y" AS [The Value], CountValueInColumns("Y",[Mois], [DGA], [BDV], [Acid], [PCB]) AS [Count Of Y]
FROM Tracker_List;

that provided a long list with Ys in the left column and numbers (presumably counts in the right columns see below:

What i am looking for is the below....table on the left is the main table with columns with Y, C and blanks in them, i want to essentially count the Y in each separate column and display the count number as shown in table 2 (happy if this table is transposed so Y as the column and the column headers as the row).

Any thoughts?

Thanks,

 

[cheekybuddha]

SELECT
  SUM(IIf(Mois = 'Y', 1, 0)) AS Mois,
  SUM(IIf(DGA = 'Y', 1, 0)) AS DGA,
  SUM(IIf(BDV = 'Y', 1, 0)) AS BDV,
  SUM(IIf(Acid = 'Y', 1, 0)) AS Acid,
  SUM(IIf(PCB = 'Y', 1, 0)) AS PCB,
  SUM(IIf(FFA = 'Y', 1, 0)) AS FFA,
  SUM(IIf(Fns = 'Y', 1, 0)) AS Fns
FROM YourTable;

Really, you should address your table structure, as has been suggested.

You have a spreadsheet style table which is a nightmare for proper data analysis and leads to horrible queries like the one above.

hth,

d

 

[Minty]

Your data isn't stored correctly, which is why you are jumping through hoops to do a count.

Edit - Cheeky beat me to it.

 

[cheekybuddha]

Using CJ_London's variation:

SELECT
  SUM(-(Mois = 'Y')) AS Mois,
  SUM(-(DGA = 'Y')) AS DGA,
  SUM(-(BDV = 'Y')) AS BDV,
  SUM(-(Acid = 'Y')) AS Acid,
  SUM(-(PCB = 'Y')) AS PCB,
  SUM(-(FFA = 'Y')) AS FFA,
  SUM(-(Fns = 'Y')) AS Fns
FROM YourTable;

 

[arnelgp]

i though you will Count crosswise?
see query1 and the final query, query2

 

[jdlewin1]

thanks you, both variations worked.

i am aware that the data isnt the best but its a small part of a large live system. The particular query that the data is being counted from is used to display which analysis needs to be completed for over 20 different tests (the columns) and can be for as much as 1000 samples (the rows). The data is live and always changing its status from Y to C as things get complete.

We are building a new web based platform to replace the access system but for now we have what we have and i just need to add this small feature to what otherwise is a large system!

Thanks all for your help.

 

[MajP]

Normalize the data using a query. Then it is extremely simple to get the counts and anything else

SELECT Mois, "Mois" AS Type
FROM Table1
UNION All
SELECT DGA, "DGA" AS Type
FROM Table1
UNION All
SELECT BDV, "BDV" AS Type
FROM Table1
UNION All
SELECT Acid, "Acid" AS Type
FROM Table1
UNION All
SELECT PCB, "PCB" AS Type
FROM Table1
UNION All
SELECT FFA, "FFA" AS Type
FROM Table1
UNION All
SELECT Fns, "Fns" AS Type
FROM Table1

 

[Pat Hartman]

We are building a new web based platform to replace the access system but for now we have what we have and i just need to add this small feature to what otherwise is a large system!

"Access" isn't the reason for your poor design. It is the original developers lack of knowledge on how to create a relational database. I hope you don't continue with this unnormalized schema for the new application.

The new app will not be better than the Access app unless its design is better.