Problem with Access Union Query

Question

I have a union query running a select on 2 sub queries. Both run fine individually. I am taking a field from a table and comparing for the select. I use the same field from the same table for both queries. The union query runs for awhile and then asks for input of the field being compared in the first query. It does not matter what I put in as an answer, including nothing, but the query locks up until I hit enter. The query works, but I can not get rid of the extra input requirement. ???

Answer 1

What if you union the full queries rather than unioning the saved queries, like this:

SELECT dbo_REF.COST_FMLY, dbo_V_PickHeaderArchive.PickStatus, dbo_V_PickHeaderArchive.PartDesc, dbo_REF.ITEM_DESC, dbo_V_PickLineArchive.QtyPicked, dbo_V_PickHeaderArchive.PartID, dbo_V_ReasonCode.ReasonDesc, dbo_V_PickHeaderArchive.CreateUserID, dbo_V_AppUser.UserName, dbo_V_PickHeaderArchive.OrderID, dbo_V_PickHeaderArchive.PicklistID, dbo_V_PickHeaderArchive.Assembly, dbo_REF.TOT_ROLCST AS [Part Cost], [Tot_Rolcst]*[QtyPicked] AS [Ext Cost], Trim([orderID]) & "-001" AS [Shop Order], dbo_V_PickHeaderArchive.CreateTime, [Data Spread].[Beg Date], [Data Spread].[End Date]

FROM [Data Spread], (((dbo_V_PickHeaderArchive INNER JOIN dbo_V_PickLineArchive ON (dbo_V_PickHeaderArchive.PartID = dbo_V_PickLineArchive.PartID) AND (dbo_V_PickHeaderArchive.PicklistID = dbo_V_PickLineArchive.PicklistID)) INNER JOIN dbo_V_ReasonCode ON (dbo_V_PickHeaderArchive.ReasonCode = dbo_V_ReasonCode.ReasonCode) AND (dbo_V_PickHeaderArchive.TranType = dbo_V_ReasonCode.TranType)) INNER JOIN dbo_REF ON dbo_V_PickHeaderArchive.PartID = dbo_REF.ITEM) INNER JOIN dbo_V_AppUser ON dbo_V_PickHeaderArchive.CreateUserID = dbo_V_AppUser.UserID

GROUP BY dbo_REF.COST_FMLY, dbo_V_PickHeaderArchive.PickStatus, dbo_V_PickHeaderArchive.PartDesc, dbo_REF.ITEM_DESC, dbo_V_PickLineArchive.QtyPicked, dbo_V_PickHeaderArchive.PartID, dbo_V_ReasonCode.ReasonDesc, dbo_V_PickHeaderArchive.CreateUserID, dbo_V_AppUser.UserName, dbo_V_PickHeaderArchive.OrderID, dbo_V_PickHeaderArchive.PicklistID, dbo_V_PickHeaderArchive.Assembly, dbo_REF.TOT_ROLCST, [Tot_Rolcst]*[QtyPicked], Trim([orderID]) & "-001", dbo_V_PickHeaderArchive.CreateTime, [Data Spread].[Beg Date], [Data Spread].[End Date], dbo_V_PickHeaderArchive.TranType

HAVING (((dbo_V_PickHeaderArchive.PickStatus)="D") AND ((dbo_V_ReasonCode.ReasonDesc)="Defective Part") AND ((dbo_V_PickHeaderArchive.OrderID) Not Like "R*") AND ((dbo_V_PickHeaderArchive.CreateTime)>=[Beg Date] And (dbo_V_PickHeaderArchive.CreateTime)<=[End Date]) AND ((dbo_V_PickHeaderArchive.TranType)="ISSM"))

ORDER BY [Tot_Rolcst]*[QtyPicked] DESC

UNION

SELECT dbo_REF.COST_FMLY, dbo_V_PickHeader.PickStatus, dbo_V_PickHeader.PartDesc, dbo_REF.ITEM_DESC, dbo_V_PickLine.QtyPicked, dbo_V_PickHeader.PartID, dbo_V_ReasonCode.ReasonDesc, dbo_V_PickHeader.CreateUserID, dbo_V_AppUser.UserName, dbo_V_PickHeader.OrderID, dbo_V_PickHeader.PicklistID, dbo_V_PickHeader.Assembly, dbo_REF.TOT_ROLCST AS [Part Cost], [Tot_Rolcst]*[QtyPicked] AS [Ext Cost], Trim([orderID]) & "-001" AS [Shop Order], dbo_V_PickHeader.CreateTime, [Data Spread].[Beg Date], [Data Spread].[End Date]

FROM [Data Spread], (((dbo_V_PickHeader INNER JOIN dbo_V_PickLine ON (dbo_V_PickHeader.PicklistID = dbo_V_PickLine.PicklistID) AND (dbo_V_PickHeader.PartID = dbo_V_PickLine.PartID)) INNER JOIN dbo_V_ReasonCode ON (dbo_V_PickHeader.TranType = dbo_V_ReasonCode.TranType) AND (dbo_V_PickHeader.ReasonCode = dbo_V_ReasonCode.ReasonCode)) INNER JOIN dbo_REF ON dbo_V_PickHeader.PartID = dbo_REF.ITEM) INNER JOIN dbo_V_AppUser ON dbo_V_PickHeader.CreateUserID = dbo_V_AppUser.UserID

GROUP BY dbo_REF.COST_FMLY, dbo_V_PickHeader.PickStatus, dbo_V_PickHeader.PartDesc, dbo_REF.ITEM_DESC, dbo_V_PickLine.QtyPicked, dbo_V_PickHeader.PartID, dbo_V_ReasonCode.ReasonDesc, dbo_V_PickHeader.CreateUserID, dbo_V_AppUser.UserName, dbo_V_PickHeader.OrderID, dbo_V_PickHeader.PicklistID, dbo_V_PickHeader.Assembly, dbo_REF.TOT_ROLCST, [Tot_Rolcst]*[QtyPicked], Trim([orderID]) & "-001", dbo_V_PickHeader.CreateTime, [Data Spread].[Beg Date], [Data Spread].[End Date], dbo_V_PickHeader.TranType

HAVING (((dbo_V_PickHeader.PickStatus)="D") AND ((dbo_V_ReasonCode.ReasonDesc)="Defective Part") AND ((dbo_V_PickHeader.OrderID) Not Like "R*") AND ((dbo_V_PickHeader.CreateTime)>=[Beg Date] And (dbo_V_PickHeader.CreateTime)<=[End Date]) AND ((dbo_V_PickHeader.TranType)="ISSM"))

ORDER BY [Tot_Rolcst]*[QtyPicked] DESC;

---

-- Roger Carlson

MS Access MVP 2006-2010

www.rogersaccesslibrary.com

If you want a detailed answer, ask a detailed question!

Answer 2

When I run the daily and the archive query separately, the run correctly to completion. When I run the union query, it runs for quite awhile and then requires an input on the CreatTime field referenced in the daliy query (not the archive). I can type in a response or just hit Enter and it runs for another relatively long time and gives the same results as the individual queries. I want to get rid of the extraneous input requirement so I can "fire and forget". It is a long running query. Why is the union query asking for any input??

Thanks

Answer 3

What field(s) is it prompting for?

---

-- Roger Carlson

MS Access MVP 2006-2010

www.rogersaccesslibrary.com

If you want a detailed answer, ask a detailed question!

Answer 4

OK, here goes.

Thanks

Archive

SELECT dbo_REF.COST_FMLY, dbo_V_PickHeaderArchive.PickStatus, dbo_V_PickHeaderArchive.PartDesc, dbo_REF.ITEM_DESC, dbo_V_PickLineArchive.QtyPicked, dbo_V_PickHeaderArchive.PartID, dbo_V_ReasonCode.ReasonDesc, dbo_V_PickHeaderArchive.CreateUserID, dbo_V_AppUser.UserName, dbo_V_PickHeaderArchive.OrderID, dbo_V_PickHeaderArchive.PicklistID, dbo_V_PickHeaderArchive.Assembly, dbo_REF.TOT_ROLCST AS [Part Cost], [Tot_Rolcst]*[QtyPicked] AS [Ext Cost], Trim([orderID]) & "-001" AS [Shop Order], dbo_V_PickHeaderArchive.CreateTime, [Data Spread].[Beg Date], [Data Spread].[End Date]

FROM [Data Spread], (((dbo_V_PickHeaderArchive INNER JOIN dbo_V_PickLineArchive ON (dbo_V_PickHeaderArchive.PartID = dbo_V_PickLineArchive.PartID) AND (dbo_V_PickHeaderArchive.PicklistID = dbo_V_PickLineArchive.PicklistID)) INNER JOIN dbo_V_ReasonCode ON (dbo_V_PickHeaderArchive.ReasonCode = dbo_V_ReasonCode.ReasonCode) AND (dbo_V_PickHeaderArchive.TranType = dbo_V_ReasonCode.TranType)) INNER JOIN dbo_REF ON dbo_V_PickHeaderArchive.PartID = dbo_REF.ITEM) INNER JOIN dbo_V_AppUser ON dbo_V_PickHeaderArchive.CreateUserID = dbo_V_AppUser.UserID

GROUP BY dbo_REF.COST_FMLY, dbo_V_PickHeaderArchive.PickStatus, dbo_V_PickHeaderArchive.PartDesc, dbo_REF.ITEM_DESC, dbo_V_PickLineArchive.QtyPicked, dbo_V_PickHeaderArchive.PartID, dbo_V_ReasonCode.ReasonDesc, dbo_V_PickHeaderArchive.CreateUserID, dbo_V_AppUser.UserName, dbo_V_PickHeaderArchive.OrderID, dbo_V_PickHeaderArchive.PicklistID, dbo_V_PickHeaderArchive.Assembly, dbo_REF.TOT_ROLCST, [Tot_Rolcst]*[QtyPicked], Trim([orderID]) & "-001", dbo_V_PickHeaderArchive.CreateTime, [Data Spread].[Beg Date], [Data Spread].[End Date], dbo_V_PickHeaderArchive.TranType

HAVING (((dbo_V_PickHeaderArchive.PickStatus)="D") AND ((dbo_V_ReasonCode.ReasonDesc)="Defective Part") AND ((dbo_V_PickHeaderArchive.OrderID) Not Like "R*") AND ((dbo_V_PickHeaderArchive.CreateTime)>=[Beg Date] And (dbo_V_PickHeaderArchive.CreateTime)<=[End Date]) AND ((dbo_V_PickHeaderArchive.TranType)="ISSM"))

ORDER BY [Tot_Rolcst]*[QtyPicked] DESC;

Daily

SELECT dbo_REF.COST_FMLY, dbo_V_PickHeader.PickStatus, dbo_V_PickHeader.PartDesc, dbo_REF.ITEM_DESC, dbo_V_PickLine.QtyPicked, dbo_V_PickHeader.PartID, dbo_V_ReasonCode.ReasonDesc, dbo_V_PickHeader.CreateUserID, dbo_V_AppUser.UserName, dbo_V_PickHeader.OrderID, dbo_V_PickHeader.PicklistID, dbo_V_PickHeader.Assembly, dbo_REF.TOT_ROLCST AS [Part Cost], [Tot_Rolcst]*[QtyPicked] AS [Ext Cost], Trim([orderID]) & "-001" AS [Shop Order], dbo_V_PickHeader.CreateTime, [Data Spread].[Beg Date], [Data Spread].[End Date]

FROM [Data Spread], (((dbo_V_PickHeader INNER JOIN dbo_V_PickLine ON (dbo_V_PickHeader.PicklistID = dbo_V_PickLine.PicklistID) AND (dbo_V_PickHeader.PartID = dbo_V_PickLine.PartID)) INNER JOIN dbo_V_ReasonCode ON (dbo_V_PickHeader.TranType = dbo_V_ReasonCode.TranType) AND (dbo_V_PickHeader.ReasonCode = dbo_V_ReasonCode.ReasonCode)) INNER JOIN dbo_REF ON dbo_V_PickHeader.PartID = dbo_REF.ITEM) INNER JOIN dbo_V_AppUser ON dbo_V_PickHeader.CreateUserID = dbo_V_AppUser.UserID

GROUP BY dbo_REF.COST_FMLY, dbo_V_PickHeader.PickStatus, dbo_V_PickHeader.PartDesc, dbo_REF.ITEM_DESC, dbo_V_PickLine.QtyPicked, dbo_V_PickHeader.PartID, dbo_V_ReasonCode.ReasonDesc, dbo_V_PickHeader.CreateUserID, dbo_V_AppUser.UserName, dbo_V_PickHeader.OrderID, dbo_V_PickHeader.PicklistID, dbo_V_PickHeader.Assembly, dbo_REF.TOT_ROLCST, [Tot_Rolcst]*[QtyPicked], Trim([orderID]) & "-001", dbo_V_PickHeader.CreateTime, [Data Spread].[Beg Date], [Data Spread].[End Date], dbo_V_PickHeader.TranType

HAVING (((dbo_V_PickHeader.PickStatus)="D") AND ((dbo_V_ReasonCode.ReasonDesc)="Defective Part") AND ((dbo_V_PickHeader.OrderID) Not Like "R*") AND ((dbo_V_PickHeader.CreateTime)>=[Beg Date] And (dbo_V_PickHeader.CreateTime)<=[End Date]) AND ((dbo_V_PickHeader.TranType)="ISSM"))

ORDER BY [Tot_Rolcst]*[QtyPicked] DESC;

Union

SELECT [COST_FMLY], [PartID],[PartDesc],[ITEM_DESC],[QtyPicked],[Part Cost],[Ext Cost],[CreateTime],[Beg Date],[End Date]

FROM [Window Issues Daily] UNION SELECT [COST_FMLY],[PartID],[PartDesc],[ITEM_DESC],[QtyPicked],[Part Cost],[Ext Cost],[CreateTime],[Beg Date],[End Date] FROM [Window Issues Archive];

Answer 5

We'd be better able to help if you listed the SQL of all three queries.

---

-- Roger Carlson

MS Access MVP 2006-2010

www.rogersaccesslibrary.com

If you want a detailed answer, ask a detailed question!