Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 23rd, 2004, 11:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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
Reply With Quote
  #2 (permalink)  
Old February 24th, 2004, 02:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Norwich, Norfolk, United Kingdom.
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
Reply With Quote
  #3 (permalink)  
Old February 28th, 2004, 08:05 PM
Authorized User
 
Join Date: Jul 2003
Location: , , Canada.
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)

Reply With Quote
  #4 (permalink)  
Old February 29th, 2004, 10:44 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
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
Reply With Quote
  #5 (permalink)  
Old March 1st, 2004, 11:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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.
Reply With Quote
  #6 (permalink)  
Old March 1st, 2004, 11:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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.)
Reply With Quote
  #7 (permalink)  
Old March 6th, 2004, 05:02 PM
Authorized User
 
Join Date: Jul 2003
Location: , , Canada.
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.

Reply With Quote
  #8 (permalink)  
Old March 8th, 2004, 10:40 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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.
Reply With Quote
  #9 (permalink)  
Old March 10th, 2004, 01:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
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!!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 11:59 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.