Rearrange Table Data

[AC5FF]

I thought I was in for an easy task today - but I'm hitting that dreaded stone wall again...

I have a table of data that has been imported from Excel. The data is formatted in the table as such:
P A R T
___________________
W /
E /
I / PRICES
G /
H /
T /

As it sits, there are 53 columns by 151 rows

I need this to "pivot" into the following columns:
Part Weight Price

So, three columns, approximately 7200 rows.

Is there any easy way to reorganize my data like that? Every way I have tried has faild.

Thx

 

[MarkK]

I don't understand how this represents a table structure ...

P A R T
___________________
W /
E /
I / PRICES
G /
H /
T /

Could you elaborate?

 

[AC5FF]

Sorry;

Part #'s are column headings and Weight is listed down the first column.
The data (prices) are figured by looking at where the weight and part# intersect.

 

[MarkK]

This is not how tables are structured in a database system.
Each record in a table in a database represents a single instance of an object, so a part number and any data belonging to that part should be in a single record. A record looks like a row, but it has fields--columns--that represent distinct attributes of the object represented by the record.
If your tables aren't structured this way you cannot leverage any of the power that a database system provides.

 

[AC5FF]

Sorry - Been a crazy day at work and I was unable to get back to here...

Lagbolt - I completely agree with you. That is why I am trying to get the data restructured into a format I can easily use within the database...

The original data was put into a 'spreadsheet' format in Excel for ease of use when printed out. Now that I am moving a lot of this process over to an Access database, I would like to get my data into a 'table' format. Hence the need for help.

Since I have a little more time now, Let me try and better display what I have. This is what is in Excel and (unfortunatly) how I imported it into Access.

Pounds	Toy A	Toy B	Toy C	Toy D	Toy E
1	1.52	1.82	1.92	2.20	2.25
2	2.00	3.15	4.15	4.85	5.25
3	2.84	3.19	4.21	5.62	6.05
4	3.10	4.05	4.86	5.91	6.25
5	3.84	4.55	5.64	6.33	7.05
…	---	---	---	---	---
100	28.64	82.41	110.94	148.10	195.46

Restructured into something like this:

Pounds	Product	Cost
1	Toy A	1.52
1	Toy B	1.82
1	Toy C	1.92
1	Toy D	2.20
1	Toy E	2.25
2	Toy A	2.00
2	Toy B	3.15
2	Toy C	4.15
2	Toy D	4.85
---	---	---
100	Toy E	195.46

Can it be done automatically????

 

[scubadiver007]

What you can do is create a query for each individual toy and then append the rows to a new table. It will take time but I fear that is the only way.

 

[MarkK]

Yeah, you can do this in VBA with code like ...

Sub ConvertData()
[COLOR="Green"]'  This routine creates a temp QueryDef to insert data into a new table.
'  Then it opens a recordset on the existing data and traverses the fields
'  collection of each record (since each record contains multiple costs to
'  extract ).  For each field--except the first, which is the weight--this routine
'  inserts a new record in the new table which contains only the weight, the
'  name and the cost.[/COLOR]
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim fld As DAO.Field
   Dim qdf As DAO.QueryDef
   
   Set dbs = CurrentDb
[COLOR="Green"]   'create temp querydef to execute parameterized inserts
   '(see the Else block of the Select Case, below)[/COLOR]
   Set qdf = dbs.CreateQueryDef("", _
      "INSERT INTO Table2 " & _
         "( Pounds, ToyName, Cost ) " & _
      "VALUES " & _
         "( [prmPounds], [prmName], [prmCost] );")
   
[COLOR="Green"]   'create recordset of badly structured data to move to new table[/COLOR]
   Set rst = dbs.OpenRecordset( _
      "SELECT * FROM Table WHERE Field1 <> 'Pounds'")
   With rst
[COLOR="Green"]      'traverse all records of the badly structured data[/COLOR]
      Do While Not .EOF
[COLOR="Green"]         'traverse all fields of each record[/COLOR]
         For Each fld In .Fields
            Select Case fld.Name
               Case "Field1"
[COLOR="Green"]                  'ignore: this is the weight column[/COLOR]
               Case Else
[COLOR="Green"]                  'this is a column named "Toy X"
                  'assign the weight from column 1...[/COLOR]
                  qdf.Parameters("prmPounds") = !Field1
[COLOR="Green"]                  '...and use the name and value of the current field...[/COLOR]
                  qdf.Parameters("prmName") = fld.Name
                  qdf.Parameters("prmCost") = fld.Value
[COLOR="Green"]                  '...to insert the new record[/COLOR]
                  qdf.Execute dbFailOnError
            End Select
         Next
         .MoveNext
      Loop
      .Close
   End With

End Sub

You'll have to adapt the SQL to the names of the structures you actually have, but this gives you a very close approximation of a process that will work for your purpose.
Cheers,

 

[AC5FF]

LagBolt

I hope that you had this VBA laying around and didn't have to spend much time writing it!

This is going to be a good oportunity for me to learn a little on how to incorporate VBA into my DB. I am a very green novice when it comes to VBA. I know a lot about programming and how to read/follow what is going on; but how to incorporate this into a pre-existing DB --- I'm pretty much starting from scratch.

Any pointers on how to incorporate this would be appriciated; but I am going to research and work on it as I go...

THX!

 

[MarkK]

That code's not that complicated. Each step is quite simple really.

I think how you use this tool depends on your work flow. Is this a one-shot reformat or do you need to do it weekly with data from somewhere beyond your control?
At its most simple, copy it to a standard module, change the names and places, and run it from your debug toolbar. Put the insertion point in the sub's signature line and click the 'step into' button or hit F5 or something.
Or if you run it weekly from a button on a form, leave it in the standard module and call it from a button click...

private sub command0_click()
  ConvertData
end sub

Or if you have a few conversion routines you want to keep together and run each from a button, maybe create a conversion tool form, put the code in that form, and run it from button clicks.
How you incorporate it depends on how your intend to use it.
Cheers,

 

[AC5FF]

I am going to work on this as I have time Lagbolt.... Thanks!

This is pretty much going to be a one-time thing; or could be a once a year thing.
For right now, I ended up running a simple update query; I just had to edit/re-run it about 50 times.. LOL I needed the data re-formatted for a pressing report; but to eliminate having to go through this again next year I will keep working on implementing your VB...

Thanks again for all the help!!