Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Using 2 recordsets to reconcile reports or qry

    I have a report 7,000 lines and another report 6700 lines, i would like to use 2 recordset to find what items are in one record set and not the other, the missing records can be from either recordset (hence i have to reconcile them )

    Does anyone have any ideas? there are about 7 - 9 fields in the record set , each recordset comes from a qry.


    Help is greatly appreciatd


    ~M

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    The following is basic syntax that will allow you to show records that exist in one table, but not the other and vis versa(because of the UNION). Know that becuase a UNION is being used the field being shown in each individual SQL statement have to the the same in number of and type.


    SELECT Table1.Field1, Table1.Field2, Table1.Field3
    FROM Table1 LEFT JOIN Table2 ON Table1.Field1= Table2.Field1
    WHERE (((table2.Field1) Is Null))
    UNION
    SELECT Table2.Field1, Table2.Field2, Table2.Field3
    FROM Table2 LEFT JOIN Table1 ON Table1.Field1= Table2.Field1
    WHERE (((table1.Field1) Is Null))


    S-

  3. #3
    Join Date
    Oct 2003
    Posts
    311
    Originally posted by sbaxter
    The following is basic syntax that will allow you to show records that exist in one table, but not the other and vis versa(because of the UNION). Know that becuase a UNION is being used the field being shown in each individual SQL statement have to the the same in number of and type.


    SELECT Table1.Field1, Table1.Field2, Table1.Field3
    FROM Table1 LEFT JOIN Table2 ON Table1.Field1= Table2.Field1
    WHERE (((table2.Field1) Is Null))
    UNION
    SELECT Table2.Field1, Table2.Field2, Table2.Field3
    FROM Table2 LEFT JOIN Table1 ON Table1.Field1= Table2.Field1
    WHERE (((table1.Field1) Is Null))


    S-
    some of the data will be the same (date, or dollar amounts) do i need to do the join on each of the columns and have each colm in the where clause???

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    You are only joining where the columns are to be the same in each table.

    But to use a Union, you have to have the same number of overall columns and datatype in each select statement the you are combining together or it won't work

    S-

  5. #5
    Join Date
    Oct 2003
    Posts
    311
    ok, so i need all of them to be the same so i have to join on all, the tables are exactly the same ( i made them from a make tbl qry)

    So the were clause do i need to include each colm here also? ie -
    WHERE (((table2.Field1) Is Null)) Or (((table2.Field2) Is Null))


    Tnk u so much!!!

    ~>M<~

  6. #6
    Join Date
    Nov 2003
    Posts
    267
    I would just include in the were clause the field were you know data should normally be all the time. If you don't have one like that then include as many fields as neccesary to get you the data you need.

    Also if you need to do this make you OR an AND (you will want it were all fields are Null in this situation)

    S-

Posting Permissions

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