G
Glint
I need to summarise the financial transactions of our records from time to
time. Several tables are involved, many of them only remotely related, if at
all. Some tables are nearly up to 2000 records already.
I built a union query to capture all transactions of the affected tables.
However, I found that running the query on our pc in the church was very a
tedious task: it was very, very slow. So, I decided to split the query into
smaller ones, using them as the record source for subforms in a main form.
Below is a sample of the query to find the amount of cash-in-hand (any
unlodged cash, which is whatever remains from total collections after
expenses have been made):
SELECT 1 AS BKID, vCashTransactions.Zone, vCashTransactions.TDate,
Sum(vCashTransactions.Amount) AS SumOfAmount
FROM vCashTransactions
GROUP BY vCashTransactions.Zone, vCashTransactions.TDate,
vCashTransactions.CashOrCheque
HAVING (((vCashTransactions.CashOrCheque)=1))
ORDER BY vCashTransactions.TDate
UNION SELECT ALL 1 AS BKID, BankAccount.Zone, Banking.TDate, Sum([Amount])
AS Lodged
FROM BankAccount INNER JOIN (Banking INNER JOIN BankingLodgement ON
Banking.BID = BankingLodgement.BID) ON BankAccount.BAID = Banking.BAID
GROUP BY BankAccount.Zone, Banking.TDate, BankingLodgement.CashOrCheque
HAVING (((BankingLodgement.CashOrCheque)=1))
UNION SELECT ALL 1 AS BKID, Expenses.Zone, Expenses.TDate,
ExpensesDetail.Amount
FROM Expenses INNER JOIN ExpensesDetail ON Expenses.ExpenseID =
ExpensesDetail.ExpenseID
WHERE (((Expenses.CashOrCheque)=1));
vCashTransaction is a query that captures cash transactions.
Depending on the dates selected on the main form, one can get the balance of
cash-in-hand from this query.
I had ten of such queries, some not as complex as the example above, but
some more complex, for ten sub-forms on the main form.
What I found was that the main form now opens faster than when I based the
sub-forms on one giant union query, but this main form is still slow.
Interestingly, the queries open in split seconds, but it still takes the main
form ages to open.
I need suggestions in the following areas:
1. What design considerations can we bear in mind in future, considering
that we may want to display what accountants call TRIAL BALANCE (having
summaries of things like Asset depreciation on the same page as funds lost in
transit)?
2. Does basing sub-forms on small queries (union or not) perform better than
basing them on one common super query?
time. Several tables are involved, many of them only remotely related, if at
all. Some tables are nearly up to 2000 records already.
I built a union query to capture all transactions of the affected tables.
However, I found that running the query on our pc in the church was very a
tedious task: it was very, very slow. So, I decided to split the query into
smaller ones, using them as the record source for subforms in a main form.
Below is a sample of the query to find the amount of cash-in-hand (any
unlodged cash, which is whatever remains from total collections after
expenses have been made):
SELECT 1 AS BKID, vCashTransactions.Zone, vCashTransactions.TDate,
Sum(vCashTransactions.Amount) AS SumOfAmount
FROM vCashTransactions
GROUP BY vCashTransactions.Zone, vCashTransactions.TDate,
vCashTransactions.CashOrCheque
HAVING (((vCashTransactions.CashOrCheque)=1))
ORDER BY vCashTransactions.TDate
UNION SELECT ALL 1 AS BKID, BankAccount.Zone, Banking.TDate, Sum([Amount])
AS Lodged
FROM BankAccount INNER JOIN (Banking INNER JOIN BankingLodgement ON
Banking.BID = BankingLodgement.BID) ON BankAccount.BAID = Banking.BAID
GROUP BY BankAccount.Zone, Banking.TDate, BankingLodgement.CashOrCheque
HAVING (((BankingLodgement.CashOrCheque)=1))
UNION SELECT ALL 1 AS BKID, Expenses.Zone, Expenses.TDate,
ExpensesDetail.Amount
FROM Expenses INNER JOIN ExpensesDetail ON Expenses.ExpenseID =
ExpensesDetail.ExpenseID
WHERE (((Expenses.CashOrCheque)=1));
vCashTransaction is a query that captures cash transactions.
Depending on the dates selected on the main form, one can get the balance of
cash-in-hand from this query.
I had ten of such queries, some not as complex as the example above, but
some more complex, for ten sub-forms on the main form.
What I found was that the main form now opens faster than when I based the
sub-forms on one giant union query, but this main form is still slow.
Interestingly, the queries open in split seconds, but it still takes the main
form ages to open.
I need suggestions in the following areas:
1. What design considerations can we bear in mind in future, considering
that we may want to display what accountants call TRIAL BALANCE (having
summaries of things like Asset depreciation on the same page as funds lost in
transit)?
2. Does basing sub-forms on small queries (union or not) perform better than
basing them on one common super query?