Thanks for the replies, ghodges (Geoff) and NoellaG (NG).
As far as database design is concerned (tables and relationships), I am confident that I created balance between theory and real-world requirements of the system.
It is an accounting database that has at least 20 modules (all dealing with entirely different sets of transactions), and most of them need to output an automated journal entry. Hence, the need for union queries.
Code:
Invoice Module
Debit Accounts Receivable
Debit Sales Discounts
Credit Sales Tax Payable
Credit Sales
Collection Module
Debit Untransferred Collections
Debit Creditable Withholding Tax
Credit Accounts Receivable
Remittance Module
Debit Transferred Collections
Debit Collection Short or Over
Credit Untransferred Collections
Deposit Module
Debit Cash in Bank
Debit Deposit Short or Over
Credit Transferred Collections
Returned Checks Module
Debit Accounts Receivable
Credit Cash in Bank
Inventory Purchases Module
Debit Inventory
Debit Sales Tax Payable
Credit Inventory - Trade Discounts
Credit Accounts Payable - Clearing
Fixed Asset Purchases Module
Debit Fixed Assets
Debit Sales Tax Payable
Credit Accounts Payable - Clearing
Accounts Payable Module
Debit Sundry Assets and Expenses
Debit Accounts Payable - Clearing
Credit Accounts Payable
Check Disbursement Module
Debit Sundry Assets and Expenses
Debit Accounts Payable
Credit Cash in Bank
Inventory Withdrawal Module
Debit Accounts Payable
Debit Inventory Losses
Credit Inventory
Inventory Transfer Module
Debit Inventory
Credit Inventory
General Journal Module
Debit Sundry Debits
Credit Sundry Credits
There are other modules I didn't mention here.
Each debit require one regular query.
Each module (a cluster of debits and credits) require one union query.
Finally, the entire general ledger requires a union query consisting of all modules.
Geoff, I can implement your recommendation that I accumulate my data in a temporary table, instead of forcing all data to merge in a union query. That is actually quite simple to do.
What I do not know is the use of temporary tables itself. Is it the same as using temporary tables in SQL Server (#table)? Or is it a permanent table on which I do DELETE and INSERT operations? I heard that doing frequent DELETEs and INSERTs on a permanent table holding a huge set of data makes a database prone to fragmentation and corruption.
I consider myself skillful in designing databases (myself being the judge), but I do not know any optimization I must do in an Access database.
Thanks for any help I am about to receive.
Joe.
Cannot open any more databases.
Windows 7 
Access 2003
Cannot open any more databases.
Reply With Quote
Originally Posted by RuralGuy
