VBA to count the number of entry in a row and update in count column

[swindel]

Hello,

I am new in this forum and would like to understand the VBA macro, Just wanna know how can I count the number of entries in a row per criteria. I have the Unique ID, and have a couple of data and will insert the count on Count Y and Count X.

ID	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6	Count Y	Count X
1​	X	Y	X	X	X		1​	4​
2​	X	X		X			0​	3​
3​		Y	Y	Y			3​	0​
4​	Y	Y	Y	X		X	2​	2​
5​							0​	0​
6​	X		X	Y	Y		1​	2​
7​		Y	Y	Y	X	X	3​	2​

 

[Joe4]

Welcome to the Board!

One of the hardest things for people who are familiar with Excel to understand is that Access is a totally different beast altogether, and is not an "extension" of Excel, and does not work the same way. Access is a relational database program. Relational database programs work very welll when they are designed properly, that is their table design follows the Rules of Normalization. One of the rules says that you should not have repeating fields (columns) that do the same thing. you should have multiple records (rows), not fields (columns). With that sort of design, if you had more data points, you would not need to create any new fields (columns), just more records.

So, with a Normalized data table, instead of having 5 fields for ID 1, you would have 5 records. The beginning of the data table you posted would look like this, if normalized:

ID	Data Number	Value
1	1	X
1	2	Y
1	3	X
1	4	X
1	5	X
2	1	X
2	2	X
…	…	…

Then, it is very easy to get the counts you require using a simple Totals (Aggregate) Query without having to do any special programming.

Otherwise. you would either need to do a query with a bunch of IIF statements added together, or VBA code that loops through each record.

The issue with the query with a bunch of IIF statements is if you have to add new fields, you need to amend these formulas.
The issue with the VBA approach is that you either need to have pre-exiting empty fields to write the results to, or return the results to a temporary table (both of which destroys the dynamic nature of the data, as you would need to run this code EVERY time the data changes).

Here is a good article on the Rules of Normalization: Database normalization description - Office

 

[swindel]

Thank you Joe, however the problem here is that i have a thousands unique ID and a hundred fields. I tried to do it in query however there is a limitation there.

 

[Joe4]

Thank you Joe, however the problem here is that i have a thousands unique ID and a hundred fields. I tried to do it in query however there is a limitation there.

Yes, that is a "big red flag" that you are going about it in the wrong manner. When you have design issues, it makes (what should be) very simple tasks very difficult to do.

If it were me, I would probably take the following approach:
Redesign the data table to make it normalized. I would probably write an Excel macro to automatically fix the structure of data (turning what you have into the structure I presented), then import that into my new table, where you can then do what you want easily.

Speaking from experience, the problem with trying to work around the current de-normalized structure in Access is that it will be absolutely maddening (you would be better off to do it in Excel)!
Even if you create a workaround for this particular task (which will be inefficent and non-dynamic), you will probably run into more issues if you try to do anything else with the data.
As I said, de-normalized data is VERY hard to work with in Access.

With a well-designed database, you should seldom have to create new fields in existing tables. So, if you had more data points to add, under the current structure you have, you would need to add more data fields (columns) to your table, as opposed to the normalized approach, which would just be adding more data rows and not having to change any table or query structure, or programming.

In a nutshell, in Access, it is always more desirable to add more data records (efficient, easy, cheap) than it is to add more fields (expensive, more complex, need to update other aspects of database).

 

[Micron]

Agree 100% with what Joe is saying. To embellish that a bit, suppose I write a procedure that does what you want. What if you want to add a condition or maybe a new field; are you going to modify the procedure yourself so that it encompasses this new requirement? If you could, I suspect you could write it from scratch right now. I suspect that you'd be back here asking for a modification to what is really a hack. Or say you want to replicate the procedure for another purpose - same questions. You should try to get off of this merry-go-round that you're on.

Having said that, Google "ms access loop over recordset fields" and find some code that you can adapt to your situation?

 

[Zot]

Here is a sample of simple macro that is easy to understand and modify to your need

Sub CountXY()

Dim colX As String, colY As String
Dim cell_ID As Range, rngID As Range
Dim cell_Data As Range, rngData As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.ActiveSheet
Set rngID = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp))

colX = "I"
colY = "H"

For Each cell_ID In rngID
    Set rngData = ws.Range("B" & cell_ID.Row, "G" & cell_ID.Row)
    For Each cell_Data In rngData
        Select Case cell_Data
            Case "X"
                ws.Cells(cell_Data.Row, colX) = ws.Cells(cell_Data.Row, colX) + 1
            Case "Y"
                ws.Cells(cell_Data.Row, colY) = ws.Cells(cell_Data.Row, colY) + 1
        End Select
    Next
Next

End Sub

 

[Micron]

@Zot, impressive - but this is the Access forum. I can't see that working in Access.
Then again, the OP might be the one who posted in the wrong place.

 

[Zot]

Sorry my mistake. I did not read ?

 

[Micron]

You're not the first, and I count myself in that group.

 

[Joe4]

@Zot, impressive - but this is the Access forum. I can't see that working in Access.
Then again, the OP might be the one who posted in the wrong place.

No, it is in the correct forum.

Actually, Zot was the first one who replied earlier in the day, but it was an Excel solution and the OP alerted them to it. I deleted those posts so that the question would still appear as a new unanswered question (you can see the remnants of those deletions under the original question post).

Zot, if you are using the "Unanswered threads" link to locate new unanswered qustions, be sure to notice that these include questions across ALL questions forums.
Note that it lists which forum they are in right under the question (many people miss that), i.e.