SUMMARIES REPORTING

[Glint]

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?

 

[Klatuu]

Is your database split?
If not, where is it? On a server or on your desktop?
If so, where is the back end and where is the front end?
Proper deployment of an Access application can have a significant impact on
performance.

 

[Glint]

Thanks for your response, Klatuu.
The database is split, and both front and back ends are in the same desktop
at this stage.
--
Glint

Is your database split?
If not, where is it? On a server or on your desktop?
If so, where is the back end and where is the front end?
Proper deployment of an Access application can have a significant impact on
performance.
--
Dave Hargis, Microsoft Access MVP

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?

 

[Klatuu]

I think b is probably your best bet. Without knowing your data or your
queries, I can't make any suggestions on improving performance, but you might
look at indexing any fields in your tables your are filtering on.
--
Dave Hargis, Microsoft Access MVP

Thanks for your response, Klatuu.
The database is split, and both front and back ends are in the same desktop
at this stage.
--
Glint

Is your database split?
If not, where is it? On a server or on your desktop?
If so, where is the back end and where is the front end?
Proper deployment of an Access application can have a significant impact on
performance.
--
Dave Hargis, Microsoft Access MVP

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?