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
Solved

SQL Syntax

Posted on 2014-02-10
3
292 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 74

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…

809 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