CODEWITHSUNDEEP
ms-accesscountvba
user3139793
user3139793

Reputation: 11

Count # of Times a Field Exists in Table/Query

I have a table with Project #, Components for Project, Status Color (could be red, green, yellow). The "Status Color" column is a Text field that will say either Red Green or Yellow.

So on the table, I can have, lets say 20 components for a project and the status for 10 of these items is Red (late), 5 Yellow (expected to be late), and 5 Green.

I have created a summary form in Access that has four fields after filtering for specific project

Total Components for Project: _ Total Red: Total Yellow: Total Green: _

I want the VBA script to look at the specific project selected and then do a count of how many red, green and yellow components I have. I have been trying endlessly and painfully to create a VBA that will look at the table and fill it out. The VBA to select/filter project was not hard but the counting is.

Any suggestions what the VBA script would be for this?

Upvotes: 1

Views: 137

Answers (3)

donPablo
donPablo

Reputation: 1959

This should do it. Change 'Iif' to 'Case When...Else...End' if need be.

    SELECT
        [Project #],
        SUM(Iif ([Status Color] = "Red", 1,0)) as CountRed,
        SUM(Iif ([Status Color] = "Yellow", 1,0)) as CountYellow,
        SUM(Iif ([Status Color] = "Green", 1,0)) as CountGreen,
        SUM(Iif ([Status Color] <> "Red" 
             and [Status Color] <> "Yellow" 
             and [Status Color] <> "Green", 1,0)) as CountOther
    FROM YourTable
    GROUP BY
        [Project #]

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8402

You're going to need a Transform (aka Pivot) to do this. I created a new table with a few fields:

  • Keys (I left this field blank, since you need one column with identical values to pivot off of)
  • ProjNum
  • Components
  • Color

Put them in a table called tblTemp. My SQL looks like this:

TRANSFORM Count(tblTemp.[ProjNum]) AS CountOfProjNum
SELECT tblTemp.[Keys], Count(tblTemp.[ProjNum]) AS [Total Of ProjNum]
FROM tblTemp
GROUP BY tblTemp.[Keys]
PIVOT tblTemp.[Color];

It worked as expected and gave me a total column (Total of ProjNum) and a column for Red, Green and Yellow. I'm sure you can edit the code accordingly and get your desired results.

Since it looks like you want only a specific ProjNum, you'll probably have to add a WHERE clause to this SQL statement to filter it out. The WHERE clause would come after the FROM and before the GROUP BY.

Upvotes: 0

HansUp
HansUp

Reputation: 97100

I don't understand the details of your summary form. However, I think a GROUP BY query would assemble the data you want.

SELECT
    [Project #],
    [Status Color],
    Count(*) AS how_many
FROM YourTable
GROUP BY
    [Project #],
    [Status Color];

If that query returns what you want after adjusting the table and field names, you could use it as the form's record source. And then you might not need any VBA for this ... just bind text boxes to the record source fields.

Upvotes: 1

Related Questions