Solved

SQL Syntax

Posted on 2014-02-10
3
288 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 39

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 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

22 Experts available now in Live!

Get 1:1 Help Now