Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

QUERY SYNTAX HELP CONVERSION

Posted on 2001-07-24
5
Medium Priority
?
239 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

636 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