How to make query results into column headings

[dugoneill]

Is it possible to run a query against one table which will have one distinct result set, and then use that result set as the column headings within another query?

 

[RainLover]

The answer to the first part of the question is yes, and this can be done by setting the appropiate criteria in your query. So if you had a field of assending numbers and you wanted the record which contains the number 678 then simply put 678 in the criteria.

As far as the second part of your question I don not understand what you are trying to achieve. So if you could explain a little more then that may help.

 

[dugoneill]

Thanks for the quick reply.

Basically I think what I am trying to do is create a form with a subform.

We have a table with data in column 01, 02, 03, 04 --- 14 and an ID column.

The ID column references back to another table which identifies what the data in each column represents.

So If the ID field = 1, then the data in column 01 is First Name, column 02 is last name, column 03 is job title, etc.
If the ID field = 2, then the data in column 01 is Phone Number, 02 is fax number, 03 is Cell phone number

This is cross referenced against another table where it identifies which column has which data.

So this table has Column_hdr01, hdr02, hdr03
Each record in this table then labels what the column heading would be for the data in that column if that specifc record set (ID) is chosen.

So what I was thinking would be a query (form) that asks you to chose (select from drop down box perhaps) which ID record you are looking for, which in turn would pull the proper column headings from the table, and fill it with the data which was for this purpose.

The number of columns necessary is also different depending on which ID is select

And the result set of each needs to be queriable to report against and to narrow the result set of each ID, and also to allow input into the tables columns as well.

 

[RainLover]

This is not the way to design a database.

If you want a Form and SubForm situation create two Tables.

tblTableName with some fields like these examples

TableNameID as AutoNumber
AField as Text, or number etc
Another Field
etc

Then create another Table

TblSecondTable
SecondTableID as Autonumber
TableNameID as Long. This Field is the same as the Primary Key in the First Table
Additional Fields.

Now create two Forms, one for each Table.

On the first form insert the second form as a sub form then join the two fields via the Primary Key and the Foregin Key

Hope this helps.

 

[dugoneill]

This is not the way to design a database.

If you want a Form and SubForm situation create two Tables.

tblTableName with some fields like these examples

TableNameID as AutoNumber
AField as Text, or number etc
Another Field
etc

Then create another Table

TblSecondTable
SecondTableID as Autonumber
TableNameID as Long. This Field is the same as the Primary Key in the First Table
Additional Fields.

Now create two Forms, one for each Table.

On the first form insert the second form as a sub form then join the two fields via the Primary Key and the Foregin Key

Hope this helps.

Thanks, I will try this. And I agree it is not the way to create a database, unfortunately I am a small cog in a bigger wheel and am just trying to connect to the data they already have in my own Access Front End to make it work smarter for me at least.

 

[RainLover]

Let's know if I can help further