Table transposition - how to

[Adams]

Dear Access users!



I am a brand new Access user. Please help me with some problem with table.

I have in Excel table like that:

A1.
Type	Name	Date1	Date 2	Date3	Date4	Date5
Type1	Name1	11	22	36	53	66
Type2	Name2	12	23	37	54	67
Type3	Name3	13	24	38	55	68
Type4	Name4	14	25	39	56	69
Type5	Name5	15	26	40	57	70
Type6	Name6	16	27	41	58	71
Type7	Name7	17	28	42	59	72
Type8	Name8	18	29	43	60	73
Type9	Name9	19	30	44	61	74

and I want to change it to 1 dimension table to create it to pivot table aslo in Excel.
Table no 2 should looks like below:

A2.
Type	Name	Date	Vol
Type1	Name1	Date 1	11
Type1	Name1	Date 2	22
Type1	Name1	Date 3	36
Type1	Name1	Date 4	53
Type1	Name1	Date 5	66
Type2	Name2	Date 1	12
Type2	Name2	Date 2	23
Type2	Name2	Date 3	37
Type2	Name2	Date 4	54
Type2	Name2	Date 5	67
Type3	Name3	Date 1	13
Type3	Name3	Date 2	24
Type3	Name3	Date 3	38
Type3	Name3	Date 4	55
Type3	Name3	Date 5	68

How to do that in Access?

If you can help me with that problem I will be glad. Thanks in advance!!

Regards
Peter

[Dal Jeanis]

1) Import the first table into Access with this layout

tblTempImport
   TempType
   TempName
   TempVol1
   TempVol2
   TempVol3
   TempVol4
   TempVol5

2) Create the destination table

tblVolInfo
   VolID        Autokey
   VolType      
   VolName
   VolDate
   VolNbr

3) Insert the data

INSERT INTO tblVolInfo
(VolType, VolName, VolDate, VolNbr)
SELECT TempType TempName, "HardcodeDate1", TempVol1
FROM tblTempImport;

INSERT INTO tblVolInfo
(VolType, VolName, VolDate, VolNbr)
SELECT TempType TempName, "HardcodeDate2", TempVol2
FROM tblTempImport;

etc.

[Adams]

1) Import the first table into Access with this layout

tblTempImport
   TempType
   TempName
   TempVol1
   TempVol2
   TempVol3
   TempVol4
   TempVol5

2) Create the destination table

tblVolInfo
   VolID        Autokey
   VolType      
   VolName
   VolDate
   VolNbr

3) Insert the data

INSERT INTO tblVolInfo
(VolType, VolName, VolDate, VolNbr)
SELECT TempType TempName, "HardcodeDate1", TempVol1
FROM tblTempImport;

INSERT INTO tblVolInfo
(VolType, VolName, VolDate, VolNbr)
SELECT TempType TempName, "HardcodeDate2", TempVol2
FROM tblTempImport;

etc.

Thank you very much for your fast replay!
I am realy noob of Access. Can you describe me more precisly how to make that step?

I can import xls file form the excel by import options. I don't knw how to import with some layout. My xls file has 188 rows and 188 columns so I cant transpone it in excel

could you find some time to describe me your solution more precisly like for a totaly beginner user of Access?

Thanks any way for your time and solution!

Peter

[Dal Jeanis]

Import it the way you understand. That will create a table. Whatever names that the import gives the fields, you can either use Table Design view to change the field names, or you can change the SQL code above to use the names the system gave the columns.

Just don't get intimidated by any of this - you can plunk away at it and learn one technique at a time until it's done. Just get in the habit of backing up your database after every successful step. I only make a total hash of things 2-3 times out of a hundred, but I might not figure it out until after three more steps...

The other good habit to develop is this - do all your testing and development in a junk copy of your database, then, when you think you have it right, take a new test copy of the production database and make only the changes you think you need. Then test your work thoroughly and professionally. If you don't do that, then often you will find that there was something else you did to the database while testing, that you forgot to UNDO.