How To Use The Failed Matches Query In Resource 32000
Resource 32000 writes all transaction detail into the “Detail? table. Control entry lines such as Invoices, Stock, and JC WIP are inserted with identical values for “OriginalValue? and “CurrentValue?. Transactions, which match lines, write a record into a table called “Matching?, which identifies the two lines being matched, and the value of the match. Whenever this happens Resource 32000 automatically adjusts the value of the two detail lines by the value of the match. The transactional nature of modern databases means that unless there is a database failure all or none of the transaction is written and hence either the match happens entirely or not at all.
After a database failure conceivably a transaction could have been half written. DSR have created a query, which searches out such failures called: “FailedMatches?. This query works on PCs which have Microsoft Access installed on them.
To use the query:
- Load the “SpotMatchings.mdb? database into data\system in your Resource 32000 area if it is not already there.
- This database contains the required queries, but naturally not the tables that they need to refer to.
- Use Microsoft Access to link the following tables from the problem database: “Detail? and “Matching?
- If the source database is MS SQL Server or Sybase then you may need to rename the linked tables. Typically they might have the prefix “DBO? on their names, which you should remove.
- Run the “FailedMatches? query. You will be prompted for the ledger that you want to examine. You will be supplied with a list of lines whose “CurrentValue? is not equal to “OriginalValue? – the total of matches applied to the line.
Other queries, which might be useful, are “OrphanedMatches1? and “OrphanedMatches2?. These queries list “Matching? records, which do not point at a “Detail? record. (In a database where archived records have been deleted this query may not be terribly useful)