Multiple 'Many' Table Query

[Guest]

In my database I have a small Main table which houses a record per
individual, and then links to 5 tables with 'many' records each which
represent different types of characteristics for each individual, their
personality traits, experiences, etc.. I have written a form which allows the
user to select value for each or any combination of the 5 charactertics
(housed in individual tables). I have the form field names as parameters in
my query and this works when tested just one field at a time. I can't get
the query to work when I try to use all 5 parameters. I want the the form
field variables to seems invisible if the user didn't populate them with a
value. I have tried setting them to null,"". I have each of the 5 tables
linked to main with an autonumber key. I perhaps am confused about the
construction of my query - criteria, and versus or?? I query the 5 fields in
the five tables in the query -- but the form will not necessarily populate
all 5 fields with 'real' values. I hope this makes sense.
Any help appreciated.
C Emmons

 

[Allen Browne]

The simplest way to build this form might be to use subforms.

1. Create a new blank form, with its Record Source set to the main table.

2. Before you put any text boxes on the form, add a Tab control from the
Toolbox (View menu.)

3. In the first page of the tab control, place all the fields from the main
table.

4. In the second page of the tab control, place a *subform* bound to the
first related table (characteristics?)

5. Right-click the top of the tab control to insert another page. In this
page, add another subform for the next related table.

6. Repeat step 5 until you have 6 pages in your tab control (one for the
main table, and 5 with subforms.)

Since each form is bound to just one table, you avoid the whole issue of
uneditable multi-table queries.

At some point (e.g. for reports), you will still need to create a query that
returns records even when not all the related tables have a value. The trick
is to use outer joins, and watch your criteria. Both those issues are
explained in this article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Guest]

Thanks so much for your reply. I have a form with subforms as you describe
already created for adding records and it works fine. My problem is in my
form to query the data. I have small form with a search possibility for
each of my five parameters (which happen to be in the 5 separate tables). In
the actual query, for my criteria I reference each of the form fields -
[forms]![queryform]![field1], field2-field5 ... . It works as long as all 5
fields are populated. I want to set all 5 fields to whatever is appropriate
to show all records - unless a value is specified for the given field. I
have tried setting field to '*', etc.. For instance, I want the user to be
able to specify, field1=abc123, and leave fields2-5 unspecified in the
form.However, the query has [forms]![queryform]![field(1-5)] parameters in
place for all fields all the time. I want the not specified fields to show
'all 'records -- like setting a criteria to 'any'. I'm sorry to not be able
to explain more clearly. Thanks for your help. I have 5 fields, but I would
like to offer the 'any of these' option to any given field.
C Emmons

 

[Allen Browne]

Use code to build a query string to assign to the RecordSource of the main
form.

The string will either be just the main table (if use user only used
criteria from the main table), or use an INNER JOIN to the related table if
you need to query on fields from there as well. Include the DISTINCT keyword
in the string to eliminate duplicates.

For an example of how to build the INNER JOIN string, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html
I understand you don't have a subform, but the technique of setting the
RecordSource is the same as explained in the middle of that article.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks so much for your reply. I have a form with subforms as you
describe
already created for adding records and it works fine. My problem is in my
form to query the data. I have small form with a search possibility for
each of my five parameters (which happen to be in the 5 separate tables).
In
the actual query, for my criteria I reference each of the form fields -
[forms]![queryform]![field1], field2-field5 ... . It works as long as all
5
fields are populated. I want to set all 5 fields to whatever is
appropriate
to show all records - unless a value is specified for the given field. I
have tried setting field to '*', etc.. For instance, I want the user to
be
able to specify, field1=abc123, and leave fields2-5 unspecified in the
form.However, the query has [forms]![queryform]![field(1-5)] parameters in
place for all fields all the time. I want the not specified fields to
show
'all 'records -- like setting a criteria to 'any'. I'm sorry to not be
able
to explain more clearly. Thanks for your help. I have 5 fields, but I
would
like to offer the 'any of these' option to any given field.
C Emmons

The simplest way to build this form might be to use subforms.

1. Create a new blank form, with its Record Source set to the main table.

2. Before you put any text boxes on the form, add a Tab control from the
Toolbox (View menu.)

3. In the first page of the tab control, place all the fields from the
main
table.

4. In the second page of the tab control, place a *subform* bound to the
first related table (characteristics?)

5. Right-click the top of the tab control to insert another page. In this
page, add another subform for the next related table.

6. Repeat step 5 until you have 6 pages in your tab control (one for the
main table, and 5 with subforms.)

Since each form is bound to just one table, you avoid the whole issue of
uneditable multi-table queries.

At some point (e.g. for reports), you will still need to create a query
that
returns records even when not all the related tables have a value. The
trick
is to use outer joins, and watch your criteria. Both those issues are
explained in this article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html