By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,347 Members | 2,091 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,347 IT Pros & Developers. It's quick & easy.

3 Questions on Optimizing dependent queries in AccessXP

P: n/a
I have an appointment scheduling application with a number of queries
that filter through a table with many thousand appointments and comes
up with one days schedule. I need to start out by finding the
appointments I need to view in the calendar for one staff person. I
wanted to know the best order to put my queries in. I need to have
queries dependent on each other for my display (since the data is
fairly normalized). I have three questions then.

***** First, should I, in principle try to return the smallest record
set with the first query, or run two queries to filter the data.
"Appt.ID" is an autonumber, primary index for the table "appt". I could
simply run this as

qApptFltr:

SELECT appt.ID, appt.Date, appt.[StaffNumber]
FROM appt
WHERE (((appt.Date)=[forms]![Global]![Date]) AND
((appt.[StaffNumber])=[forms]![Global]![Staff]));

But would it be faster if I ran:

qQueryA:
SELECT appt.ID, appt.Date, appt.[StaffNumber]
FROM appt
WHERE (((appt.Date)=[forms]![Global]![Date]));

Followed by qApptFltr:

SELECT appt.ID, appt.Date, appt.[StaffNumber]
FROM qQueryA
WHERE (((appt.[StaffNumber])=[forms]![Global]![Staff])));
**** Second, I always have been told to return as few rows as possible
from a query. If I need the data from the rows to run joins to get my
display data should I simply add all the fields I need to one of the
above queries, or should I run the query first, then JOIN with the
items I need from the full table as in the example below?

SELECT appt.*
FROM ApptFltr INNER JOIN appt ON ApptFltr.ID = appt.ID;

**** Finally, after getting all the data I need, should I generate a
form display by using a large query to present the data from each
appointment, using multiple JOINS; or should I just load the data into
a form with lots of combo boxes to display the appropriate data?
Clearly the latter is most useful when the data needs to be edited, but
at that point I only have one record, but what would be fastest for a
continuous form view?

Thanks in advance.
Jim M

Sep 6 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.