Help on query

I

ISSB Programmer

I am working on a report that uses the following column: "Number of teachers
currently in same school". In my database I have a table that tracks the
schools that each teacher worked at and the start and end dates that they
worked there. To find out if a teacher is currently at the same school I
created the following 5 queries:

Query 1: Using the Min function, find the first start date for each teacher.

Query 2: Query query 1 to find out what school each teacher was working at
on their first start date.

Query 3: Find the school that each teacher is working at currently. This is
determined by a Null end date.

Query 4: Compare Query 2 and Query 3 to find which teachers are still at the
same school.

Naturally I find this very inefficient. This is for only one column in the
report. I have 7 reports to do so far. Is this the best way to do it? Thanks.
 
M

[MVP] S.Clark

You could do subqueries to be more 'efficient', but then debugging becomes
an issue. Stick with what you have.

Explain your last two sentences in more detail, as it doens't make sense in
relation to the info preceding it.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
I

ISSB Programmer

Steve,

Thanks for your help. The last two sentences aren't important. I just wanted
to show that if I have to do 4 queries for one column in one report then I
could average 10 to 15 queries for each report and I can't imagine having 70
queries in one mdb.
 
I

ISSB Programmer

Steve,

Could you please explain how to use a subquery for my situation? I couldn't
figure out a way to use a subquery to pull the information for the first
school and the current school for each teacher. If I use the Min function in
a subquery the query pulls the teacher with the earliest start date instead
of the earliest start date for each teacher. Thanks.

Wade
 
Top