Looping and Updating

[Knicks]

I have a table called tblRDH2005. It has 1 field for region and then 41 fields used for a questionaire. The 41 fields are L1...L41 and their options can be only 1,2,3,4,5 (each number representing a severity from strongly disagree to strongly agree with 3 being neutral).

What I would like to do is run some looping and addition. Say loop through all fields L1 thru L41 where region is like Washington and severity = "5", each instance of "5" I would like to add "1" for a running count to a field called Washington5. I would need to do this for (4)(3)(2)(1) also for Washington. And the same for Oregon and Idaho. I don't know if I would need to use a temporary variable instead of an actual field. And I am not sure how to initially open and update a recordset in VBA.

Any help would be appreciated.

 

[mikey69]

This is a job for a Visual Basic module. You need to create a temporary table with 6 fields, RegionId, Severity1 thru Severity5.

Then you use code to create a 5 element array, read your base table, sorted by region. Loop through the fields 1 thru 43 in each record, add 1 to the array element corresponding to the severity value. On change of region you write a record to the temporary table, using the totals in the array, reset the array values to zero.

These sorts of things are always more quickly and reliably done using VB code, I think. You'll find the effort of climbing the learning curve is really worth it.

Access makes all things possible. It even makes them intelligible

 

[snerting]

You should consider normalizing the table tblRDH2005 so adding a question doesn't require the table definition to change.

Further, if you want to aggregate records like you describe, why not use SQL? It's what it's there for.

sql = "SELECT count(*) AS cnt FROM tblRDH2005 WHERE L" & question_number & "=" & severity_number"


Anyway, to answer your real question:
Dim db As Database
Set db = DBEngine(0)(0)

Dim my_recordset As DAO.Recordset
Set my_recordset = db.OpenRecordset(sql)

Dim tmp_field As Field

While Not my_recordset.EOF
For each tmp_field In my_recordset.Fields
(...)
Next tmp_field
my_recordset.MoveNext
Wend


General recordset functions you might need:

my_recordset.Edit ' Sets the recordset in edit mode
my_recordset.Addnew ' Adds a new empty row in the recordset
my_recordset.Update ' Writes changes done through Edit/Addnew to the database
my_recordset.Close ' Closes the open recordset
my_recordset.MoveLast ' moves to the last record in the recordset. Beware, it goes through all records, so it's slow on large recordsets
my_recordset.MoveFirst ' moves to the first record
my_recordset.RecordCount ' counts the returned entries. You have to call MoveLast or MoveNext until end of records before RecordCount will return a true value.


Anyway, I really think you should consider normalizing the whole structure.

 

[MajP]

Like Snerting said you have a very bad design, it is referred to as not normalized. Data bases are designed to work with like information in rows not in columns. It may be to late to normalize your application, but this will normalize your output so that you can then use the power of a database to get answers.

This code will normalize your table. Changing your data to

Region Question Answer
Alabama question1 5
Alabama question2 1
.
Alabama question41 3
Washington question1 5
.
.
Washington question41 1

Why? Because once you have it in this normalized format you can do a lot of query manipulation, using aggregate queries to get all the answers you want. You can then make reports that group on regions, questions, and answer values.

Make a new table
tblNormal
autoQuestionID auto number
strRegionName text
strQuestionName text
intAnswerValue integer

My old table in your format
tblData
autoQuestionsID autoNumber
strRegionName (your field one)
field1 to field1 (your fields)

Public Sub subNormalizeQuestions()
Dim rsUnNormal As DAO.Recordset
Dim rsNormal As DAO.Recordset
Dim myField As DAO.Field
Dim strRegion As String
Dim intCount As Integer
Set rsUnNormal = CurrentDb.OpenRecordset("tblData")
Set rsNormal = CurrentDb.OpenRecordset("tblNormal", dbOpenDynaset)
Do While Not rsUnNormal.EOF
  strRegion = rsUnNormal.Fields("strRegionName")
  For intCount = 2 To (rsUnNormal.Fields.count - 1)
    rsNormal.AddNew
    rsNormal.Fields("strRegionName") = strRegion
    rsNormal.Fields("strQuestionName") = rsUnNormal.Fields(intCount).Name
    rsNormal.Fields("intAnswerValue") = rsUnNormal.Fields(intCount).value
    rsNormal.Update
  Next intCount
  rsUnNormal.MoveNext
Loop
End Sub

 

[mikey69]

You guys are quite right aboout normalizing. If Knicks decides to take that pain, then I'd suggest that he migrates to a table with nothing but long integers in the fields:
RegionID
QuestionID
ResponseValue

Use RegionID/QuestionID as Primary key and put RegionName and QuestionText in related tables. That will give efficient processing when searching



Access makes all things possible. It even makes them intelligible