Marsh...I have included the code for the whole
sub...thanks for your help
Sub Transpose()
Dim dbsExport As Database
Dim rstData, rstTdata As Recordset
Dim wrkJet As Workspace
Dim SQLtext, Action, FieldName As String
Set dbsExport = DBEngine.Workspaces(0).Databases(0)
Set rstData = dbsExport.OpenRecordset("dzn96ind",
dbOpenDynaset)
rstData.MoveFirst
' the field [ind] defines an industry code. I have
previously created tables which have a list of locations
as the first field
' and then a number of industry classifications as the
remainimg fields. There are ober 500 classifications so
the data is to
' be stored in 3 tables.
' The source file [dzn96ind] is a flat file that has
90,000 records which just define the location [DZN96],
industry group[IND] and no of jobs [JOBS]
' the idea is to read a line of the source file, use
industry to identify the appropriate file using [ind]
identify the appropriate record (row) using the location
[DZN96] and
' then insert the number of jobs in the field which
has the title the same as [ind]
' this select determines in which table the data is to
be stored. There are a number of protential options
' the variable Action is used to identify the correct
table
While Not rstData.EOF
Select Case rstData!ind
Case "A000"
Action = "1"
Case "B000"
Action = "1"
Case "C000"
Action = "2"
Case "D000"
Action = "2"
Case "E000"
Action = "2"
Case "F000"
Action = "2"
Case "G000"
Action = "2"
Case "I000"
Action = "2"
Case "K000"
Action = "2"
Case "LC000"
Action = "3"
Case "M000"
Action = "3"
Action = "3"
Case "O000"
Action = "3"
Case "P000"
Action = "3"
Case "Q000"
Action = "3"
Case "&&&&"
Action = "3"
Case Else
Select Case Val(rstData!ind)
Case 110 To 2839
Action = "1"
Case 2840 To 7520
Action = "2"
Case 7700 To 9700
Action = "3"
End Select
End Select
' Using the Action variable, the data in the source
file has been identified as appropriate to a particular
table
' only three choices are possible and each defines
an appropriate SQL
Select Case Action
Case "1"
SQLtext = "select * from [100TO2839] where
[TZN]= " + Str(rstData![dzn96])
Case "2"
SQLtext = "select * from [2840TO7520]
where [TZN]= " + Str(rstData![dzn96])
Case "3"
SQLtext = "select * from [7700PLUS] where
[TZN]= " + Str(rstData![dzn96])
End Select
' create the recordset using the appropriate table
and selecting the correct location
Set rstTdata = dbsExport.OpenRecordset(SQLtext,
dbOpenDynaset)
' check that a record has been selected
If IsNull(rstTdata.RecordCount) Or
rstTdata.RecordCount = 0 Then
Debug.Print "No record selected for location " +
Str(rstData![dzn96])
Else
With rstTdata
.MoveFirst
.Edit
' this is the line I need help with. I know
what I have here doesn't work
' what I am trying to do id to get it to use
the value in rstData![ind] to identify the appropriate
field in the rstTdata recordset
.(rstData![ind]) = rstData!jobs
.Update
.Close
End With
rstData.MoveNext
End If
Wend
End Sub
