Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 23rd, 2004, 11:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access subqueries with parameters moved to SQL

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
 
Old February 24th, 2004, 02:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 129
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 28th, 2004, 08:05 PM
Authorized User
 
Join Date: Jul 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)

 
Old February 29th, 2004, 10:44 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 1st, 2004, 11:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old March 1st, 2004, 11:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.)
 
Old March 6th, 2004, 05:02 PM
Authorized User
 
Join Date: Jul 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old March 8th, 2004, 10:40 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old March 10th, 2004, 01:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!!





Similar Threads
Thread Thread Starter Forum Replies Last Post
which is faster Subqueries or Views? lakshminal SQL Language 0 May 1st, 2008 11:20 PM
SubQueries debbiecoates SQL Server 2000 5 October 22nd, 2007 02:04 PM
Multiple subqueries? thf1977 MySQL 1 October 24th, 2006 05:30 PM
Trouble with Subqueries, am I going mad. kim3er SQL Server 2000 3 April 10th, 2005 02:13 PM
subqueries mgdts SQL Server DTS 0 July 28th, 2003 01:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.