CODEWITHSUNDEEP
sqlvbams-accessdao
Lacie
Lacie

Reputation: 3

VBA-ACCESS DAO SQL with UNION clause and WHERE clause

This is a small part of my code but in this particular section, I'm trying to get the following statement to execute:

"WHERE ([SummaryTbl].[WORK_ITEM_NMB]>2500) AND ([ParentChildTbl].[WORK_ITEM_NMB]>2500)"

I've used all the JOINS/UNION since ACCESS doesn't support FULL OUTER JOIN and that all works. However, I want it to return only values where the WORK_ITEM_NMB for both tables is greater than 2500 and this does not work. It keeps giving me all the records. I'm thinking I either put the WHERE clause in the incorrect place and/or I have to add it to every JOIN statement. I don't get an error. I just get all the records. 

Set db = OpenDatabase(DBLoc)
SQL = "SELECT [SummaryTbl].[WORK_ITEM_NMB], [SummaryTbl].[WORK_ITEM_STS], [SummaryTbl].[RELEASE_NMB], [SummaryTbl].[NOMADS_PRIORITY], [SummaryTbl].[TEST_ACTUAL_DT], [SummaryTbl].[TRAIN_ACTUAL_DT], [SummaryTbl].[TITLE], [SummaryTbl].[DESCRIPTION], [SummaryTbl].[DETAILED_RQ_COMMENTS], [SummaryTbl].[TRAIN_COMMENTS], [SummaryTbl].[TEST_COMMENTS], [ParentChildTbl].[HasAssocWI] FROM SummaryTbl LEFT JOIN ParentChildTbl ON [SummaryTbl].[WORK_ITEM_NMB] = [ParentChildTbl].[WORK_ITEM_NMB] UNION SELECT [SummaryTbl].[WORK_ITEM_NMB], [SummaryTbl].[WORK_ITEM_STS], [SummaryTbl].[RELEASE_NMB], [SummaryTbl].[NOMADS_PRIORITY], [SummaryTbl].[TEST_ACTUAL_DT], [SummaryTbl].[TRAIN_ACTUAL_DT], [SummaryTbl].[TITLE], [SummaryTbl].[DESCRIPTION], [SummaryTbl].[DETAILED_RQ_COMMENTS], [SummaryTbl].[TRAIN_COMMENTS], [SummaryTbl].[TEST_COMMENTS], [ParentChildTbl].[HasAssocWI] FROM SummaryTbl RIGHT JOIN ParentChildTbl ON [SummaryTbl].[WORK_ITEM_NMB] = [ParentChildTbl].[WORK_ITEM_NMB]" & _
"WHERE ([SummaryTbl].[WORK_ITEM_NMB]>2500) AND ([ParentChildTbl].[WORK_ITEM_NMB]>2500)"


'Execute Query and populate recordset
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

Thank you!

Upvotes: 0

Views: 288

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

You'll need to include the where clause in both select statements, since the select statements will be evaluated before the union; hence, you'll need something like:

SELECT 
    [SummaryTbl].[WORK_ITEM_NMB], 
    [SummaryTbl].[WORK_ITEM_STS], 
    [SummaryTbl].[RELEASE_NMB], 
    [SummaryTbl].[NOMADS_PRIORITY], 
    [SummaryTbl].[TEST_ACTUAL_DT], 
    [SummaryTbl].[TRAIN_ACTUAL_DT], 
    [SummaryTbl].[TITLE], 
    [SummaryTbl].[DESCRIPTION], 
    [SummaryTbl].[DETAILED_RQ_COMMENTS], 
    [SummaryTbl].[TRAIN_COMMENTS], 
    [SummaryTbl].[TEST_COMMENTS], 
    [ParentChildTbl].[HasAssocWI] 
FROM 
    SummaryTbl LEFT JOIN ParentChildTbl ON
    [SummaryTbl].[WORK_ITEM_NMB] = [ParentChildTbl].[WORK_ITEM_NMB] 
WHERE 
    ([SummaryTbl].[WORK_ITEM_NMB]>2500) AND 
    ([ParentChildTbl].[WORK_ITEM_NMB]>2500)
UNION 
SELECT
    [SummaryTbl].[WORK_ITEM_NMB], 
    [SummaryTbl].[WORK_ITEM_STS], 
    [SummaryTbl].[RELEASE_NMB],
    [SummaryTbl].[NOMADS_PRIORITY], 
    [SummaryTbl].[TEST_ACTUAL_DT], 
    [SummaryTbl].[TRAIN_ACTUAL_DT], 
    [SummaryTbl].[TITLE], 
    [SummaryTbl].[DESCRIPTION], 
    [SummaryTbl].[DETAILED_RQ_COMMENTS], 
    [SummaryTbl].[TRAIN_COMMENTS], 
    [SummaryTbl].[TEST_COMMENTS], 
    [ParentChildTbl].[HasAssocWI] 
FROM 
    SummaryTbl RIGHT JOIN ParentChildTbl ON 
    [SummaryTbl].[WORK_ITEM_NMB] = [ParentChildTbl].[WORK_ITEM_NMB]
WHERE 
    ([SummaryTbl].[WORK_ITEM_NMB]>2500) AND 
    ([ParentChildTbl].[WORK_ITEM_NMB]>2500)

Though, it looks like your second select statement should be sourcing the WORK_ITEM_NMB field from the ParentChildTbl table (on the right of the join), rather than the SummaryTbl table.

Also, since you're joining on the WORK_ITEM_NMB field, you only need to filter this in the table for which all records are to be returned.

You could also apply the selection criteria to the result of the union, e.g.:

SELECT t.* 
FROM
(
    SELECT 
        [SummaryTbl].[WORK_ITEM_NMB], 
        [SummaryTbl].[WORK_ITEM_STS], 
        [SummaryTbl].[RELEASE_NMB], 
        [SummaryTbl].[NOMADS_PRIORITY], 
        [SummaryTbl].[TEST_ACTUAL_DT], 
        [SummaryTbl].[TRAIN_ACTUAL_DT], 
        [SummaryTbl].[TITLE], 
        [SummaryTbl].[DESCRIPTION], 
        [SummaryTbl].[DETAILED_RQ_COMMENTS], 
        [SummaryTbl].[TRAIN_COMMENTS], 
        [SummaryTbl].[TEST_COMMENTS], 
        [ParentChildTbl].[HasAssocWI] 
    FROM 
        SummaryTbl LEFT JOIN ParentChildTbl ON
        [SummaryTbl].[WORK_ITEM_NMB] = [ParentChildTbl].[WORK_ITEM_NMB] 
    UNION 
    SELECT
        [ParentChildTbl].[WORK_ITEM_NMB], 
        [SummaryTbl].[WORK_ITEM_STS], 
        [SummaryTbl].[RELEASE_NMB],
        [SummaryTbl].[NOMADS_PRIORITY], 
        [SummaryTbl].[TEST_ACTUAL_DT], 
        [SummaryTbl].[TRAIN_ACTUAL_DT], 
        [SummaryTbl].[TITLE], 
        [SummaryTbl].[DESCRIPTION], 
        [SummaryTbl].[DETAILED_RQ_COMMENTS], 
        [SummaryTbl].[TRAIN_COMMENTS], 
        [SummaryTbl].[TEST_COMMENTS], 
        [ParentChildTbl].[HasAssocWI] 
    FROM 
        SummaryTbl RIGHT JOIN ParentChildTbl ON 
        [SummaryTbl].[WORK_ITEM_NMB] = [ParentChildTbl].[WORK_ITEM_NMB]
) t
WHERE t.[WORK_ITEM_NMB] > 2500

Upvotes: 3

Related Questions