How to create a virtual table...?

[jalbao]

is it possible to use sql to create a virtual table? when i say virtual table, i'm not referring to a View. So what do I mean then? I'll attempt to explain:

i have two tables; tblTest and tblTestData. The table tblTest consists of two fields; lTestID (the primary key) and sTestName (the name of a test). The second table, tblTestData, consists of three fields; lTestData (numerical values that represent test values), lTestID (foreign key) and dtAdded (date/time when the value was entered).

As you can tell, tblTest and tblTestData create a one-to-many relationship where one test can have many different values (like a classroom setting where on one test the kids will have different scores).

Well, I need to grab data from the two tables to create a virtual table. The following is the virtual table that i need:

Let's call the virtual table vTable: vTable will have n amount of fields - these fields will be the names of all of the tests taken from the sTestName field in the tblTest table. Then all of the test data (taken from the lTestData field in the tblTestData table) will be stored in the 'column' that the data belongs to. Just to clarify, this new virtual table will now be thought of in terms of 'columns' of data rather than the typical 'rows' of data. Remember, each column or field will represent a test, and all the data (or referring to the classroom, the scores) will be listed directly below the corresponding test in the same column.

This is different that a sql View in that when you create a view you have to specify the columns (field) that you want to use in the SELECT statment.. the table that I want to create will be gathering the column names NOT from other columns in other tables, but rather the column names will be gathered from row values found in the tblTest table.


i hope i explained it well enough to be understood... i'm horrible when it comes to communication.

anyway, i'm thinking that sql can do this but on the other hand, maybe not. If someone can point me in the right direction or if you know for a fact that this is impossible, i'd appreciate it..

thanks

 

[tlbroadbent]

It appears that you have described a crosstab query. Check Access help topic "What is a crosstab query and when would you use one?"

Let me know if I've misunderstood you're requirement. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Cagliostro]

Yes it is:
select t.a, t.b, t.* from (select ddd as a, rrr as b, ccc,xxx from sometable) as t
where t.xxx=somecondition..... John Fill

ivfmd@mail.md

 

[jalbao]

thanks for the response all...

in reply to JohnFill- i'm not clear on what your query is gathering. In other words, what is the first SELECT doing and what is the second SELECT doing? Is the first SELECT "creating" the virtual columns? Or is it the second SELECT that is accomplishing that?