Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Dynamic Recordset Processing

    Goal: Track " Electrical" change documents that impact Drawings

    I have four tables, three of which document changes with the fourth identifying drawings. Change records are linked to the Drawings table in one to many relationship as one change may impact several drawings.

    an SQL union query of the three change tables filters all changes "electrical" and I would like to further process this recordset to ultimatly report a drawing list with associated "electrical" changes only.

    The drawing table is static but the Change tables are updated daily and the query is set to run on startup.

    What is the best statergy for executing the above?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT drawings.drawing_no
         , drawings.anothercolumn
         , changes.somecolumn
      FROM drawings
    LEFT OUTER
      JOIN ( SELECT drawing_no
                  , somecolumn
               FROM changes_table1
              WHERE type = 'electrical'
             UNION ALL
             SELECT drawing_no
                  , somecolumn
               FROM changes_table2
              WHERE type = 'electrical'
             UNION ALL
             SELECT drawing_no
                  , somecolumn
               FROM changes_table3
              WHERE type = 'electrical'
           ) AS changes
        ON changes.drawing_no = drawings.drawing_no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    2

    Thanks Rudy,

    I will "checkout" your solution as my database is access 2007 and I will have to convert it to run in my VBA code.

    I was pleased to see a local talent answer as I am a bit north of you in Barrie.

    Thanks for the quick response and I will certainly checkout your book

    Best Regards

    neilT

Posting Permissions

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