Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Query_CompareTables.mdb
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

Query_CompareTables.mdb

 Post Reply Post Reply
Author
Message
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Query_CompareTables.mdb
    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: uploads/37/Query_CompareTables.zip

--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.