transpose data in Access 2003

I kindly ask you help me transpose the data as in attached file using a VBA code in Access 2003.
 Matthewspatric please add 2 more columns to my table as you can see in the attached file and to send me the VBA code. It is a follow-up of the problem you solved for me. Thank you very much.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
test this
marian68Author Commented:
Thank you very much capricorn1 for your reply but it doesn't work. I attached the excel file to show you what I need and how to transpose the data but I import the data directly in Access, so I need the VBA code to transpose the data from an access table(StagingTable-64 fields) to an another access table(PermanentTable - 5 fields) without passing the date through Excel. In fact StagingTable is a query made from the table where I imported all information. Thank you very much.
Rey Obrero (Capricorn1)Commented:
what is not working?

where is your query?

why not upload a db with your tables.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Rey Obrero (Capricorn1)Commented:
test this
Sub Transpose()
    ' requires reference to Microsoft DAO
    Dim rs As DAO.Recordset
    Dim Counter As Long
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [StagingTable]")
    With DoCmd
        .SetWarnings False
        For Counter = 4 To rs.Fields.Count
            .RunSQL "INSERT INTO [PermanentTable] ([DATE],[APP #],[NAME], [SPECIALTY], [GRADE]) " & _
                "SELECT [DATE],[APP #],[NAME], '" & rs.Fields(Counter - 1).Name & "', " & _
                "[" & rs.Fields(Counter - 1).Name & "] " & _
                "FROM [StagingTable]"
        .SetWarnings True
    End With
    Set rs = Nothing
    MsgBox "Done"
End Sub

Open in new window

marian68Author Commented:
The original data is in Lotus format. I import this data in Access in a table. From this table I create a query called StagingTable with 65 field. From this query I need the data transposed in a table called PermanentTable with 5 fields.  So the code VBA doesn't have anything to do with Excel.
First I copied your VBA code in my database it didn't work but when I used your database it work but after I imported the date and I tried several times. I was a little confused because I saw in your VBA code the words "excel" and "table1". Tomorrow I"ll tell if it works all the time and I'll give the points if everything is OK. For the most convenient is to give me only the code VBA and I copy it in my database of course respecting the tables and fields' names.
Thank you very much
Rey Obrero (Capricorn1)Commented:
you have the codes above...your last post

< I saw in your VBA code the words "excel" and "table1">
that is not the code for your requirement.
marian68Author Commented:
for the last code when I execute it I received the following messages:
"enter parameter value app #" Ok
"enter parameter value name" ok
and so on
when I pushed cancel  I received the message " Run time error 3270 Reserved Erroe"
Thank you
Rey Obrero (Capricorn1)Commented:
what are the name of the fields that you have in your




post them here
marian68Author Commented:
I'm sorry but I have to go. I'll send you tomorrow the name of the fileds
Thank you very much

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.