I have a table the currently looks like this:
Emplid Skill1 Skill2 Skill3 Skill4 Skill5
222222 2233 2418 2332 2324 2378
111111 1452 4562 7895 1455 1458
Does anyone know how I could use a query to rearrange the data like so:
Emplid Skill
222222 2233
222222 2418
222222 2332
222222 2324
222222 2378
111111 1452
111111 4562
111111 7895
111111 1455
111111 1458
Thank you in advance,
Byron R. Jones
Byron,
If I were you, I would fix this *permanently*. As you've no doubt
found, querying denormalized data is a pain.
I wrote some code to do this. Basically, loop through the skill
columns and append the (EmployeeID, SkillID) pairs to a single table.
Public Sub NormalizeTable(ByVal strSrcTable As String, ByVal
strDestTable As String, ByVal strPK As String)
'Statement to be built:
'INSERT INTO User_Apps ( Username, Access )
'SELECT xlsAppsLoaded.Username, xlsAppsLoaded.Access
'FROM xlsAppsLoaded
'WHERE xlsAppsLoaded.Access='T';
'INPUTS:
'strSrcTable = the table the data is coming FROM
'strDestTable = the table the data is being appended TO
'strPK = the primary key of the Destination table.
'SAMPLE CALL
'Normalizetable "xlsappsloaded","User_Apps","username"
Dim tdf As DAO.TableDef
Dim intField As Integer
Dim strSQL As String
Set tdf = DBEngine(0)(0).TableDefs(strSrcTable)
For intField = 1 To tdf.Fields.Count - 1
'--building SQL statement
strSQL = "INSERT INTO " & strDestTable & " (Username,
ApplicationTitle) "
strSQL = strSQL & "SELECT [" & strSrcTable & "].[" & strPK
& "], '" & tdf.Fields(intField).Name & "'"
strSQL = strSQL & " FROM [" & tdf.Name & "]"
strSQL = strSQL & " WHERE ((([" & tdf.Name & "].[" &
tdf.Fields(intField).Name & "]='T')));"
'--executing the SQL statement (dbFailOnError just makes
the code continue of an illegal insert is run
'--it will just fail on that insert, but the code will
continue
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next intField
Set tdf = Nothing
MsgBox "Finished normalizing " & strSrcTable & " into " &
strDestTable, vbOKOnly
End Sub
