Reverse Crosstab

[CrissyF]

I have an Excel spreadsheet that has dates across the horizontal axis (Xaxis)
and part numbers descending down on the vertical axis (Y axis). It looks
almost exactly like the results of a crosstab. Here is a brief example:

Part 200806 200807 200808
1 $10 12$ $13
2 $10 12$ $13
3 $10 12$ $13

And I need it to look like this:
Part Date Value
1 200806 $10
1 200807 $12
1 200808 $13
2 200806 $10
2 200807 $12
2 200808 $13

Hard coding in VB for each individual part number is not an option because
there are atleast 500 part number that are 21 Alpha-numeric in length. Also,
the date spread is from 200806 till 201212, a very wide date range. I posted
this in access because I figured that there would be something easy to do
this with. We've looked at some of the other threads for reverse Crosstab
and don't believe this would work for our situation. VB and C++ knowledge is
very limited so please be forgiving! Any help would be greatly appreciated!

 

[Allen Browne]

Execute a series of Append query statements (one for each date header), to
append the data to your target table.

This will involve writing some code to:
a) Loop through the Fields of the TableDef, so you can get the name of each
one.
b) In the loop Exeute a SQL string to append the data.

For an example of how to loop the Fields of the TableDef, see:
http://allenbrowne.com/func-06.html

For an example of executing a SQL string, see:
http://allenbrowne.com/ser-60.html

 

[CrissyF]

I'm sorry but this answer is a little beyond me. I am not sure what to do
with the code you provided (what needs to be customized in it, where to put
it, what it does). Is there a simplier way of acheiving the results I need,
or can you give me more info on what I need to do with the codes.

Also, when I click on the second link it takes me to directions on how to
supress error messages not an example of executing a SQL string.

Thank you for your help.

Crystal

 

[Peter Hibbs]

If this is a one off operation then the Excel to Access Converter
Utility program at :-
http://www.rogersaccesslibrary.com/...p?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4
may be able to do it for you.

HTH

Peter Hibbs.

 

[Allen Browne]

Okay, if you are not able to code it (or if it is a one-off operation):

1. Create the target table with the fields you originally showed.

2. Link the Excel spreadsheet: File | Get External | Link.

3. Create a query using the linked spreasheet as the source table.

4. Change it to an Append query (Append on Query menu.)
Access will ask what table to append to, and add an Append row to the query
design grid.

5. Drag the Part field into the grid.
Choose the matching field in the Append row under this.

6. Type this into the Field row:
TheDate: 200806
Choose the date field in the Append row under this.

7. In the field row, choose the 200806 field.
In the Append row under this, choose your Value field.

8. Run the query (Run on Query menu.)
Access will probably ask you to confirm adding these rows to the table.

9. Change the number beside TheDate (from step 6 above) and the column you
chose (at step 7) so they both read 200807. Then run the query again, so it
adds the next column.

10. Repeat step 9 until you have added all the fields.