Solved

QUERY SYNTAX HELP CONVERSION

Posted on 2001-07-24
5
230 Views
Last Modified: 2006-11-17
Hello I am having a issue with a query that I need help with.  In an older Access database there was a query that called a function that did a sum on a specific field. Here is the original query in Access:
There is two functions called GettblOrderDriversTolls which got a Sum of Tolls for that OrderNumber and GetODExtraChargeDriverAmount that Sumed Charges.  After the query I have pasted the functions.  What I am thinking is that a sub queries  can be used to do this and I need some syntax help.  Even if you could provide me with the way to do it that would be helpful, or I will increase the points if you can provide exact syntax Thanks all. :)


SELECT tblCustomer.CustCode, tblCustomer.CustomerCode, tblSalesPerson.SalesManNAME, tblOrderSalesReps.SalesRepPercent, tblSalesPerson.SalesManCode, Sum(tblOrderSalesReps.SalesRepCompensation) AS SumOfSalesRepCompensation, Count(tblOrders.OrderNumber) AS CountOfOrderNumber, tblCustomer.Contact, Sum(GettblOrderDriversTolls([tblorders].[OrderNumber])) AS Tolls, Sum(tblOrders.OrderTotal) AS SumOfOrderTotal, Sum(GetODExtraChargeDriverAmount([tblOrders].[OrderNumber],0)) AS NonTaxAccCharges, [SumOfOrderTotal]-[Tolls]-[NonTaxAccCharges] AS Net
FROM (tblCustomer INNER JOIN tblOrders ON tblCustomer.CustomerCode = tblOrders.CustomerCode) INNER JOIN (tblSalesPerson INNER JOIN tblOrderSalesReps ON tblSalesPerson.SalesManCode = tblOrderSalesReps.SalesRepCode) ON tblOrders.OrderNumber = tblOrderSalesReps.OrderNumber
WHERE (((tblOrders.PickupDate) Between ParamFromDate(Null) And ParamToDate(Null)) AND ((tblOrders.OrderStatusCode) Not In ("VD","QT")))
GROUP BY tblCustomer.CustCode, tblCustomer.CustomerCode, tblSalesPerson.SalesManNAME, tblOrderSalesReps.SalesRepPercent, tblSalesPerson.SalesManCode, tblCustomer.Contact
HAVING (((tblSalesPerson.SalesManCode) Like ParamSalesmanCode(Null))) ORDER BY tblCustomer.CustCode


Public Function GettblOrderDriversTolls(OrderNumber As Long) As Currency
Dim TempTolls As Currency
Dim rs As New ADODB.Recordset
'foo = OpenDB(dbEngine00)
Set rs = dbEngine00.OpenRecordset("select * from tblOrderDrivers where OrderNumber = " & OrderNumber, dbOpenDynaset)
TempTolls = 0
If rs.RecordCount = 0 Then
    GoTo Exit_Handler
End If
rs.MoveFirst
Do While Not rs.EOF
    TempTolls = TempTolls + rs!Tolls
    rs.MoveNext
Loop
'--------------------------
Exit_Handler:
GettblOrderDriversTolls = TempTolls
rs.Close
Set rs = Nothing
Exit Function

'--------
Err_Handler:
Select Case err
End Select
foo = stdErrMsg(err, Error)
Resume Exit_Handler
Resume
End Function

Public Function GetODExtraChargeDriverAmount(OrderNumber As String, IsTaxable As Integer) As Currency
Dim TempAccChargeAmount As Currency
Dim rs As New ADODB.Recordset
Dim strsql As String
'foo = OpenDB(dbEngine00)
strsql = "select * from tblOrderDrivers where OrderNumber = " & AddQuotes(OrderNumber)
With rs
    Set .ActiveConnection = CurrentProject.Connection()
    .Open strsql
End With
TempAccChargeAmount = 0
If rs.RecordCount = 0 Then
    GoTo Exit_Handler
End If
rs.MoveFirst
Do While Not rs.EOF
    TempAccChargeAmount = TempAccChargeAmount + GetODExtraChargeDriverAmtSub(rs!OrderDriverID, IsTaxable)
    rs.MoveNext
Loop
'--------------------------
Exit_Handler:
GetODExtraChargeDriverAmount = TempAccChargeAmount
rs.Close
Set rs = Nothing
Exit Function

'--------
Err_Handler:
Select Case err
End Select
foo = stdErrMsg(err, Error)
Resume Exit_Handler
Resume
End Function



0
Comment
Question by:mp_lynch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6314990
GettblOrderDriversTolls
this is (for each row)

select sum(Tolls)
from tblOrderDrivers t1
where t1.OrderNumber = tblorders.OrderNumber

so just replace
Sum(GettblOrderDriversTolls([tblorders].[OrderNumber])) AS Tolls
by
(select sum(Tolls) from tblOrderDrivers t1 where t1.OrderNumber = tblorders.OrderNumber) AS Tolls

The other is very similar so you should be able to replace that yourself.

0
 

Author Comment

by:mp_lynch
ID: 6315846
I am getting an error column tblOrders.OrderNumber is invalid in the select list because it is not contained in either an aggregate function or in the Group By clause.  If I add that into the Group By it gives me to many return records for the summation of the numbers.  Any ideas?
0
 

Author Comment

by:mp_lynch
ID: 6315863
I am getting an error column tblOrders.OrderNumber is invalid in the select list because it is not contained in either an aggregate function or in the Group By clause.  If I add that into the Group By it gives me to many return records for the summation of the numbers.  Any ideas?
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6921218
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if still open in seven days.  Please post closing recommendations before that time.

Question(s) below appears to have been abandoned. Your options are:
 
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process for further information, if needed.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE KnowledgePro user, use the Power Search option to find them.  Anytime you have questions which are LOCKED with a Proposed Answer but does not serve your needs, please reject it and add comments as to why.  In addition, when you do grade the question, if the grade is less than an A, please add a comment as to why.  This helps all involved, as well as future persons who may access this item in the future to seek help.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20155693.html
http://www.experts-exchange.com/questions/Q.20156457.html
http://www.experts-exchange.com/questions/Q.20218347.html
http://www.experts-exchange.com/questions/Q.20229738.html
http://www.experts-exchange.com/questions/Q.20229739.html
http://www.experts-exchange.com/questions/Q.20241590.html
http://www.experts-exchange.com/questions/Q.20252917.html
http://www.experts-exchange.com/questions/Q.20229780.html




**** PLEASE DO NOT AWARD THE POINTS TO ME. *****
 
------------>  EXPERTS:  Please leave your closing recommendations if this item remains inactive another seven (7) days.  If you are interested in the cleanup effort, please click this link http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643 
POINTS FOR EXPERTS awaiting comments are listed here -> http://www.experts-exchange.com/commspt/Q.20277028.html
 

Moderators will finalize this question if still open in 7 days, by either moving this to the PAQ (Previously Asked Questions) at zero points, deleting it or awarding expert(s) when recommendations are made, or an independent determination can be made.  Expert input is always appreciated to determine the fair outcome.
 
Thank you everyone.
 
Moondancer
Moderator @ Experts Exchange
0
 
LVL 1

Accepted Solution

by:
Moondancer earned 0 total points
ID: 7084959
Inconclusive, zero response.  100 points refunded, item closed.
Moondancer - EE Moderator
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question