Correct method for transposing data from an Excel spreadsheet into an Access table?

[SurreyNick]

Using VBA can someone explain the process I need to follow to transpose the data in the spreadsheet shown in pic1 into a db table laid out as shown in pic2?

Cycling through each of the records in the spreadsheet I need to take the StudentID and ClassRef and append to it an ExamPaperID which will come from a control on my form, then take each question number in turn e.g. 1.1, 1.2, 1.3 etc. and add the student's score for that question, repeating this through all question numbers before moving on to the next student record.

Other than doing a very long-winded transposition copying and pasting within Excel I don’t know where to begin. I am sure there’s a much better way than this but I don’t know what that would be.

If someone can point me in the right direction, perhaps directing me at a similar example which I can use as a foundation, I would be very grateful.

Thank you.

 

[theDBguy]

You can certainly do this with VBA, but I thought there was a way to do this using Excel. I just can't remember how right now. Let's see if someone else remembers (or corrects me).

 

[Minty]

A Power query is your friend in Excel.

It has the ability to transform that data into a normalised structure fully automagically

If you need some help just post a small example spreadsheet and we can demonstrate.

 

[SurreyNick]

Wow! Never heard of a Power query. Definitely want to know more about that
Spreadsheet attached.

 

[Minty]

Told you it was quick...

 

[SurreyNick]

Gosh. That's extraordinary!
Please tell. How is it done?
Nick

 

[Minty]

Erm I think it uses black holes and mirrors...

This is all in done in Excel (for interested viewers)

1. Take you data - make sure it is in a table for simplicity.
2. Click on the Data menu tab, and select Get Data From Table.
3. This will open the Power Query Window.
4. Select the Columns you want to transpose (In your case 1.1 though to 8.4) - They should be highlighted in Green.
5. Click on the Transform menu tab
6. Click on the Unpivot Columns action. (et Voila nearly!)
7. Click back on the Home tab.
8. Click Close and load.

Bob is now your uncle.

If you make a mistook somewhere in the process, you can click in the query properties window on the right and edit / remove steps to make any changes.

 

[theDBguy]

Erm I think it uses black holes and mirrors...

This is all in done in Excel (for interested viewers)

1. Take you data - make sure it is in a table for simplicity.
2. Click on the Data menu tab, and select Get Data From Table.
3. This will open the Power Query Window.
4. Select the Columns you want to transpose (In your case 1.1 though to 8.4) - They should be highlighted in Green.
5. Click on the Transform menu tab
6. Click on the Unpivot Columns action. (et Voila nearly!)
7. Click back on the Home tab.
8. Click Close and load.

Bob is now your uncle.

If you make a mistook somewhere in the process, you can click in the query properties window on the right and edit / remove steps to make any changes.

Hi Minty. Thanks for the assist!

 

[SurreyNick]

Yes, thanks very much. A really clever solution and a very useful piece of knowledge to own.
Now all I need to do is check I can execute the procedure within Access using VBA.

 

[theDBguy]

Now all I need to do is check I can execute the procedure within Access using VBA.

Well, if you're going to use VBA anyway, then just do it with VBA. No sense in getting Excel involved (me think). I just thought this was a one-time deal, that's why I suggested to stick with Excel. I guess not.

 

[CJ_London]

depends on the number of columns in excel (max for access is 255) but you can do this in sql - using a union query, each union bringing through the 1.1, 1.2, etc columns in turn.

 

[Minty]

You can use a power query from Access via VBA in an Excel sheet.
It's a little convoluted getting the syntax right but can be very handy for manoeuvring data around for import or export.

If you look at the power query in edit mode you can actually then dig in and see the actual query syntax.

So your query is actually this in the advanced editor;

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Name", type text}, {"Class", type text}, {"1.1", Int64.Type}, {"1.2", Int64.Type}, {"1.3", Int64.Type}, {"2.1", Int64.Type}, {"2.2", Int64.Type}, {"3.1", Int64.Type}, {"3.2", Int64.Type}, {"3.3", Int64.Type}, {"4.1", Int64.Type}, {"4.2", Int64.Type}, {"5.1", Int64.Type}, {"5.2", Int64.Type}, {"5.3", Int64.Type}, {"6.1", Int64.Type}, {"6.2", Int64.Type}, {"6.3", Int64.Type}, {"7.1", Int64.Type}, {"8.1", Int64.Type}, {"8.2", Int64.Type}, {"8.3", Int64.Type}, {"8.4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Student ID", "Name", "Class"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

You can see the steps are separated by # marks.

 

[SurreyNick]

Well, if you're going to use VBA anyway, then just do it with VBA. No sense in getting Excel involved (me think). I just thought this was a one-time deal, that's why I suggested to stick with Excel. I guess not.

It was perfectly reasonable to assume this was a one off. I should have made clear it wasn't. But in fact it is a procedure that will be executed regularly. It's for the exams assessment db I recently completed for my son's Biology dept. But until it has been thoroughly road tested the majority of teachers in the dept. will carry on using spreadsheets. My son does however want to get all the data into the db so he asked if I could put a button on a form within the db to do that. The subject of this thread is part of the routine which will do that.

I did originally intend using only VBA to transpose the data into the format I want, ready for importing into access, but I have no objection to using code to execute a power query if it achieves the same thing. It's the solution that matters

Nevertheless, I would still be very interested to know how it could be done using VBA. The knowledge will be valuable and I would like to learn. I think @CJ_London was proposing a solution but I didn't quite grasp it. My knowledge is still very limited.

Nick.

 

[theDBguy]

I think @CJ_London was proposing a solution but I didn't quite grasp it.

@CJ_London was suggesting to try something like this.

SELECT [Student ID], [Name], Class, [1.1] As QNumber FROM TableName
UNION
SELECT [Student ID], [Name], Class, [1.2] FROM TableName
UNION
SELECT [Student ID], [Name], Class, [1.3] FROM TableName
UNION
SELECT [Student ID], [Name], Class, [2.1] FROM TableName
UNION
and so on...

 

[SurreyNick]

Ah! Thank you. I'll give that a try

 

[theDBguy]

Ah! Thank you. I'll give that a try

Let us know how it goes...

 

[SurreyNick]

Will do. The Excel power query proved itself to be a breeze to make and run, but creating and running it from within Access isn't quite as simple a task. I found conflicting opinion online whether it's even possible, but did eventually find a thread that claims to show a method. I haven't tried it yet, although I probably shall, but I think I'll first have a go with the suggested union query method. It'll have to be tomorrow though. I will report back

 

[SurreyNick]

Well, the union query was very straightforward and I can see it's going to be a useful tool, but alas I haven't been able to get it to produce exactly what I need. I have been able to use it to produce the result shown in pic1 and this gets me part way, but it lacks the all important question number alongside each score, as shown in column D of the power query (shown in pic2). Using a union query, is there a way to include the QNumber in each row?

 

[Minty]

Will do. The Excel power query proved itself to be a breeze to make and run, but creating and running it from within Access isn't quite as simple a task. I found conflicting opinion online whether it's even possible, but did eventually find a thread that claims to show a method. I haven't tried it yet, although I probably shall, but I think I'll first have a go with the suggested union query method. It'll have to be tomorrow though. I will report back

The Excel Power Query is certainly doable but not straightforward, I do have some code somewhere that created about 200 pivot tables across 20 or 30 tabs in Excel, all driven from Access via Power queries.

It took a good deal of fiddling to get it to work, I'll see if I can cobble together an example, but it might take a while.

 

[theDBguy]

Well, the union query was very straightforward and I can see it's going to be a useful tool, but alas I haven't been able to get it to produce exactly what I need. I have been able to use it to produce the result shown in pic1 and this gets me part way, but it lacks the all important question number alongside each score, as shown in column D of the power query (shown in pic2). Using a union query, is there a way to include the QNumber in each row?

Oops, sorry about that, my bad. Please try this one.

SELECT [Student ID], [Name], Class, "1.1" As QNumber, [1.1] As Score FROM TableName
UNION
SELECT [Student ID], [Name], Class, "1.2", [1.2] FROM TableName
UNION
SELECT [Student ID], [Name], Class, "1.3", [1.3] FROM TableName
UNION
SELECT [Student ID], [Name], Class, "2.1", [2.1] FROM TableName
UNION
and so on...