Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparison Query (WINXP - ACC 97)

    Good Day,

    I have a table (tblCurrentMonth) with 25 fields. Every month I compare five fields from this table to tblPreviousMonth. (Both tables are identical setup).
    The query looks like this:

    SELECT [TBLCURRENTMONTH].MEMNUM, [TBLCURRENTMONTH].[LAST NAME], [TBLPREVIOUSMONTH].[LAST NAME], [TBLCURRENTMONTH].[FIRST NAME], [TBLPREVIOUSMONTH].[FIRST NAME], [TBLCURRENTMONTH].MI, [TBLPREVIOUSMONTH].MI, [TBLCURRENTMONTH].STREET, [TBLPREVIOUSMONTH].STREET, [TBLCURRENTMONTH].CITY, [TBLPREVIOUSMONTH].CITY
    FROM [TBLCURRENTMONTH] INNER JOIN [TBLPREVIOUSMONTH] ON [TBLCURRENTMONTH].MEMNUM = [TBLPREVIOUSMONTH].MEMNUM
    WHERE ((([TBLPREVIOUSMONTH].[LAST NAME])<>[TBLCURRENTMONTH].[LAST NAME])) OR ((([TBLCURRENTMONTH].[LAST NAME]) Is Not Null) AND (([TBLPREVIOUSMONTH].[LAST NAME]) Is Null)) OR ((([TBLCURRENTMONTH].[LAST NAME]) Is Null) AND (([TBLPREVIOUSMONTH].[LAST NAME]) Is Not Null)) OR ((([TBLPREVIOUSMONTH].[FIRST NAME])<>[TBLCURRENTMONTH].[FIRST NAME])) OR ((([TBLCURRENTMONTH].[FIRST NAME]) Is Not Null) AND (([TBLPREVIOUSMONTH].[FIRST NAME]) Is Null)) OR ((([TBLCURRENTMONTH].[FIRST NAME]) Is Null) AND (([TBLPREVIOUSMONTH].[FIRST NAME]) Is Not Null)) OR ((([TBLPREVIOUSMONTH].MI)<>[TBLCURRENTMONTH].[MI])) OR ((([TBLCURRENTMONTH].MI) Is Not Null) AND (([TBLPREVIOUSMONTH].MI) Is Null)) OR ((([TBLCURRENTMONTH].MI) Is Null) AND (([TBLPREVIOUSMONTH].MI) Is Not Null)) OR ((([TBLPREVIOUSMONTH].STREET)<>[TBLCURRENTMONTH].[STREET])) OR ((([TBLCURRENTMONTH].STREET) Is Not Null) AND (([TBLPREVIOUSMONTH].STREET) Is Null)) OR ((([TBLCURRENTMONTH].STREET) Is Null) AND (([TBLPREVIOUSMONTH].STREET) Is Not Null)) OR ((([TBLPREVIOUSMONTH].CITY)<>[TBLCURRENTMONTH].[CITY])) OR ((([TBLCURRENTMONTH].CITY) Is Not Null) AND (([TBLPREVIOUSMONTH].CITY) Is Null)) OR ((([TBLCURRENTMONTH].CITY) Is Null) AND (([TBLPREVIOUSMONTH].CITY) Is Not Null));


    Naturally, the boss wants to now compare ALL 25 fields to see what changes were made from one month to the next. Is there an easier way to do this than to go into QBE and do the above for all 25 fields? I would think there is a way to do a Loop, but my VBA is a bit rusty.

    Can you point me in the right direction?

    Thank you very much.
    Michael

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparison Query (WINXP - ACC 97)

    I think you must compare all 25 fields. This VBA code should build the query so you don't need to fuss with all the syntax and brackets details (not tested)

    Sub test()
    Dim sWhere As String
    Dim sOld As String
    Dim sNew As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qdf As DAO.QueryDef

    sOld = "[TBLPREVIOUSMONTH].["
    sNew = "[TBLCURRENTMONTH].["
    sWhere = ""
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblcurrentmonth")

    For Each fld In rs.Fields
    sWhere = sWhere & " and (" & sOld & fld.Name & "] <> " & sNew & fld.Name & _
    "]) OR (" & sOld & fld.Name & "] IS NOT NULL and " & sNew & fld.Name & "] IS NULL) "
    Next fld

    sWhere = "SELECT blah, blah, blah FROM [TBLCURRENTMONTH] INNER JOIN [TBLPREVIOUSMONTH] ON [TBLCURRENTMONTH].MEMNUM = [TBLPREVIOUSMONTH].MEMNUM " & sWhere

    Set qdf = db.QueryDefs("NameOfQuery")
    qdf.SQL = sWhere

    End Sub
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparison Query (WINXP - ACC 97)

    Ooops..

    at the very least, you must make this correction:

    sWhere = "SELECT blah, blah, blah FROM [TBLCURRENTMONTH] INNER JOIN [TBLPREVIOUSMONTH] ON [TBLCURRENTMONTH].MEMNUM = [TBLPREVIOUSMONTH].MEMNUM WHERE " & mid(sWhere,5)
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparison Query (WINXP - ACC 97)

    Wow !!! Thank you so much jacksonmacd !!

    So I do need to enter the 25 fields in the blah blah blah part.
    SELECT blah, blah, blah

    I can't wait to get to work on Tuesday. I will let you know how it works out !!

    Much, much appreciated.

    Sincerely,
    Michael

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparison Query (WINXP - ACC 97)

    You could use

    <code>SELECT TBLCURRENTMONTH.*, TBLPREVIOUSMONTH.* FROM ...</code>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparison Query (WINXP - ACC 97)

    I have attached a somewhat generic version of Jackson's code. The procedure CompareTablesQuery has four arguments, all of type string:
    strTable1 and strTable2 are the names of the tables to be compared; they must have the same structure.
    strLink is the name of a unique identifier.
    strQuery is the name of the query that will display the results. If this query exists, it will be overwritten, otherwise it will be created.

    Example of calling the procedure (for example from the Immediate window):

    CompareTablesQuery "TBLPREVIOUSMONTH", "TBLCURRENTMONTH", "MEMNUM", "qryCompare"

    Another approach can be found in CompareTwoTables.mdb frm Roger's Access Library (free download.)
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparison Query (WINXP - ACC 97)

    Thank you SO much Jackson & Hans. Although I do cherish having a 3 day weekend, I (almost) can't
    wait to get to work on Tuesday to get this running.

    Best Regards Guys !!
    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •