Create a table Query

[Libra79]

HI,

How can I create a table form a query but that the fields in the query are transfered to the field name in the table.

Example : the query has one column field called numbers: 1 2 3 4 5

Then a table based on that query will have the fields 1 2 3 4 5 as field names so I can store data in the fields.


Thanks,

[Stewart Ross]

Hi Libra79. This sounds very like an assignment question. If it is, please read our posting guidelines and the explicit FAQ on homework questions for our site rules. I can provide some guidance as to how to approach this problem, but working on the assumption that what you ask is indeed a homework question you will understand that I cannot provide a solution for you.

Your requirement is to 'pivot' the row data to become columns in a new table. There is no one step way to do this. However, it is possible by doing the following:

create a dummy crosstab query using your numbers field several times, once as a row source, once as a column header, and once as a calculated field in a dummy capacity (it does not matter much what calculation is involved - but if you wish to retain the data type correctly you should use max or min).

This will give you the same number of rows, with the column headings being the values.

Create a new query based on your crosstab query, selecting only the column header fields. Make this query a create table query and run it to create a new table which will have the correct column headings, but of course contain the dummy totals from the first query.

Finally, delete the row data from the new table and enter whatever values you need in your new table.

I hope this provides sufficient guidance without in any way doing the work for you.

-Stewart

ps I have linked two screenshots, one showing a table called numbers storing text values, and the other the result of the create table crosstab before deletion of the row data.

[Libra79]

Hi,

Thanks this helped a lot.

And it's actually not a homework. I am developing a database that has fields related to features for an automobile business.I needed to do this so for every feature I can start saving data from the production lines but I had the info on a table as stored data and I needed it to be as a table to store the data based on that info.

Thanks,

Hi Libra79. This sounds very like an assignment question. If it is, please read our posting guidelines and the explicit FAQ on homework questions for our site rules. I can provide some guidance as to how to approach this problem, but working on the assumption that what you ask is indeed a homework question you will understand that I cannot provide a solution for you.

Your requirement is to 'pivot' the row data to become columns in a new table. There is no one step way to do this. However, it is possible by doing the following:

create a dummy crosstab query using your numbers field several times, once as a row source, once as a column header, and once as a calculated field in a dummy capacity (it does not matter much what calculation is involved - but if you wish to retain the data type correctly you should use max or min).

This will give you the same number of rows, with the column headings being the values.

Create a new query based on your crosstab query, selecting only the column header fields. Make this query a create table query and run it to create a new table which will have the correct column headings, but of course contain the dummy totals from the first query.

Finally, delete the row data from the new table and enter whatever values you need in your new table.

I hope this provides sufficient guidance without in any way doing the work for you.

-Stewart

ps I have linked two screenshots, one showing a table called numbers storing text values, and the other the result of the create table crosstab before deletion of the row data.