Print Page | Close Window

Query_CompareTables.mdb

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Tejpal, A.D.
Forum Description: A.D. Tejpal's Library
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=67
Printed Date: 18 Oct 2017 at 5:10am


Topic: Query_CompareTables.mdb
Posted By: A.D. Tejpal
Subject: Query_CompareTables.mdb
Date 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)

Download Access 2K/XP/2K3/2K7: http://www.rogersaccesslibrary.com/forum/uploads/37/Query_CompareTables.zip - uploads/37/Query_CompareTables.zip



-------------
--

Email: mailto:adtejpal@gmail.com - adtejpal@gmail.com



Print Page | Close Window