Dynamic Recordset Processing

2neilt · #1 (**permalink**) 08-23-11, 07:46

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?

r937 · #2 (**permalink**) 08-23-11, 08:38

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

2neilt · #3 (**permalink**) 08-23-11, 09:16

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

Internet Services

Web Development

Digital Marketing

Consumer Tech