Help to optimize qureries

[Aino]

I have an Access database with a number of forms and reports, that all
work pretty fast - except one report based on a form and a series of
queries. I was going to accept this, but after splitting the database
in a front- and a backend, this specific function won't work any
longer, and searching on the error message I found it was due to too
complicated queries.

Since I am not that strong in SQL, and was satisfied when they gave
the correct result, they are probably far from optimal, and here I
need help.

I have a form with 3 inputfields: cmbDay (e.g. Monday), txtFrom (e.g.
10:00) and txtTo (e.g. 11.30). When pressing OK I execute a report
based on the first sql qryAvailCars:

SELECT V1.Car, V1.TimeMax, V1.AdressMax, V2.TimeMin, V2.AdressMin
FROM
(SELECT DISTINCT L1.Car, TimeMax, AdressMax
FROM
(SELECT Car
FROM qryAvailTimes)
AS L1 LEFT JOIN
(SELECT Car, TimeMax, AdressMax
FROM qryAvailTimes L2
WHERE TimeMax =
(SELECT MAX(TimeMax) FROM qryAvailTimes L3 WHERE TimeMax
<= Forms!FindAvailCar.txtFrom AND L2.Car = L3.Car))
AS L4
ON L1.Car = L4.Car) AS V1 LEFT JOIN
(SELECT DISTINCT L5.Car, TimeMin, AdressMin FROM
(SELECT Car
FROM qryAvailTimes)
AS L5 LEFT JOIN
(SELECT Car, TimeMin, AdressMin
FROM qryAvailTimes L6
WHERE TimeMin =
(SELECT MIN(TimeMin) FROM qryAvailTimes L7 WHERE TimeMax

= Forms!FindAvailCar.txtTo AND L6.Car = L7.Car))

AS L8
ON L5.Car = L8.Car) AS V2 ON V1.Car = V2.Car;


As can be seen, this is dependent on a second query qryAvailTimes:

SELECT *
FROM qryTimes
WHERE Day=Forms!FindAvailCars.cmbDay
AND NOT Car IN
(SELECT Car
FROM qryTimes
WHERE TimeMax > Forms!FindAvailCars.txtFrom
AND TimeMin < Forms!FindAvailCars.txtTo
AND Day = Forms!FindAvailCars.cmbDay);


This is again relying on a third query qryTimes:

SELECT DISTINCT TMin.Day, TMin.Car, TMin.Routenumber, TMin.TimeMin,
TMin.AdressMin, TMax.TimeMax, TMax.AdressMax
FROM
(SELECT T1.Day, T1.Car, T1.Routenumber, T1.Adress AS AdressMin,
TimeMin
FROM [qryTourlist] AS T1 INNER JOIN
(SELECT DISTINCT Day, Car, Routenumber, Min(Time) AS TimeMin
FROM [qryTourlist]
GROUP BY Day, Car, Routenumber) AS T2
ON (T1.Day=T2.Day) AND (T1.Car=T2.Car) AND
(T1.Routenumber=T2.Routenumber) AND (T1.Time=T2.TimeMin)) AS TMin,
(SELECT T3.Day, T3.Car, T3.Routenumber, T3.Adress AS AdressMax,
TimeMax
FROM [qryTourlist] AS T3 INNER JOIN
(SELECT DISTINCT Day, Car, Routenumber, Max(Time) AS TimeMax
FROM [qryTourlist]
GROUP BY Day, Car, Routenumber) AS T4
ON (T3.Day= T4.Day) AND (T3.Car=T4.Car) AND
(T3.Routenumber=T4.Routenumber) AND (T3.Time=T4.TimeMax)) AS TMax
WHERE TMin.Day=TMax.Day AND TMin.Car=TMax.Car AND
TMin.Routenumber=TMax.Routenumber
ORDER BY T1.Day, T1.Car, T1.Routenumber;


All of these queries are used solely for this one function, but the
last query in the chain is used in several other reports as well,
qryTourlist:

SELECT X.Tour_ID, K.Name, K.Remark, R.Car, U.Day_ID, U.Name AS Dag,
N.Routenumber, R.ChangeDate AS RouteChangeDate, X.TourChangeDate,
X.Time, X.Action, X.Code1, X.Code2, X.Adress, X.Krak, X.Phone1,
X.Phone2, X.Phone3, X.Changed
FROM
(SELECT T.Tour_ID, T.Customer_ID, T.Route_ID, T.ChangeDate AS
TourChangeDate, T.Time, T.Action, T.Adress, T.Krak, T.Phone1,
T.Phone2, T.Phone3, T.Changed, H.Code1, H.Code2
FROM
(SELECT S.Tour_ID, S.Customer_ID, S.Route_ID, S.ChangeDate,
S.Time, S.Action, S.Changed, A.Adress, A.Krak, A.Phone1, A.Phone2,
A.Phone3
FROM
(SELECT Tour_ID, Customer_ID, Route_ID, ChangeDate,
Passiv, OnTime AS Time, FromAdress_ID AS Adress_ID, FromType AS Type,
"On" AS Action, IIF(Resetdato IS NOT NULL, "x", NULL) As Changed
FROM Tours
UNION ALL
SELECT Tour_ID, Customer_ID, Route_ID, ChangeDate, Passiv,
OffTime, ToAdress_ID, ToType, "Off", IIF(Resetdato IS NOT NULL, "x",
NULL)
FROM Tours) AS S,
(SELECT H.Home_ID AS Adress_ID, H.Adress, H.Krak,
H.Phone1, H.Phone2, H.Phone3, -1 AS Type
FROM Home H
UNION ALL
SELECT I.Institution_ID, I.Adress, I.Krak, I.Phone1,
I.Phone2, I.Phone3, 0 AS Type
FROM Institutioner I) AS A
WHERE S.Type = A.Type AND S.Adress_ID = A.Adress_ID AND
S.Passiv = FALSE) AS T LEFT JOIN
(SELECT H1.Customer_ID, H1.Code1, H2.Code2
FROM
(SELECT H.Code AS Code1, K.Customer_ID
FROM Handicaputils H, Customers K
WHERE H.Handicap_ID = K.Handicap1_ID) AS H1 LEFT JOIN
(SELECT H.Code AS Code2, K.Customer_ID
FROM Handicaputils H, Customers K
WHERE H.Handicap_ID = K.Handicap2_ID) AS H2
ON H1.Customer_ID = H2.Customer_ID) AS H
ON T.Customer_ID=H.Customer_ID) AS X, Customers AS K, Routes AS R,
Routenumbers AS N, Days AS U
WHERE X.Customer_ID=K.Customer_ID AND X.Route_ID=R.Route_ID AND
R.Day_ID=U.Day_ID AND R.Routenumber_ID=N.Routenumber_ID
ORDER BY R.Car, U.Day_ID, N.Routenumber, X.Time, X.Action, K.Name;


All these linked queries seem overly complicated, but I don't know how
else to get my results. And they do work, just slowly, and not in a
shared database.

Any help will be appreciated!
Best regards
Aino