Query_CompareTables.mdb |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
Posted: 08 Jul 2008 at 10:18pm |
This sample db demonstrates an interesting approach for comparison of data in two tables. Discrepancies between the contents of different tables can be identified directly via union query, without having to undertake a series of individual field-wise comparisons. Hitherto, the main hurdle in taking advantage of this feature of a union query (without ALL clause) has been the absence of a foolproof and positive method to identify the source table for each row displayed by the union query, irrespective of any order by clause. Inclusion of a source-identifier string converts erstwhile identical records from different tables into unique ones, causing all such records to show up, defeating the original objective. However, the above limitation can be overcome by utilizing an interesting feature of Access in such a manner that various source-identifier strings pertaining to different tables are treated as identical by the union query, while remaining uniquely identifiable through proper interpretation. This approach is based upon the fact that for a given letter of alphabet, while upper and lower case are treated alike by the query, their interpretation based upon character codes is different. Discrepancies in the contents of two tables (for records identified by combination of PtID and AdmDate) are displayed as follows: 1 - Non-Matching records: (a) These are displayed in the subform at right. For each row, the name of its source table is also shown. (b) Each row is highlighted (separate back colors, depending upon the source table name). (c) For the fields under comparison, only those values are shown where there is a discrepancy between the two tables. Other fields (having no deviation) appear blank. (d) Displayed non-matching values are highlighted (separate back colors depending upon which table the field belongs to). 2 - Missing records (Existing in one table but not the other): (a) These are displayed in the subform at left. For each row, the name of its source table is also shown. (b) Each row is highlighted (separate back colors, depending upon the source table name). Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)
|
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |