General Guidance

  • Thread starter Thread starter Lloyd
  • Start date Start date
L

Lloyd

I have been working on an application for some time and
realize that I need some help in understanding queries.
Users may have different views on these questions,
depending on their development style, but here are some
that come to mind;

1) I am using multiple table queries as a source for
forms. In general does one include all the fields from
tables or be more selective? (When I am more selective,
I'm always having to go back and add another field to the
query.)

2) Is there value to having a few queries that have all
the linked tables and fields and using them for multiple
forms or should I be creating a separate query for each
form, even if the form doesn't require specific sorts
and/or selections?

3) Must the query source of a subform have only data that
is in the query for the calling form?

4) Anyone know of a good resource that discusses issues
such as these?

Thanks, Lloyd
 
I have been working on an application for some time and
realize that I need some help in understanding queries.
Users may have different views on these questions,
depending on their development style, but here are some
that come to mind;

1) I am using multiple table queries as a source for
forms. In general does one include all the fields from
tables or be more selective? (When I am more selective,
I'm always having to go back and add another field to the
query.)
Click to expand...

For good performance and reliability, include ONLY the fields that you
will need. In general, it's rare to use a multitable query as the
Recordsource for a form; it's much more common to use a single table
as the recordsource, and use one or more Subforms for the "many" side
related tables. Lookup fields are best handled by selecting the
numeric ID in the form's recordsource, and putting a Combo Box on the
form, based on the lookup table.
2) Is there value to having a few queries that have all
the linked tables and fields and using them for multiple
forms or should I be creating a separate query for each
form, even if the form doesn't require specific sorts
and/or selections?
Click to expand...

A specific query for each form. A Query containing all your tables
will a) be VERY inefficient and b) almost certainly not be updateable.
3) Must the query source of a subform have only data that
is in the query for the calling form?
Click to expand...

On the contrary: it should NOT have any data from the "calling" (more
properly, the parent) form. You'ld base the parent form on the "one"
side table of a one to many relationship, or a query selecting the
needed fields and/or records from that table; the Subform would be
based on the "many" side table (or a query based on that table). The
Master/Child Link Field of the Subform control would be the joining
fields of the related tables.
4) Anyone know of a good resource that discusses issues
such as these?
Click to expand...

There are many. "Access XP Inside Out" by John Viescas is one of
several good references.

John W. Vinson[MVP]
(no longer chatting for now)
 
Back
Top