468,268 Members | 1,695 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,268 developers. It's quick & easy.

3 Questions on Optimizing dependent queries in AccessXP

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


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

But would it be faster if I ran:

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
0 1002

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Bob Hynes | last post: by
6 posts views Thread by peter_k | last post: by
reply views Thread by Miguel Perez | last post: by
5 posts views Thread by John Rivers | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.