Below is a very handy function that transposes fields from "Table1" into "Table2". Note, in Table1 the Field2, Field3, etc represent Option codes.
Basically it takes this...
Table1
ProdBreakDown --Field2----- Field3-----Field4
Widget1------------1253--------3843-------3986
Widget2------------1130--------1234-------3843
Widget3------------1234--------5698-------4207
And transposes it into this...
Table2
ProdTarget--OptionCodes
Widget1--------1253
Widget1--------3843
Widget1--------3986
Widget2--------1234
Widget2--------1253
Widget2--------3843
Widget3--------1234
Widget3--------5698
Widget3--------4207
To see this in action. Look at my attached database.
MY QUESTION: Instead of transposing ALL records, I only want to transpose records from that contain specific option codes. For instance, the criteria would restrict transposed records to be where only Option Codes from Table1 that have a string that begins with '12' OR a full string that equals '3843' are transposed.
For a small set of records, I could easily query this from the total results of Table2 after my function has been ran by using a simple "OR" statement in SQL. The problem is, my real data consists of over 70,000 records. So transposing all the data results in millions of records being created which over inflates my database and causes it to corrupt. That's why I need the restrict to specific criteria when transposing it into Table2 to avoid creating all the addition unnecessary records.
I hope this explanation is helpful. Any feedback is much appreciated!
KP
Code:
Option Compare Database
Option Explicit
Public Sub TransRecords()
Call TransposeRecordset("Table1", "Table2", "ProdBreakDown")
MsgBox "Table2 updated!", vbExclamation + vbOKOnly
End Sub
Private Function TransposeRecordset(pstrrecoriginal As String, pstrrecnew As String, pstrkey As String)
Dim db As Database
Dim recorg As Recordset
Dim recnew As Recordset
Dim intCount As Integer
Dim varkeyvalue As Variant
Dim bolfound As Boolean
Set db = CurrentDb()
Set recorg = db.OpenRecordset("select * from [" & pstrrecoriginal & "]")
Set recnew = db.OpenRecordset("select * from [" & pstrrecnew & "]")
'Loop through records in recorginal
While Not recorg.EOF
intCount = 0
bolfound = False
'Loop through fields in recorginal looking for key
While intCount <= recorg.Fields.Count - 1 And bolfound = False
If recorg(intCount).Name = pstrkey Then
varkeyvalue = recorg(intCount)
bolfound = True
DoCmd.Echo True, "Transposing " & varkeyvalue
End If
intCount = intCount + 1
Wend
For intCount = 0 To recorg.Fields.Count - 1
'skip key field
If recorg(intCount).Name <> pstrkey Then
recnew.AddNew
recnew(0) = varkeyvalue
recnew(1) = Nz(recorg(intCount).Value, "")
recnew.Update
End If
Next
recorg.MoveNext
Wend
DoCmd.Echo True, ""
End Function