Access convert columns into rows

Question

Hi All,

I have a fillable pdf form which is completed by the applying laboratory. I extract info into a txt file and that file contains all data as one row of records, among those are different analytical methods (which are check marked on the form). Currently the extract is appended into a temp table in the database and looks like this:

LabID | Method1 | Method 2| Method3

0001  |    Yes       |    No        |      Yes     

Eventually, I have a final table where I need to transfer those records as:

LabID | MethodID | Selected

0001  | Method1   |    Yes

0001  | Method2   |    No

0001  | Method3   |    Yes

Please help! Let me know if there is a way of doing this and how. 

Thanks a ton!

=)

Answer 1

Yep, that's what I already figured out =) But I think I found an easier fix. I am changing the fillable pdf form from check boxes to actual drop-down fields with method codes. This will allow me export data already with method codes as data fields instead of headers.

Should work, fingers crossed. Thanks for your help anyways, I will keep this example for later, in case I come across something similar =)

Answer 2

To solve your question

create the output table Tb2 with 3 field LabId, Method and Select

then create a module and paste this code

Function CreaTable()

    Dim rstIn As Recordset

    Dim rstOut As Recordset

    Dim ind As Integer

    Set rstIn = CurrentDb.OpenRecordset("Select * from tb1")

    Set rstOut = CurrentDb.OpenRecordset("Select * from tb2")

    Do While Not rstIn.EOF

       For ind = 1 To rstIn.Fields.Count - 1

           rstOut.AddNew

           rstOut("LabId") = rstIn("LabId")

           rstOut("Method") = rstIn(ind).Name

           rstOut("Selected") = rstIn(ind).Value

           rstOut.Update

       Next

       rstIn.MoveNext

    Loop

    rstIn.Close

    rstOut.Close

    Set rstIn = Nothing

    Set rstOut = Nothing

End Function

Ciao Mimmo

Answer 3

The table you are trying to generate correctly represents a many to many relationship type between labs and methods, whereas your original table is an example of what is known as 'encoding data as column headings', which violates one of the fundamental rules of the database relational model, the Information Rule (Codd's Rule #1) which requires that all data be stored as values at column positions in rows in table.

The restructuring of an incorrectly designed table like this can be automated, and you'll find an example as UnencodeColumns.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, although your source table has Boolean (Yes/No) columns the example for Quantity in Stock values would in fact be the appropriate one in your case as you wish to store the Boolean values in the third column, Selected, in your final table rather than having a two-column table in which each row repesents a selection.  The StoreStock_Vals table in my demo is analogous to the table you wish to generate.  In your case, rather than quantities the values would be Boolean TRUE or FALSE values

Answer 4

Thanks for your help, although, I am not sure I understand you correctly. When you say "creating a new table Tb2 with just one field containing 1,2,3" - do you mean to have one column with 3 sequential rows or one row with three columns labeled as column 1, 2, and 3?

Also, If I create a new table Tb2, then the check marks are gone, because the fields are pre-set already as Yes/No fields. 

Thank you!

Answer 5

Hi,

you can do it creating a new table Tb2 with just one field containing 1,2,3

then do a query so as:

SELECT Tb1.LabId, Tb2.Method, IIf([Method]=1,[Method1],IIf([method]=2,[Method2],[Method3])) AS Selected

FROM Tb1, Tb2;

Ciao Mimmo