I have a table with 36 fields that may contain a value from a 20 item code list or be null. I need a report that tells how many times each code appears in all fields on a monthly basis. Any ideas on how I should go about this?
If nobody knows this, then is there a way I can copy many fields from one table into a new table with just one field so I can run a report grouping on that field?
Dim IsDataEnabled as Boolean
Dim ctl as control
Dim icount as Integer
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case Is = acTextBox ' 109
IsDataEnabled = True
Case Is = acListBox ' 110
IsDataEnabled = True
Case Is = acComboBox ' 111
IsDataEnabled = True
Case Is = acCheckBox ' 106
IsDataEnabled = True
End Select
If IsDataEnabled Then
If InStr(1, ctl.ControlSource, "=" > 0 Then
If ctl.value = "My Value" then
icount = icount + 1
End if
End if
End If
Next ctl
Caution, the above code will most likely need debugging. This is completely ad-hoc.
create a table to store the results in (tblCounts), and one to list all of the codes to search for (tblCodes)
In tblCounts have one field for each possible code to search for, and name the field the same as the code
In tblCodes have one field only, and add a record for each code, name this field CodeField.
Then use this code, only change the "YourTableName" to the actual name of your table you want searched, and the "[DateField]" to the name of the Date/Time field in this table
This code only check for the current month and year, so you'd have to change that to reflect other months, but it should give you a starting point.
Private Sub GetCounts_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * From tblCounts"
DoCmd.SetWarnings True
Dim db As DAO.database, rstTable As DAO.Recordset
Dim rstResults As DAO.Recordset, rstCodes As DAO.Recordset
Dim tdf As DAO.TableDef, fld As DAO.Field
Dim intCount As Integer, strCode As String, strFldName As String
Set db = CurrentDb
Set rstResults = db.OpenRecordset("tblCounts" 'table with results
Set rstCodes = db.OpenRecordset("tblCodes" ' table with codes to search
Set tdf = db.TableDefs("YourTableName" ' table you want searched
Set rstTable = db.OpenRecordset("YourTableName"
If rstTable.RecordCount > 0 Then
With rstTable
.MoveFirst
Do While Not .EOF
intCount = 0
If Month(rstTable![DateField]) = Month(Now()) And Year(rstTable![DateField]) = Year(Now()) Then
With rstCodes
.MoveFirst
Do While Not .EOF()
strCode = rstCodes!CodeField
For Each fld In tdf.Fields
strFldName = Nz(rstTable(fld.Name), " "
If InStr(strFldName, strCode) > 0 Then
intCount = intCount + 1
End If
Next fld
With rstResults
If .RecordCount = 0 Then
.AddNew
Else
.MoveFirst
.Edit
End If
'add count to code field
'this code assumes that the field name is the same as the code
rstResults(strCode) = intCount
.Update
End With
intCount = 0
.MoveNext
Loop
End With
End If
.MoveNext
Loop
End With
End If
MsgBox "Done"
ExitHere:
Set db = Nothing
Set rstTable = Nothing
Set rstCodes = Nothing
Set rstResults = Nothing
Set tdf = Nothing
Set fld = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Number & " : ", vbCritical, "Error While Processing "
GoTo ExitHere
End Sub
You could also do a union query to query the table and base your group count query on the union query. Then you don't have any VBA code and no temporary tables.
SELECT Field1
FROM MyTable
UNION ALL SELECT Field2
FROM MyTable
UNION ALL SELECT Field3
FROM MyTable
UNION ALL SELECT Field4
FROM MyTable
... <=== field5 thru field35 repeating the pattern
UNION ALL SELECT Field36
FROM MyTable
The question appears to indicate the db is poorly designed/laid out. Thirty plus fields which all contain more or less the same information strongly suggests that the table / db is far from mormalized and should be reviewed.
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.