Solved

SQL Syntax

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2003 form, automatically input query parameter from list 5 43
Query Records that don't match 8 32
SQL Query 34 79
SQL Statement to Update Email Domain 2 18
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

943 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

5 Experts available now in Live!

Get 1:1 Help Now