Union Query Where Clause Help

Question

Hi all

I have created a number of select queries which each return different data on the basis of a drawing number and revision entered by the user. I am now trying to union these together.

Each query currently has [Enter Drawing Number] and [Enter Revision] as criteria in the appropriate fields in the design view window. When I union the first two queries, the [Enter Drawing Number] prompt is displayed twice to allow each query to run. Strangely, the [Enter Revision] prompt only occurs once.

Anyhow, I am thinking that I maybe need to bracket the entire SQL code and then have a single Where clause at the end requesting the Drawing and Revision entry. Hopefully then I only get prompted once for these values.
Am I anywhere near the solution here?
If so, where should the all encompassing brackets be located?

These are the current unioned queries:

SELECT [z01-Dwg-Nos].z1_lfd, [z02-Dwg-Titles].z2_ben1, [z02-Dwg-Titles].z2_ben2, [z01-Dwg-Nos].z1_znr, [z05-Dwg-Rev-OriginComment].z5_buchst, [z07a-Item-Materials-Stkl].az7_ItemNo, [z07a-Item-Materials-Stkl].az7_Qty, [z02-Dwg-Titles_1].z2_ben1, [z02-Dwg-Titles_1].z2_sap, "" AS az7_std, [z13-ItemNos].z13_et_cha, [z01-Dwg-Nos_1].z1_znr, [z05-Dwg-Rev-OriginComment_1].z5_buchst, "" AS az7_dims, "" AS az7_Model, [z23 Plate-Spec].z23_pruefs, [z02-Dwg-Titles_1].z2_werkst, [z02-Dwg-Titles_1].z2_masse, [az7_Qty]*[z02-Dwg-Titles_1.z2_masse] AS TotWt, [z07a-Item-Materials-Stkl].az7_lfd, [z07a-Item-Materials-Stkl].az7_znr_Item_I, [z07a-Item-Materials-Stkl].az7_Z5_Ifd, [z07a-Item-Materials-Stkl].az7_buch_l
FROM (((((([z02-Dwg-Titles] INNER JOIN [z01-Dwg-Nos] ON [z02-Dwg-Titles].z2_znr_l = [z01-Dwg-Nos].z1_lfd) INNER JOIN [z07a-Item-Materials-Stkl] ON [z01-Dwg-Nos].z1_lfd = [z07a-Item-Materials-Stkl].az7_znr_l) INNER JOIN ([z02-Dwg-Titles] AS [z02-Dwg-Titles_1] INNER JOIN [z01-Dwg-Nos] AS [z01-Dwg-Nos_1] ON [z02-Dwg-Titles_1].z2_znr_l = [z01-Dwg-Nos_1].z1_lfd) ON [z07a-Item-Materials-Stkl].az7_znr_Item_I = [z01-Dwg-Nos_1].z1_lfd) LEFT JOIN [z05-Dwg-Rev-OriginComment] AS [z05-Dwg-Rev-OriginComment_1] ON [z07a-Item-Materials-Stkl].az7_Z5_Ifd = [z05-Dwg-Rev-OriginComment_1].z5_lfd) INNER JOIN [z05-Dwg-Rev-OriginComment] ON [z07a-Item-Materials-Stkl].az7_buch_l = [z05-Dwg-Rev-OriginComment].z5_lfd) LEFT JOIN [z13-ItemNos] ON [z02-Dwg-Titles_1].z2_lfd_z13 = [z13-ItemNos].z13_lfd) LEFT JOIN [z23 Plate-Spec] ON [z02-Dwg-Titles_1].z2_pru_lfd = [z23 Plate-Spec].z23_lfd
WHERE ((([z01-Dwg-Nos].z1_znr)=[Enter Drawing Number]) AND (([z05-Dwg-Rev-OriginComment].z5_buchst)=[Enter Revision]))

UNION

SELECT [z01-Dwg-Nos].z1_lfd, [z02-Dwg-Titles].z2_ben1, [z02-Dwg-Titles].z2_ben2, [z01-Dwg-Nos].z1_znr, [z05-Dwg-Rev-OriginComment].z5_buchst, [z07b-Item-Materials-Stkl].bz7_ItemNo, [z07b-Item-Materials-Stkl].bz7_Qty, [z10-Parts_Description].z10_bez1, [z11-Parts_Description2].z11_sap, [z10-Parts_Description].z10_bez2, "" AS bz7_et_cha, "" AS bz7_znr, "" AS bz7_buchst, [z11-Parts_Description2].z11_bez1, "" AS bz7_ben2, "" AS bz7_certs, [z11-Parts_Description2].z11_bez2, [z11-Parts_Description2].z11_masse, [bz7_Qty]*[z11_masse] AS TotWt, "" AS az7_lfd, "" AS bz7_znr_Item, "" AS bz7_z5_lfd, "" AS bz7_buch_lfd
FROM ((([z07b-Item-Materials-Stkl] INNER JOIN [z10-Parts_Description] ON [z07b-Item-Materials-Stkl].[bz7_z10-Ifd] = [z10-Parts_Description].z10_lfd) INNER JOIN [z11-Parts_Description2] ON [z07b-Item-Materials-Stkl].bz7_z11_Ifd = [z11-Parts_Description2].z11_lfd) INNER JOIN [z05-Dwg-Rev-OriginComment] ON [z07b-Item-Materials-Stkl].bz7_buch_l = [z05-Dwg-Rev-OriginComment].z5_lfd) INNER JOIN ([z02-Dwg-Titles] INNER JOIN [z01-Dwg-Nos] ON [z02-Dwg-Titles].z2_znr_l = [z01-Dwg-Nos].z1_lfd) ON [z07b-Item-Materials-Stkl].bz7_znr_l = [z01-Dwg-Nos].z1_lfd
WHERE ((([z01-Dwg-Nos].z1_znr)=[Enter Drawing No]) AND (([z05-Dwg-Rev-OriginComment].z5_buchst)=[Enter Revision]))
ORDER BY [z07a-Item-Materials-Stkl].az7_ItemNo;

Many thanks

Phil

Answer 1

Hi Phil. Rather than use a parameter prompt, have you considered using an input form?

Answer 2

Hi DBGuy

Yes, I do intend to do that, once I know that my queries will work from the parameter style input.

Somehow, the problem I had that prompted this post seems to have gone away!!!
Not sure what I did right or wrong in this mix, but with the where clause after each query, I am now only getting asked to input the where criteria once.
I am now up to 4 Unioned queries and the results are returning correctly from what I can see.

Once I have a working "dirty" solution, I will look to add some sort of input/selection form.
The purpose of this application is simply to extract data from tables and pass to Excel spreadsheets, so it doesn't have to be overly user-friendly or polished.

Thanks for your continued support with this, it is certainly appreciated.
If you want to make contact off-forum please feel free [pscull - at - europacrown - dot - com].

Answer 3

Hi. Glad to hear you got it working. Good luck with your project.

I'll keep your contact info for future reference.

Cheers!