Loop through many fields in a table to create a new field (1 Viewer)

[melfaram]

I would like to thank you for your help in advance. I have a table called sheet1. It has a field for ID (PID), and many fields for scores assigned by evaluators (text not number) The fields have the same first part (Rev) and a numbers appended to them : Rev1, Rev2, etc. I want to loop through all the fields that have the scores and change them from string to number and then use them to create a new field called total that has the total numbers of values< 4 across the fields. The ID field should not be in the loop.. I have never used loops before. Therefore, I will be very grateful if you let me know 1) how to exclude the ID field from the loop and include the fields I want only and 2) what should I do to end up with the results I want.
Thanks again

What I have
Table: Sheet1
Fields: PID, Rev1, Rev2, Rev3, Rev4 ---------------- Rev30

Current table:

PID	Rev1	Rev2	Rev3	Rev4	Rev30
1	1	2	3	5	----
2	4	4	5	5	----
3	4	5	6	5	---

What I want

PID	Rev1	Rev2	Rev3	Rev4	Rev30	Total (values < 4)
1	1	2	3	5	---	0
2	4	4	5	5	---	2
3	4	4	4	5	----	3

 

[arnelgp]

what you are doing is not Normal to database.

you should have a "simple" table:

PID (number)
Rev (number)

everytime there is revision, you add Record with same PID but increasing Rev number.

 

[Melfaramawi]

I am sorry if my question was not clear. I do not create tables or add new records to them. I get excel sheets and I import them in access. I am looking for a Vba code to loop through the records across the fields.

 

[Uncle Gizmo]

I am sorry if my question was not clear.

Your question is very clear! The difficulty you are facing is because of the arrangement of the data. You need to transpose the data into two columns as suggested by ArnelGP.

The why and the how are explained in my blog here:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

I also have a tool to simplify this process which is available for free to Access World Forum members.

If you would like a copy of the tool then send me a private message within access world forums messaging system and I will send you a coupon code to release a free copy.

 

[June7]

A VBA procedure could do this but as already advised, a normalized structure would likely not even need VBA.

Your desired output is not clear to me. Why does PID 1 have 0 Total when there are 3 values <4 and neither of the other records has values <4 so why do they show 2 and 3?

 

[Melfaramawi]

A VBA procedure could do this but as already advised, a normalized structure would likely not even need VBA.

Your desired output is not clear to me. Why does PID 1 have 0 Total when there are 3 values <4 and neither of the other records has values <4 so why do they show 2 and 3?

Thank you for your response. The numbers in total are arbitrary. I just put any numbers. Because I have many fields I did not put all of them. But you are right the total for first row should have at least 3. Ok, I know how to transpose (union all) but is there a way to transpose using loops. The idea is the number of fields will keep changing and not fixed.

 

[June7]

Here is a very simple function:

Function GetTotal(intPID As Integer) As Integer
Dim rs As DAO.Recordset, intF As Integer, x As Integer, intC As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Sheet1 WHERE PID=" & intPID)
intF = rs.Fields.count - 1
For x = 1 To intF
    If rs(x) < 4 Then intC = intC + 1
Next
GetTotal = intC
End Function

It assumes: 1) PID field is the first field in table design and 2) all other fields are of the RevX naming and 3) every field has a number value

Call the function from query:
SELECT PID, GetTotal([PID]) AS Cnt FROM Sheet1;

Or call from a textbox on form or report.

 

[melfaram]

Here is a very simple function:

Function GetTotal(intPID As Integer) As Integer
Dim rs As DAO.Recordset, intF As Integer, x As Integer, intC As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Sheet1 WHERE PID=" & intPID)
intF = rs.Fields.count - 1
For x = 1 To intF
    If rs(x) < 4 Then intC = intC + 1
Next
GetTotal = intC
End Function

It assumes: 1) PID field is the first field in table design and 2) all other fields are of the RevX naming and 3) every field has a number value

Call the function from query:
SELECT PID, GetTotal([PID]) AS Cnt FROM Sheet1;

Or call from a textbox on form or report.

Thank you very much June7. It worked well. I really appreciate it.

 

[June7]

Might still consider procedure to transpose data when received. If your dataset gets very large, my VBA solution might perform slowly. SQL aggregation is usually most efficient approach.

 

[melfaram]

Might still consider procedure to transpose data when received. If your dataset gets very large, my VBA solution might perform slowly. SQL aggregation is usually most efficient approach.

Therefore, it will be great if you can post a loop that can be used with union all to transpose datasets such as the one you wrote the previous loop for, please.

 

[Pat Hartman]

What we are suggesting is that INSTEAD of importing the data as is, you normalize it on import. That solves your analysis problem, now and later. June's solution is specific to your exact question. If you want to do an average, you need to create a different function. Converting the data to a proper design allows you to create simple queries rather than having to write code for each new request.

@Uncle Gizmo is offering a free tool to do this for you. Perhaps you should look at it.

 

[melfaram]

What we are suggesting is that INSTEAD of importing the data as is, you normalize it on import. That solves your analysis problem, now and later. June's solution is specific to your exact question. If you want to do an average, you need to create a different function. Converting the data to a proper design allows you to create simple queries rather than having to write code for each new request.

@Uncle Gizmo is offering a free tool to do this for you. Perhaps you should look at it.

Ok, got it. Thanks