Solved

SQL Syntax

Posted on 2014-02-10
3
291 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
3 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 300 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 73

Assisted Solution

by:sdstuber
sdstuber earned 200 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

786 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