I have been strugling with this one for several months now. I have an Access database with lots of queries that reley on subquries that contain parameters. I have migrated about 300 of the queries to MS SQL, (all by hand, yes, I knew about the upsizing wizard).

The ones I did not convert were the ones that contained subquries that had parameters in them. Now, I did convert the subquries themselves to stored procedures, well enough, but I can't use them in another stored procedure like I could if it was a view.

Am I missing a concept here?! How do "most" people deal with Access queires containing parameter queries when they are moving them to MS SQL??!!

Everthing that I have been able to read indicates that you use joins, but my goodness, some of my quires are quite ugley on there own, without haveing to make them all into one big mess of a query.

I have not been successfull at finding any reference, on the web or book (I am up to 5 books on my desk on Access to SQL or T-SQL, or SQL basics), It there a concept to "generaly" convert an Access query that contains a subquery that uses a parameter?


I Solved It! :D:)
See my post farther down about In-Line Functions, Mitch.

__________________
Mitch

Sorry Mitch, can't help. But just to let you know that my experience of converting complex Access queries into VB sql is that it's a real pain. I empathise with you.

Clive Astley

In my conversion, I used Stored Procedures with derived tables or in some cases I used temporary tables (eg. CREATE #work ... INSERT #work). Parameters were passed to the Stored Procedure either in Code or in the Control Property (eg. @FromDate)

Use stored procedures with subqueries and parameters on the subqueries. You the use the stored procedures in access by using ADO. It is more work in a sense, but the end result will be a much faster application.

I do not recommend using temp tables because they waste server resources.



Sal

Thanks pinkandthebrain,

Would it be possible for you to provide an example of this? Would you list the Access SQL and then the MS SQL that uses the derived table so that I can how they look? Thanks.

Are "derived tables" the same thing/concept as "subqueries" and "nested queries"? Are the last 2 the same thing, by the way? (This terminology is the least of my wories, but since it came up, I thought I'd ask.)

Sorry for the delay. Can you post an example of the code you are working with? Thanks.

Yes, I have many of them:

Note: that the references to "Forms!..." is from a field on a form that would be open, I am sure that would become a parameter, right?

Main:

Q-WorkOrderCompletion

SELECT [Q-WorkOrderCompletionTotalOrderQty].QuoteID, [Q-WorkOrderCompletionTotalOrderQty].WOID, [Q-WorkOrderCompletionTotalOrderQty].ECN, [Q-WorkOrderCompletionTotalOrderQty].ComboCustPartNum AS [Customer's Item/Part Number], [Q-WorkOrderCompletionTotalOrderQty].ComboQty AS [Qty Ordered], [Q-WorkOrderCompletionProductionQty].SumOfQtyGood AS [Qty Ran], IIf([SumOfQtyGood]>=[ComboQty],"Yes","No") AS Completed
FROM [Q-WorkOrderCompletionTotalOrderQty] LEFT JOIN [Q-WorkOrderCompletionProductionQty] ON ([Q-WorkOrderCompletionTotalOrderQty].ComboCustPartNum = [Q-WorkOrderCompletionProductionQty].ComboCustPartNum) AND ([Q-WorkOrderCompletionTotalOrderQty].ECN = [Q-WorkOrderCompletionProductionQty].ECN) AND ([Q-WorkOrderCompletionTotalOrderQty].WOID = [Q-WorkOrderCompletionProductionQty].WONum) AND ([Q-WorkOrderCompletionTotalOrderQty].QuoteID = [Q-WorkOrderCompletionProductionQty].QuoteID)
GROUP BY [Q-WorkOrderCompletionTotalOrderQty].QuoteID, [Q-WorkOrderCompletionTotalOrderQty].WOID, [Q-WorkOrderCompletionTotalOrderQty].ECN, [Q-WorkOrderCompletionTotalOrderQty].ComboCustPartNum, [Q-WorkOrderCompletionTotalOrderQty].ComboQty, [Q-WorkOrderCompletionProductionQty].SumOfQtyGood, IIf([SumOfQtyGood]>=[ComboQty],"Yes","No")
HAVING (((IIf([SumOfQtyGood]>=[ComboQty],"Yes","No"))="Yes"));


Uses these 2 queries:

1. Q-WorkOrderCompletionTotalOrderQty

SELECT [T-SetupSheetHistoryCombinationsDetail].QuoteID, [T-SetupSheetHistoryCombinationsDetail].WOID, [T-SetupSheetHistoryCombinationsDetail].ECN, [T-SetupSheetHistoryCombinationsDetail].ComboCustPartNum, [T-SetupSheetHistoryCombinationsDetail].ComboQty
FROM [T-SetupSheetHistoryCombinationsDetail]
GROUP BY [T-SetupSheetHistoryCombinationsDetail].QuoteID, [T-SetupSheetHistoryCombinationsDetail].WOID, [T-SetupSheetHistoryCombinationsDetail].ECN, [T-SetupSheetHistoryCombinationsDetail].ComboCustPartNum, [T-SetupSheetHistoryCombinationsDetail].ComboQty
HAVING ((([T-SetupSheetHistoryCombinationsDetail].QuoteID)=[Forms]![F-SetupSheetShopFloor]![F-SetupSheetHistoryProduction-Subform]![QuoteID]) AND (([T-SetupSheetHistoryCombinationsDetail].WOID)=[Forms]![F-SetupSheetShopFloor]![F-SetupSheetHistoryProduction-Subform]![WONum]) AND (([T-SetupSheetHistoryCombinationsDetail].ECN)=[Forms]![F-SetupSheetShopFloor]![F-SetupSheetHistoryProduction-Subform]![ECN]));

2. Q-WorkOrderCompletionProductionQty

SELECT [T-SetupSheetHistoryProduction].QuoteID, [T-SetupSheetHistoryProduction].WONum, [T-SetupSheetHistoryProduction].ECN, [T-SetupSheetHistoryProduction].ComboCustPartNum, Sum([T-SetupSheetHistoryProduction].QtyGood) AS SumOfQtyGood
FROM [T-SetupSheetHistoryProduction]
GROUP BY [T-SetupSheetHistoryProduction].QuoteID, [T-SetupSheetHistoryProduction].WONum, [T-SetupSheetHistoryProduction].ECN, [T-SetupSheetHistoryProduction].ComboCustPartNum
HAVING ((([T-SetupSheetHistoryProduction].QuoteID)=[Forms]![F-SetupSheetShopFloor]![F-SetupSheetHistoryProduction-Subform]![QuoteID]) AND (([T-SetupSheetHistoryProduction].WONum)=[Forms]![F-SetupSheetShopFloor]![F-SetupSheetHistoryProduction-Subform]![WONum]) AND (([T-SetupSheetHistoryProduction].ECN)=[Forms]![F-SetupSheetShopFloor]![F-SetupSheetHistoryProduction-Subform]![ECN]));


Here is another one (little bigger):

Q-AnalysisOfSetupWTool

SELECT Sum([TotalSetup]/60) AS SetupHrs, Sum([TotalRun]/60) AS RunHrs, Sum([TotalDown]/60) AS DownHrs, Sum([T-SetupSheetHistoryProduction].QtyGood) AS SumOfQtyGood, Sum(([ComboLengthActual]*[QtyGood])/12) AS GoodFeet, Sum((([ComboLengthActual]*[QtyGood])/12)*[AvgWt-Setup-ECN-WOID-Rev]) AS GoodLbs, Sum([T-SetupSheetHistoryProduction].QtyRejected) AS SumOfQtyRejected, Sum(([ComboLengthActual]*[QtyRejected])/12) AS BadFeet, Sum((([ComboLengthActual]*[QtyRejected])/12)*[AvgWt-Setup-ECN-WOID-Rev]) AS BadLbs, Avg([Q-AvgWtPerFt-Setup-ECN-WOID-Rev].[AvgWt-Setup-ECN-WOID-Rev]) AS [AvgOfAvgWt-Setup-ECN-WOID-Rev], Count([T-SetupSheetHistoryProduction].Shift) AS CountOfShift, Sum([TotalScrapTime]/60) AS ScrapHrs
FROM tblArCust INNER JOIN ((((([T-SetupSheetHistoryProduction] INNER JOIN [T-SetupSheetHistoryCombinationsDetail] ON ([T-SetupSheetHistoryProduction].ECN = [T-SetupSheetHistoryCombinationsDetail].ECN) AND ([T-SetupSheetHistoryProduction].WONum = [T-SetupSheetHistoryCombinationsDetail].WOID) AND ([T-SetupSheetHistoryProduction].QuoteID = [T-SetupSheetHistoryCombinationsDetail].QuoteID) AND ([T-SetupSheetHistoryProduction].ComboCustPartNum = [T-SetupSheetHistoryCombinationsDetail].ComboCustPartNum)) INNER JOIN [T-SetupSheetHistoryHeader] ON ([T-SetupSheetHistoryProduction].Revision = [T-SetupSheetHistoryHeader].Revision) AND ([T-SetupSheetHistoryProduction].ECN = [T-SetupSheetHistoryHeader].ECN) AND ([T-SetupSheetHistoryProduction].WONum = [T-SetupSheetHistoryHeader].WOID) AND ([T-SetupSheetHistoryProduction].QuoteID = [T-SetupSheetHistoryHeader].QuoteID)) LEFT JOIN [Q-AvgWtPerFt-Setup-ECN-WOID-Rev] ON ([T-SetupSheetHistoryProduction].Revision = [Q-AvgWtPerFt-Setup-ECN-WOID-Rev].Revision) AND ([T-SetupSheetHistoryProduction].ECN = [Q-AvgWtPerFt-Setup-ECN-WOID-Rev].ECN) AND ([T-SetupSheetHistoryProduction].WONum = [Q-AvgWtPerFt-Setup-ECN-WOID-Rev].WONum) AND ([T-SetupSheetHistoryProduction].QuoteID = [Q-AvgWtPerFt-Setup-ECN-WOID-Rev].QuoteID)) INNER JOIN [T-SetupSheetHistoryHeader] AS [T-SetupSheetHistoryHeader_1] ON ([T-SetupSheetHistoryProduction].Revision = [T-SetupSheetHistoryHeader_1].Revision) AND ([T-SetupSheetHistoryProduction].ECN = [T-SetupSheetHistoryHeader_1].ECN) AND ([T-SetupSheetHistoryProduction].WONum = [T-SetupSheetHistoryHeader_1].WOID) AND ([T-SetupSheetHistoryProduction].QuoteID = [T-SetupSheetHistoryHeader_1].QuoteID)) INNER JOIN [Q-AnalysisOfSetupWithTool-WOIDListByTool] ON ([T-SetupSheetHistoryProduction].Revision = [Q-AnalysisOfSetupWithTool-WOIDListByTool].Revision) AND ([T-SetupSheetHistoryProduction].ECN = [Q-AnalysisOfSetupWithTool-WOIDListByTool].ECN) AND ([T-SetupSheetHistoryProduction].WOIDSub = [Q-AnalysisOfSetupWithTool-WOIDListByTool].WOIDSub) AND ([T-SetupSheetHistoryProduction].WONum = [Q-AnalysisOfSetupWithTool-WOIDListByTool].WOID) AND ([T-SetupSheetHistoryProduction].QuoteID = [Q-AnalysisOfSetupWithTool-WOIDListByTool].QuoteID)) ON tblArCust.CustId = [T-SetupSheetHistoryHeader].CustId
WHERE ((([T-SetupSheetHistoryProduction].Date) Between [begindate] And [enddate]) AND (([T-SetupSheetHistoryProduction].QuoteID) Like [search-SetupID]));

Uses these two:

1. Q-AnalysisOfSetupWithTool-WOIDListByTool

SELECT [T-SetupSheetHistoryToolDetail].QuoteID, [T-SetupSheetHistoryToolDetail].WOID, [T-SetupSheetHistoryToolDetail].WOIDSub, [T-SetupSheetHistoryToolDetail].ECN, [T-SetupSheetHistoryToolDetail].Revision
FROM [T-SetupSheetHistoryToolDetail]
WHERE ((([T-SetupSheetHistoryToolDetail].ActualToolID) Like [search-ToolID]))
GROUP BY [T-SetupSheetHistoryToolDetail].QuoteID, [T-SetupSheetHistoryToolDetail].WOID, [T-SetupSheetHistoryToolDetail].WOIDSub, [T-SetupSheetHistoryToolDetail].ECN, [T-SetupSheetHistoryToolDetail].Revision
ORDER BY [T-SetupSheetHistoryToolDetail].WOID, [T-SetupSheetHistoryToolDetail].WOIDSub, [T-SetupSheetHistoryToolDetail].Revision;

2. Q-AvgWtPerFt-Setup-ECN-WOID-Rev

SELECT [T-SetupSheetHistoryProduction].QuoteID, [T-SetupSheetHistoryProduction].ECN, [T-SetupSheetHistoryProduction].Revision, [T-SetupSheetHistoryProduction].WONum, Avg([T-SetupSheetHistoryProduction].Weight) AS [AvgWt-Setup-ECN-WOID-Rev]
FROM [T-SetupSheetHistoryProduction]
WHERE ((([T-SetupSheetHistoryProduction].Weight)>0 And ([T-SetupSheetHistoryProduction].Weight) Is Not Null))
GROUP BY [T-SetupSheetHistoryProduction].QuoteID, [T-SetupSheetHistoryProduction].ECN, [T-SetupSheetHistoryProduction].Revision, [T-SetupSheetHistoryProduction].WONum;



And here is another:

Q-OpenOrderHistoryProjection

SELECT Sum([TotalSetup]/60) AS SetupHrs, Sum([TotalRun]/60) AS RunHrs, Sum([TotalDown]/60) AS DownHrs, Sum([T-SetupSheetHistoryProduction].QtyGood) AS SumOfQtyGood, Sum(([ComboLengthActual]*[QtyGood])/12) AS GoodFeet, Sum((([ComboLengthActual]*[QtyGood])/12)*[AvgWt-Setup-ECN-WOID-Rev]) AS GoodLbs, Sum([T-SetupSheetHistoryProduction].QtyRejected) AS SumOfQtyRejected, Sum(([ComboLengthActual]*[QtyRejected])/12) AS BadFeet, Sum((([ComboLengthActual]*[QtyRejected])/12)*[AvgWt-Setup-ECN-WOID-Rev]) AS BadLbs, Avg([Q-AvgWtPerFt-Setup-ECN-WOID-Rev].[AvgWt-Setup-ECN-WOID-Rev]) AS [AvgOfAvgWt-Setup-ECN-WOID-Rev], Count([T-SetupSheetHistoryProduction].Shift) AS CountOfShift, Sum([TotalScrapTime]/60) AS ScrapHrs
FROM tblArCust INNER JOIN ((((([T-SetupSheetHistoryProduction] INNER JOIN [T-SetupSheetHistoryCombinationsDetail] ON ([T-SetupSheetHistoryProduction].ECN = [T-SetupSheetHistoryCombinationsDetail].ECN) AND ([T-SetupSheetHistoryProduction].WONum = [T-SetupSheetHistoryCombinationsDetail].WOID) AND ([T-SetupSheetHistoryProduction].QuoteID = [T-SetupSheetHistoryCombinationsDetail].QuoteID) AND ([T-SetupSheetHistoryProduction].ComboCustPartNum = [T-SetupSheetHistoryCombinationsDetail].ComboCustPartNum)) INNER JOIN [T-SetupSheetHistoryHeader] ON ([T-SetupSheetHistoryProduction].Revision = [T-SetupSheetHistoryHeader].Revision) AND ([T-SetupSheetHistoryProduction].ECN = [T-SetupSheetHistoryHeader].ECN) AND ([T-SetupSheetHistoryProduction].WONum = [T-SetupSheetHistoryHeader].WOID) AND ([T-SetupSheetHistoryProduction].QuoteID = [T-SetupSheetHistoryHeader].QuoteID)) LEFT JOIN [Q-AvgWtPerFt-Setup-ECN-WOID-Rev] ON ([T-SetupSheetHistoryProduction].Revision = [Q-AvgWtPerFt-Setup-ECN-WOID-Rev].Revision) AND ([T-SetupSheetHistoryProduction].ECN = [Q-AvgWtPerFt-Setup-ECN-WOID-Rev].ECN) AND ([T-SetupSheetHistoryProduction].WONum = [Q-AvgWtPerFt-Setup-ECN-WOID-Rev].WONum) AND ([T-SetupSheetHistoryProduction].QuoteID = [Q-AvgWtPerFt-Setup-ECN-WOID-Rev].QuoteID)) INNER JOIN [T-SetupSheetHistoryHeader] AS [T-SetupSheetHistoryHeader_1] ON ([T-SetupSheetHistoryProduction].Revision = [T-SetupSheetHistoryHeader_1].Revision) AND ([T-SetupSheetHistoryProduction].ECN = [T-SetupSheetHistoryHeader_1].ECN) AND ([T-SetupSheetHistoryProduction].WONum = [T-SetupSheetHistoryHeader_1].WOID) AND ([T-SetupSheetHistoryProduction].QuoteID = [T-SetupSheetHistoryHeader_1].QuoteID)) INNER JOIN [Q-AnalysisOfSetupWithTool-WOIDListByTool] ON ([T-SetupSheetHistoryProduction].Revision = [Q-AnalysisOfSetupWithTool-WOIDListByTool].Revision) AND ([T-SetupSheetHistoryProduction].ECN = [Q-AnalysisOfSetupWithTool-WOIDListByTool].ECN) AND ([T-SetupSheetHistoryProduction].WOIDSub = [Q-AnalysisOfSetupWithTool-WOIDListByTool].WOIDSub) AND ([T-SetupSheetHistoryProduction].WONum = [Q-AnalysisOfSetupWithTool-WOIDListByTool].WOID) AND ([T-SetupSheetHistoryProduction].QuoteID = [Q-AnalysisOfSetupWithTool-WOIDListByTool].QuoteID)) ON tblArCust.CustId = [T-SetupSheetHistoryHeader].CustId
WHERE ((([T-SetupSheetHistoryProduction].Date) Between [begindate] And [enddate]) AND (([T-SetupSheetHistoryProduction].QuoteID) Like [search-SetupID]));


Uses these two:

1. Q-AnalysisOfSetupWithTool-WOIDListByTool
SELECT [T-SetupSheetHistoryToolDetail].QuoteID, [T-SetupSheetHistoryToolDetail].WOID, [T-SetupSheetHistoryToolDetail].WOIDSub, [T-SetupSheetHistoryToolDetail].ECN, [T-SetupSheetHistoryToolDetail].Revision
FROM [T-SetupSheetHistoryToolDetail]
WHERE ((([T-SetupSheetHistoryToolDetail].ActualToolID) Like [search-ToolID]))
GROUP BY [T-SetupSheetHistoryToolDetail].QuoteID, [T-SetupSheetHistoryToolDetail].WOID, [T-SetupSheetHistoryToolDetail].WOIDSub, [T-SetupSheetHistoryToolDetail].ECN, [T-SetupSheetHistoryToolDetail].Revision
ORDER BY [T-SetupSheetHistoryToolDetail].WOID, [T-SetupSheetHistoryToolDetail].WOIDSub, [T-SetupSheetHistoryToolDetail].Revision;

2. Q-AvgWtPerFt-Setup-ECN-WOID-Rev
SELECT [T-SetupSheetHistoryProduction].QuoteID, [T-SetupSheetHistoryProduction].ECN, [T-SetupSheetHistoryProduction].Revision, [T-SetupSheetHistoryProduction].WONum, Avg([T-SetupSheetHistoryProduction].Weight) AS [AvgWt-Setup-ECN-WOID-Rev]
FROM [T-SetupSheetHistoryProduction]
WHERE ((([T-SetupSheetHistoryProduction].Weight)>0 And ([T-SetupSheetHistoryProduction].Weight) Is Not Null))
GROUP BY [T-SetupSheetHistoryProduction].QuoteID, [T-SetupSheetHistoryProduction].ECN, [T-SetupSheetHistoryProduction].Revision, [T-SetupSheetHistoryProduction].WONum;


Well, these examples are typical of what I am running into and having much trouble with!!

Thanks for all your concideration and help, I appreciate your time and trouble.

Success! :):D:):D:):D

I have been finding that In-Line Functions do work like the parameterized subqueries that I am used to in Access!!!

Good Grief! This took me a long time and a lot of frustration to arrive at! Thanks to all who helped!!