Ideas to Transpose a Table

[meBrian]

Ok. Here is what I'm dealing with. I have TableA which is structured like this:

ItemID, S1F1_a, S1F1_b, S1F1_c, S1F2_a, S1F2_b, S1F2_c...

and so on where the F# in the field name changes up to F9 and then the S# increments going up to S5F9_(a,b,c).

Ideally, the table should be structured like this:

ID, ItemID, Category (S1F1, S1F2 or whatever), Aval, Bval, Cval

The goal is to be able to look at an individual Category's A value under an ItemID based on values of that Category's values B and C. I am expecting multiple results from a single ItemID.

Did that make sense? If not maybe this will help. I'd like to do this:

Select ItemID, Category, Aval from tableA where (tableA.Bval <> Null and tableA.Cval <> 1) AND (tableA.ItemID = 100)

Anyone have any suggestions on this?

Thanks.
Brian

 

[CautionMP]

meBrian,
Assuming the structures you detailed and that you want to create a new table with the restructured data something like this may work (typed, un-tested).

Sub TransformTable()
Dim rstInput As DAO.Recordset
Dim rstOutput As DAO.Recordset
Dim lngField As Long
Set rstInput = CurrentDb.OpenRecordset("TableA", dbOpenForwardOnly)
Set rstOutput = CurrentDb.OpenRecordset("NewTable", dbOpenDynaset)
'Loop through input
Do
  'loop through fields of input
  For lngField = 1 To rstInput.Fields.Count - 1 Step 3 'assumes a, b, c
    'Create the output records
    With rstOutput
        .AddNew
        '.Fields("ID") = ????
        .Fields("ItemId") = rstInput.Fields("ItemID")
        .Fields("Category") = Left(rstInput.Fields(lngField).Name, 4)
        .Fields("Aval") = rstInput.Fields(lngField)
        .Fields("Bval") = rstInput.Fields(lngField + 1)
        .Fields("Cval") = rstInput.Fields(lngField + 2)
        .Update
    End With
  Next lngField
  rstInput.MoveNext
Loop Until rstInput.EOF
rstInput.Close
Set rstInput = Nothing
rstOutput.Close
Set rstOutput = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[meBrian]

Thanks CMP. I was thinking generally along those lines. You're correct in assuming that I want to create a new table to work off of.

I will probably move forward with something similar to this. My only concern would be with the assumption that fields would remain in order in tableA - a,b,c. I might look at verifying the Category in the fieldname for fields 2 and 3 or something. You can never be certain that someone (I guess including me) doesn't drag and drop unknowingly.

Thanks for your response.