Ridiculous Query

[k.robbo]

I've inherited a database and I'm trying to untangle it. One of the main problems I have is some of the queries are so huge. This example I am posting adds the same table 9 times! I have no idea what to do with this apart from replacing the tables with multiple dlookups, but I'm not sure that would be an improvement. Any ideas?

SELECT tblClientTransactionJournal.ClientTransNo, tblClientTransactionJournal.TransactionDate, tblClientTransactionJournal.ClientID, tblClientTransactionJournal.TotalTransaction, tblClientTransactionJournal.HowPaid, tblClientTransactionJournal.TransCode, tblReceiptsJournal.Account_No, tblReceiptsJournal.Entry_Date, tblHowPaid.HowPaidDescription, tblTransactionCodes.Transaction_Description, tblReceiptsJournal.Cheque_BSBNo, tblReceiptsJournal.Cheque_Number, tblReceiptsJournal.Remarks, tblReceiptsJournal.RentHeading, tblReceiptsJournal.RentAmt, tblReceiptsJournal.InsuranceHeading, tblReceiptsJournal.InsuranceAmt, tblReceiptsJournal.BondHeading, tblReceiptsJournal.BondAmt, tblReceiptsJournal.Collcost1Heading, tblReceiptsJournal.Collcost1Amt, tblReceiptsJournal.Collcost2Heading, tblReceiptsJournal.Collcost2Amt, tblReceiptsJournal.Collcost3Heading, tblReceiptsJournal.Collcost3Amt, tblReceiptsJournal.Collcost4Heading, tblReceiptsJournal.Collcost4Amt, tblReceiptsJournal.Collcost5Heading, tblReceiptsJournal.Collcost5Amt, tblReceiptsJournal.SundryTrfHeading, tblReceiptsJournal.SundryTrfAmt, tblReceiptsMaster.ReceiptName, tblReceiptsMaster_1.ReceiptName, tblReceiptsMaster_2.ReceiptName, tblReceiptsMaster_3.ReceiptName, tblReceiptsMaster_4.ReceiptName, tblReceiptsMaster_5.ReceiptName, tblReceiptsMaster_6.ReceiptName, tblReceiptsMaster_7.ReceiptName, tblReceiptsMaster_8.ReceiptName, tblContracts.AccountClosed, tblClients.InActiveFlag
FROM (((((((((((((tblClientTransactionJournal LEFT JOIN tblReceiptsJournal ON tblClientTransactionJournal.ClientTransNo = tblReceiptsJournal.ClientTransNo) LEFT JOIN tblHowPaid ON tblClientTransactionJournal.HowPaid = tblHowPaid.HowPaidCode) LEFT JOIN tblTransactionCodes ON tblClientTransactionJournal.TransCode = tblTransactionCodes.Transaction_Code) LEFT JOIN tblReceiptsMaster ON tblReceiptsJournal.RentHeading = tblReceiptsMaster.ReceiptType_Recno) LEFT JOIN tblReceiptsMaster AS tblReceiptsMaster_1 ON tblReceiptsJournal.InsuranceHeading = tblReceiptsMaster_1.ReceiptType_Recno) LEFT JOIN tblReceiptsMaster AS tblReceiptsMaster_2 ON tblReceiptsJournal.BondHeading = tblReceiptsMaster_2.ReceiptType_Recno) LEFT JOIN tblReceiptsMaster AS tblReceiptsMaster_3 ON tblReceiptsJournal.Collcost1Heading = tblReceiptsMaster_3.ReceiptType_Recno) LEFT JOIN tblReceiptsMaster AS tblReceiptsMaster_4 ON tblReceiptsJournal.Collcost2Heading = tblReceiptsMaster_4.ReceiptType_Recno) LEFT JOIN tblReceiptsMaster AS tblReceiptsMaster_5 ON tblReceiptsJournal.Collcost3Heading = tblReceiptsMaster_5.ReceiptType_Recno) LEFT JOIN tblReceiptsMaster AS tblReceiptsMaster_6 ON tblReceiptsJournal.Collcost4Heading = tblReceiptsMaster_6.ReceiptType_Recno) LEFT JOIN tblReceiptsMaster AS tblReceiptsMaster_7 ON tblReceiptsJournal.Collcost5Heading = tblReceiptsMaster_7.ReceiptType_Recno) LEFT JOIN tblReceiptsMaster AS tblReceiptsMaster_8 ON tblReceiptsJournal.SundryTrfHeading = tblReceiptsMaster_8.ReceiptType_Recno) LEFT JOIN tblContracts ON tblReceiptsJournal.Account_No = tblContracts.contract_no) INNER JOIN tblClients ON tblClientTransactionJournal.ClientID = tblClients.clientID
WHERE (((tblClientTransactionJournal.ClientTransNo) Between [Forms]![frmClientTransactionFS]![txtClientTransNo] And [Forms]![frmClientTransactionFS]![txtClientTransNo]))
ORDER BY tblReceiptsJournal.Account_No;

 

[Galaxiom]

The query is hideous because the underlying data structure is denormalised.

The is evidence by ReceiptsJournal table having repeating columns and the way the query is handling them. Data is being stored in the structure instead of the records. Whatever those CollCost fields are they probably should be in a single field with another field to record the associated number.

From your description the database is likely to be littered with similar mistakes. Unfortunately, this means you will have to completely rebuild it because all the forms and reports will be built around the flawed data structure. Moreover you will need to build a set of queries to transfer the existing data into the new structure.

Whatever you do don't change anything to DLookups as that will slow it down enormously. Be careful to realise that simplicity of expression does not automatically mean better performance. Sometimes a query expression will be more complex because it runs better than the one that is easier to write. The DLookup is a perfect case and it has often been misused by novice developers who discover their application runs like treacle.

However this is not the case here. It is just bad to the bone. Your inheritance was a dud.

 

[k.robbo]

Thanks guys. Galaxiom, you've confirmed what I already suspected. Unfortunately, this is just a small example of the problems contained in this database. Wish me luck!

Uncle Gizmo, I will have a look at that link, hopefully, it will help save my sanity.