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

x
?
Solved

SQL Syntax

Posted on 2014-02-10
3
Medium Priority
?
299 Views
Last Modified: 2014-02-10
Hey guys,

I am having trouble writing a query to sum some values in table 2 and bring to table 1.

See attached picture. I am getting $97.50 on the transaction side for both records, however I am expecting $32.50 for record 1 and $65.00 for record 2.

Here is my SQL, bold section is the part in question.

ado_Transaction.RecordSource = "select " _
& "POSHEADER.transact, POSHEADER.snum, POSHEADER.statnum, whoclose, timeend, " _
& "case when POSHEADER.finaltotal > 0 then 'Sale' else 'Return' end, " _
& "case when POSHEADER.status = 3 then 'Successful' else 'Failed' end, " _
& "METHODPAY.descript, (select sum(costeach*quan) from dba.posdetail where prodtype in(0,1) and opendate = " & lbl_OpenDate.Caption & " group by posheader.transact) , finaltotal, tax1, whoclose " _
& "from dba.POSHEADER, dba.HOWPAID, dba.METHODPAY " _
& "where POSHEADER.transact = HOWPAID.transact and " _
& "HOWPAID.methodnum = METHODPAY.methodnum and " _
& "HOWPAID.approved = 1 and " _
& "POSHEADER.opendate = " & lbl_OpenDate.Caption _
& "order by POSHEADER.transact"
tables.png
0
Comment
Question by:triphen
[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
3 Comments
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 1200 total points
ID: 39848552
(select sum(costeach*quan) from dba.posdetail where prodtype in(0,1) and opendate = " & lbl_OpenDate.Caption & " and dba.posdetail.transact = posheader.transact group by posheader.transact)
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 39848566
your subquery needs to be correlated to your main query
perhaps something like this...


(  SELECT SUM(costeach * quan)
              FROM dba.posdetail
             WHERE prodtype IN (0, 1) AND opendate =  " & lbl_OpenDate.Caption _
             and posdetail.prodnum = posheader.prodnum
          GROUP BY posheader.transact),


I'm just guessing at what the correlating column might be.  It might be more than one.
But you need to include something in the subquery that restricts the sum to the rows you're interested in

as an aside, you should look into using bind variables instead of string concatenation.  It'll make your development MUCH easier in the long run and more secure as well
0
 

Author Closing Comment

by:triphen
ID: 39848576
Thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

609 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