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

SurreyNick

Member
Local time
Today, 00:53
Joined
Feb 12, 2020
Messages
127
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.
 

Attachments

  • ExcelSource.jpg
    ExcelSource.jpg
    327.5 KB · Views: 149
  • tblResultsImport.jpg
    tblResultsImport.jpg
    78.6 KB · Views: 142

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:53
Joined
Oct 29, 2018
Messages
21,358
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

AWF VIP
Local time
Today, 00:53
Joined
Jul 26, 2013
Messages
10,355
A Power query is your friend in Excel.

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

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

SurreyNick

Member
Local time
Today, 00:53
Joined
Feb 12, 2020
Messages
127
Wow! Never heard of a Power query. Definitely want to know more about that :)
Spreadsheet attached.
 

Attachments

  • SourceXLS.zip
    9.4 KB · Views: 147

Minty

AWF VIP
Local time
Today, 00:53
Joined
Jul 26, 2013
Messages
10,355
Told you it was quick...
 

Attachments

  • Target.zip
    29.1 KB · Views: 146

SurreyNick

Member
Local time
Today, 00:53
Joined
Feb 12, 2020
Messages
127
Gosh. That's extraordinary!
Please tell. How is it done?
Nick
 

Minty

AWF VIP
Local time
Today, 00:53
Joined
Jul 26, 2013
Messages
10,355
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

I’m here to help
Staff member
Local time
Yesterday, 17:53
Joined
Oct 29, 2018
Messages
21,358
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! (y)
 

SurreyNick

Member
Local time
Today, 00:53
Joined
Feb 12, 2020
Messages
127
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

I’m here to help
Staff member
Local time
Yesterday, 17:53
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 00:53
Joined
Feb 19, 2013
Messages
16,553
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

AWF VIP
Local time
Today, 00:53
Joined
Jul 26, 2013
Messages
10,355
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;
SQL:
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

Member
Local time
Today, 00:53
Joined
Feb 12, 2020
Messages
127
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’m here to help
Staff member
Local time
Yesterday, 17:53
Joined
Oct 29, 2018
Messages
21,358
I think @CJ_London was proposing a solution but I didn't quite grasp it.
@CJ_London was suggesting to try something like this.
SQL:
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

Member
Local time
Today, 00:53
Joined
Feb 12, 2020
Messages
127
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 :)
 
Last edited:

SurreyNick

Member
Local time
Today, 00:53
Joined
Feb 12, 2020
Messages
127
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?
 

Attachments

  • UnionQ.jpg
    UnionQ.jpg
    72.9 KB · Views: 114
  • PowerQ.jpg
    PowerQ.jpg
    94.9 KB · Views: 112

Minty

AWF VIP
Local time
Today, 00:53
Joined
Jul 26, 2013
Messages
10,355
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

I’m here to help
Staff member
Local time
Yesterday, 17:53
Joined
Oct 29, 2018
Messages
21,358
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.
SQL:
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...
 

Users who are viewing this thread

Top Bottom