Correct method for transposing data from an Excel spreadsheet into an Access table? (1 Viewer)

[SurreyNick]

Thank you. I am almost too embarrassed to say I don't think this is going to be the solution I need, because you have given me the solution to the problem I posed. I am however guilty of laziness in not fully explaining what I am trying to do.

When I posted the original question I assumed there would be a straightforward approach to this little problem and it wouldn't matter if the number of columns and rows varied from spreadsheet to spreadsheet. It was irresponsible of me not to explain clearly from the outset the task at hand and I apologise for that.

The spreadsheet I gave a picture of is an actual example of one of the datasets that will need transposing, but by no means the only one. There are numerous exams each with a different number of questions and also a different range of question numbers. I was hoping to come up with a solution which can handle this without requiring user intervention. The power query looks like it might, but the VBA to execute it from within Access is way beyond my current knowledge.

I found this online reference but I can't even begin to get my head around it. I don't even know if I could use it because I'm on Access 2010 and it's describes a process for Access 2016.

Use VBA to automate Power Query in Excel 2016

I think, if there's no alternative method, I'm going to have do a lot more study

 

[theDBguy]

I think, if there's no alternative method, I'm going to have do a lot more study

Not sure if you consider it as an alternative, but I already mentioned it earlier where you don't even have to get Excel involved, if you're using VBA anyway. I looked at that link you posted for automating Power Query, and it looks like a lot of work as compared to just importing or linking to the Excel file and using Access VBA to loop through the records and import the data. Just my 2 cents...

 

[SurreyNick]

compared to just importing or linking to the Excel file and using Access VBA to loop through the records and import the data.

Using Access VBA is the sort of solution I was originally hoping for but I don't know how to do it or where to begin. If you could perhaps give me enough of a clue or point me in the right direction so I can run with it?

 

[theDBguy]

Using Access VBA is the sort of solution I was originally hoping for but I don't know how to do it or where to begin. If you could perhaps give me enough of a clue or point me in the right direction so I can run with it?

I mentioned using Excel, initially, because I thought this was a one time deal. After learning this will be a regular routine, I think using Access VBA would be an easier approach.

I imagine the process going something like this.

1. The Excel file is imported into a temp table
2. A VBA routine will read the temp table row by row and field by field
3. In each iteration of the loop, a new record is appended to the actual table with the information from all the common fields (student name, course, and class, etc.) as well as the name of the field (question number), and the value in that field (score)
4. The loop will continue to the next record doing the same thing in step 3 until all the records and fields are processed
5. The temp table is deleted to clean up

In essence, I see the process using two loops, an outer loop to go through the records from the Excel file, and an inner loop to go through the fields (question numbers and scores).

Hope that makes sense...

 

[SurreyNick]

1. A VBA routine will read the temp table row by row and field by field
2. In each iteration of the loop, a new record is appended to the actual table with the information from all the common fields (student name, course, and class, etc.) as well as the name of the field (question number), and the value in that field (score)
3. The loop will continue to the next record doing the same thing in step 3 until all the records and fields are processed

Thank you, the process is very clear. It is this procedure I don't know how to accomplish but I'm assuming I need to learn how to work with record sets. I'll let you know if I succeed.
Thank again