Transpose in access??

[SoniaS]

Hey,

This is not really a transpose, i think.
I don't have much practical experience with Access database so please help with the simplest solution..

I have a table with some data and I want to convert (or create a new one) table into a different format.

my table has the following format...

serial_n 100 500 1000
95x45 1 2 0.9
96X15 3 0.1 0.5


change to this format
serial_no (these two columns will have new headings)
95x45 100 1
95x45 500 2
95x45 1000 0.9
96x15 100 3
96x15 500 0.1
96x15 1000 0.5

I hope, I have explained it well...

Sonia

 

[neileg]

You need to create 3 select queries, one for each column, 100, 500 and 1000. Then create a union query to join them all together.

What you will end up with is the structure that your table should have had in the first place. This is called normalisation.

 

[SoniaS]

Thank you for your reply.

I have tried that, but how to I do a union for 3 queries. It might seem like a stupid question but I don't knw much about access.

Also am not sure if I will get the structure I want from doing this.

my table has the following format...

serial_n 100 500 1000
95x45 1 2 0.9
96X15 3 0.1 0.5


change to this format

serial_no (these two columns will have new headings)
95x45 100 1
95x45 500 2
95x45 1000 0.9
96x15 100 3
96x15 500 0.1
96x15 1000 0.5

Thanks for your help...
Sonia

 

[SoniaS]

I managed to do a union on 3 queries.

The problems are, first of all I have about 15 columns so do i have to do 15 select queries and then UNion on all of them?

Second problem, at the moment I am only getting 2 columns, one with serial_no and the other with data (1, 2, 0.9 ..).

I also want my orginal column headings to appear in a new column. In the example below i want
100
500
1000

thanks!!!!!!!

my table has the following format...
serial_n 100 500 1000
95x45 1 2 0.9
96X15 3 0.1 0.5


change to this format

serial_no (these two columns will have new headings)
95x45 100 1
95x45 500 2
95x45 1000 0.9
96x15 100 3
96x15 500 0.1
96x15 1000 0.5

Thanks for your help...
Sonia

 

[neileg]

OK, the problem is that your structure is wrong. You need to read up on normalisation. The ironic thing is that the query you want to construct is the way your table should be constructed in the first place. If you have 15 columns (known as a repeating group) then you will need 15 select queries, but i suggest that you do this once and for all and create a new table in the proper normalised form.

To get your column heads in the select query, you need to add a calculated field. So for the "500" category, you would add in the query grid, in the Field Name cell of a blank column:-
Category:500

This will create a new Field called Category which will be set to 500 for every record. So in the query for "1000" it would be:-
Category: 1000
and so on.

If you do take my advice a nd create a new table structure, instead of using a union query, I would simple append each select query in turn to the new table.

 

[SoniaS]

Thanks for all your help..

I can't change the orginal structure as my boss wants it in that format for drawing graphs etcc....

But I need the database to be in this different format(normalised) for a software which i will run on the database. The software requires the data to be in a normalised format.

Anyway, I guess I will have to do 15 select queries and then a union. It shouldn't be too much work, hopefully. (I have 3 tables so only 45 select queries

Thank you for all your help.

 

[Jon K]

my table has the following format...

serial_n 100 500 1000
95x45 1 2 0.9
96X15 3 0.1 0.5

I managed to do a union on 3 queries.

You can do a union query directly on the table:-

SELECT serial_n, 100 AS Category, [100] as TheValue FROM [TableName]
UNION
SELECT serial_n, 500, [500] FROM [TableName]
UNION
SELECT serial_n, 1000, [1000] FROM [TableName];
.

 

[neileg]

Thanks for all your help..

I can't change the orginal structure as my boss wants it in that format for drawing graphs etcc....

But I need the database to be in this different format(normalised) for a software which i will run on the database. The software requires the data to be in a normalised format.

Anyway, I guess I will have to do 15 select queries and then a union. It shouldn't be too much work, hopefully. (I have 3 tables so only 45 select queries

Thank you for all your help.

Your boss understands database design? In any case you can create a query which turns your normalised structure back to the form you have now. Your boss can use the query as a datasource for the graphs instead of the table. Never let users dictate design - if they are that clever, let them build the database.