Complex Query?

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

.

Relevant Pages

  • Re: Complex Query?
    ... I do not understand what you mean by, for example "PID Table Details for Venkat". ... I saw your reply to arun on the subject "Dynamic Query in Ms-Access" ...
    (microsoft.public.access.formscoding)
  • Complex Query?
    ... I saw your reply to arun on the subject "Dynamic Query in Ms-Access" ... Assume the User Queries for NAme = Venkat ... Block1 is PID ...
    (microsoft.public.access.formscoding)
  • Re: Complicated Query
    ... It might be important for me to mention that PID is not my Primary ... I would like to sort out and find all the ... one entry, and currently meet the appropriate status criteria. ... I have gotten the query to work for the ...
    (microsoft.public.access.queries)
  • Re: Help on SQL:Selecting most frequent records
    ... The ranking query only assigns a rank number based on the ... WHERE PID = Forms!someform.cboPhysicians ... >Its a good solution,but since we want to work on prescribers one by one,i ... >> FROM qryDIDcounts AS T ...
    (microsoft.public.access.formscoding)
  • Re: How to find which page does a record available in Grid
    ... I think u didnt get my query. ... Say a grid contains following columns ... Arun, 22, ComputerScience ...
    (microsoft.public.dotnet.languages.csharp)
Loading