Hi,

I need to display summaries from several tables (some with only a few
records while others will have several thousands) in a form or report, with
grouping. I don't know which design would be better from the point of view of
the speed:
a) A single form based on a union query of all the tables.
b) sub-forms based on the individual tables, and linked to a main form with
which criteria can be entered.
c) Any other suggestion.

I have tried (a) and (b); both gave the required outputs but they have been
slow indeed, perhaps option (b) marginally faster. The largest table right
now has less than 1500 records, and the individual queries output fast
enough. But the forms based on them take eternity to load.

For example, the query below outputs instantly:

SELECT DISTINCT 1 AS TBID, Accounts.Account, Accounts.AccountID,
IIf([AccountID]=86,-(Nz(DSum("[DisposalValue]","aCapitalGains","[ACCode] = 86
AND [TDate]>=Forms!TBa1!SDate AND [TDate]<=Forms!TBa1!EDate AND
[Zone]=Forms!TBa1!Zone"),0)-Nz(DSum("[NBVDisposed]","aCapitalGains","[ACCode]
= 86 AND [TDate]>=Forms!Tba1!SDate AND [TDate]<=Forms!TBa1!EDate AND
[Zone]=Forms!TBa1!Zone"),0)),Nz(DSum("[DisposalValue]","aAssetsDep","[Disposed]>=Forms!TBa1!SDate
AND [Disposed]<=Forms!TBa1!EDate AND [Zone]=Forms!TBa1!Zone"),0)) AS TCa,
IIf([AccountID]=86,-(Nz(DSum("[DisposalValue]","aCapitalGains","[ACCode] = 86
AND [TDate]>=Forms!Tba1!SDate1 AND [TDate]<=Forms!TBa1!EDate1 AND
[Zone]=Forms!TBa1!Zone"),0)-Nz(DSum("[NBVDisposed]","aCapitalGains","[ACCode]
= 86 AND [TDate]>=Forms!Tba1!SDate1 AND [TDate]<=Forms!TBa1!EDate1 AND
[Zone]=Forms!TBa1!Zone"),0)),Nz(DSum("[DisposalValue]","aAssetsDep","[Disposed]>=Forms!TBa1!SDate1
AND [Disposed]<=Forms!TBa1!EDate1 AND [Zone]=Forms!TBa1!Zone"),0)) AS TCu
FROM Accounts
WHERE (((Accounts.AccountID)=86 Or (Accounts.AccountID)=87));

But then, the sub-form based on it is slow on the main form.

I must say that all the queries are not as complex as this one, and they all
output instantly on their own, but combined on the form, it takes more than
60 seconds sometimes.

How can I speed things up?