Complex Query?

[Venk]

hi all,

I saw one reply to arun on the subject "Dynamic Query in Ms-Access" by
one Mr Rick

I found it very useful.
Now to extend this solution forward I have the following situation.

I have a Query Which Retrives one or more records from the following
table(MASTER_TABLE) based on the user requirement

MASTER_TABLE (sample. In actual 20K records)
Name Block1 Block2 Block3... Block8
venkat PID AIN AOUT CHARC
Kamal MATH SIGSEL INPUT GET
John AIN PID
Arun PID MATH SEL SIGSEL

Assume the User Queries for NAme = Venkat
then the Query Result will be

QUERY_MASTER_TABLE
Name Block1 Block2 Block3... Block8
venkat PID AIN AOUT CHARC

----

Now I want to Read the Information of this record present in BLOCK1 to
BLOCK8 columns
In this case,
Block1 is PID
Block2 is AIN
Block3 is AOUT
....
Block8 is CHARC

----

Now, I have seperate tables for every unique block type. There are
totally 40 Unique blocks available
AIN
PID
AOUT
CHARC
MATH
SIGSEL
INPUT
GET etc.

And each table has come common fields and its own different fields.
there is a relation between the MASTER_TABLE and these TABLES on one
column (PARENT in the Block Table corresponds to the Name in the Master
table)
----
Now I want the result of the query to show the following

Assume the User Queries for NAme = Venkat
then the Query Result will be

QUERY_MASTER_TABLE
Name Block1 Block2 Block3... Block8
venkat PID AIN AOUT CHARC

followed by
PID Table Details for Venkat (I will select the specific parameters
required from PID Table initially)
AIN Table Details for Venkat (I will select the specific parameters
required from AIN Table initially)
AOUT Table Details for Venkat (I will select the specific parameters
required from AOUT Table initially)
CHARC table Details for Venkat (I will select the specific parameters
required from CHARC Table initially)

Similarly if the user queries on a different name say "Arun"

QUERY_MASTER_TABLE
Name Block1 Block2 Block3... Block8
Arun PID MATH SEL SIGSEL

I should get the details of
PID table details
PID Table Details for Arun (I will select the specific parameters
required from PID Table initially)
MATH Table Details for Arun (I will select the specific parameters
required from MATH Table initially)
SEL Table Details for Arun (I will select the specific parameters
required from SEL Table initially)
SIGSEL Table Details for Arun (I will select the specific parameters
required from SIGSEL Table initially)
This information should come in Report format
With the first page with the details of the master table
and the subsequent pages with the details of each block for the
particular record.

The database is quite Huge (40MB so far) and the details of each block
table is also huge
The master Query can return Multiple records in which case, I will have
to show similar details (master table record details with the
coressponding block details) for all records
Any help will be appreciated.

Thanking all of you in advance

Regards
venk

[pietlinden]

Sounds like a bad design.
If you had a table of (StudentName, BlockNumber, Class) then you could
do a crosstab to give you what you want. This design is going to be a
headache. I would rethink it. Try querying for simple things. If you
can't do it easily, then something's definitely wrong.

[Venk]

hi!

thanks for your reply.
Unfortunately this database is a backup format of a system. this is
auto generated. hence i have to live with this data in this format.
and reworking on the data is also time consuming. hence this situation

regards
venk

pi********@hotmail.com wrote:

Sounds like a bad design.
If you had a table of (StudentName, BlockNumber, Class) then you could
do a crosstab to give you what you want. This design is going to be a
headache. I would rethink it. Try querying for simple things. If you
can't do it easily, then something's definitely wrong.