Using a parameter in a union query

[Rob]

I need help on how to include a parameter in a union qry. I currently have a
list box set up on a separate form and just need to know how to reference it
in my union query. See my SQL below. I need the parameter in the "BU" field.
Thanks.

SELECT "US" AS BU, [qry total bgt by BU].[P&L Line], Sum([qry total bgt by
BU].Bgt_US) AS SumOfBgt_US, Sum([qry actuals by BU].Act_US) AS SumOfAct_US
FROM [qry actuals by BU] LEFT JOIN [qry total bgt by BU] ON [qry actuals by
BU].[P&L] = [qry total bgt by BU].[P&L Line]
GROUP BY "US", [qry total bgt by BU].[P&L Line];
UNION
SELECT "US_G&A" AS BU, [qry total bgt by BU].[P&L Line], Sum([qry total bgt
by BU].[Bgt_US_G & A]) AS [SumOfBgt_US_G & A], Sum([qry actuals by
BU].[Act_US_G & A]) AS [SumOfAct_US_G & A]
FROM [qry actuals by BU] LEFT JOIN [qry total bgt by BU] ON [qry actuals by
BU].[P&L] = [qry total bgt by BU].[P&L Line]
GROUP BY "US_G&A", [qry total bgt by BU].[P&L Line];
UNION
SELECT "UK" AS BU, [qry total bgt by BU].[P&L Line], Sum([qry total bgt by
BU].Bgt_UK) AS SumOfBgt_UK, Sum([qry actuals by BU].Act_UK) AS SumOfAct_UK
FROM [qry actuals by BU] LEFT JOIN [qry total bgt by BU] ON [qry actuals by
BU].[P&L] = [qry total bgt by BU].[P&L Line]
GROUP BY "UK", [qry total bgt by BU].[P&L Line];
UNION
SELECT "SCO" AS BU, [qry total bgt by BU].[P&L Line], Sum([qry total bgt by
BU].Bgt_SCO) AS SumOfBgt_SCO, Sum([qry actuals by BU].Act_SCO) AS SumOfAct_SCO
FROM [qry actuals by BU] LEFT JOIN [qry total bgt by BU] ON [qry actuals by
BU].[P&L] = [qry total bgt by BU].[P&L Line]
GROUP BY "SCO", [qry total bgt by BU].[P&L Line];
UNION
SELECT "SCI_TOTAL" AS BU, [qry total bgt by BU].[P&L Line], Sum([qry total
bgt by BU].Bgt_SCItotal) AS SumOfBgt_SCItotal, Sum([qry actuals by
BU].Act_SCI) AS SumOfAct_SCI
FROM [qry actuals by BU] LEFT JOIN [qry total bgt by BU] ON [qry actuals by
BU].[P&L] = [qry total bgt by BU].[P&L Line]
GROUP BY "SCI_TOTAL", [qry total bgt by BU].[P&L Line];
UNION
SELECT "SBJ" AS BU, [qry total bgt by BU].[P&L Line], Sum([qry total bgt by
BU].Bgt_SBJ) AS SumOfBgt_SBJ, Sum([qry actuals by BU].Act_SBJ) AS SumOfAct_SBJ
FROM [qry actuals by BU] LEFT JOIN [qry total bgt by BU] ON [qry actuals by
BU].[P&L] = [qry total bgt by BU].[P&L Line]
GROUP BY "SBJ", [qry total bgt by BU].[P&L Line];

 

[pietlinden]

I need help on how to include a parameter in a union qry. I currently have a
list box set up on a separate form and just need to know how to reference it
in my union query. See my SQL below. I need the parameter in the "BU" field.
Thanks.

filter before you do the union.

 

[Rob]

Sorry, not sure what you mean. The purpose of my union query is to line up
all the "BU's". Now I want to provide the user the ability to select a
specific BU and view the results.
Thanks.