Complex Query?

[sa.venkateswaran]

hi all,

I saw your reply to arun on the subject "Dynamic Query in Ms-Access"

I find 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
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 details with the coressponding
block details) for all records

 

[Steve Schapel]

sa.venkateswaran,

Is there any chance you can review your database design? If you
continue with the existing structure of MASTER_TABLE, you will end up
with big headache. There should only be one Block field, and multiple
records for each person, rather than multiple fields for block.

By the way, 'name' is a "reserved word" (i.e. has a special meaning) in
Access, and as such should not be used as the name of a field.

Also, you will really need a Blocks table to relate the 40 block types
tables to. In fact, it is likely that all should be in one table - but
that depends on the degree of differences between the blocks and the
fields requireed to describe them. But at least all the "common fields"
should definitely be in the one table.

I do not understand what you mean by, for example "PID Table Details for
Venkat". Do you mean that for Venkat there will be specific PID
information?