Solved

QUERY SYNTAX HELP CONVERSION

Posted on 2001-07-24
5
226 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
  • 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now