#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2010
    Posts
    30
    Rep Power
    7

    Help With Sub Query w/ Multiple tables


    Iím trying to make the follow syntax into a sub query. I really new with making subqueries could someone please take a look at the bottom and help me with this?

    Basically I'm trying to make a subquery from this

    COLLTRANS@.PAYMENTAMOUNT
    COLLTRANS2@.PAYMENTDATE
    COLLACCT@.RECNUM


    My select statement would look like this

    Code:
    SELECT
     	SUM(COLLTRANS@.PAYMENTAMOUNT),
    	COLLTRANS2@.PAYMENTDATE,
    	COLLACCT@.RECNUM
     FROM COLLACCT@
     	INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM
      	INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM
     WHERE 
    	COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16'
    Here I was trying to put the sub queries and comparing them

    Code:
    (SELECT COUNT(ACCT.RECNUM) FROM COLLACCT@ AS ACCT WHERE ACCT.CLIENTNUMBER=ClientNumber AND ACCT.DATEOFREFERRAL BETWEEN Start AND End)
    (SELECT SUM(TRANS.PAYMENTAMOUNT)  FROM COLLTRANS@ AS TRANS WHERE COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16')
    
    SELECT
     	COUNT(COLLACCT@.RECNUM),
    	COLLTRANS@.PAYMENTAMOUNT,
    	COLLTRANS2@.PAYMENTDATE FROM COLLACCT@
     	INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM
      	INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM
    
    (SELECT COUNT(COLLACCT@.RECNUM), COLLTRANS@.PAYMENTAMOUNT, COLLTRANS2@.PAYMENTDATE FROM COLLACCT@ INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM WHERE COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16'))
    Last edited by markroberts; February 1st, 2016 at 04:08 PM.
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,665
    Rep Power
    282
    It is not clear what you trying to accomplish.
    you have 3 tables which you join and trying to get pymentamount, paymentdate and recnum (first script) that statement will give you error because of missing group by part it should be
    Code:
    SELECT
     	SUM(COLLTRANS@.PAYMENTAMOUNT),
    	COLLTRANS2@.PAYMENTDATE,
    	COLLACCT@.RECNUM
     FROM COLLACCT@
     	INNER JOIN 
     		COLLTRANS@ 
     		ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM
      	INNER JOIN COLLTRANS2@ 
      	ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM
     WHERE 
    	COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16'
    group by 	COLLTRANS2@.PAYMENTDATE, COLLACCT@.RECNUM
    Next 2 statements looks fine
    Code:
    (SELECT COUNT(ACCT.RECNUM) 
    	FROM COLLACCT@ AS ACCT 
    	WHERE ACCT.CLIENTNUMBER=ClientNumber 
    	AND ACCT.DATEOFREFERRAL BETWEEN Start AND End)
    
    (SELECT SUM(TRANS.PAYMENTAMOUNT)  
    	FROM COLLTRANS@ AS TRANS 
    	WHERE COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16')
    and last 2 missing group by part.
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

Similar Threads

  1. Query multiple tables
    By Ognjenovic in forum Microsoft Access Help
    Replies: 3
    Last Post: January 25th, 2012, 01:52 PM
  2. Query multiple tables using SQL / ASP
    By mariosuper in forum ASP Development
    Replies: 7
    Last Post: December 18th, 2009, 11:02 AM
  3. Search multiple tables query
    By robert475 in forum Microsoft Access Help
    Replies: 4
    Last Post: November 18th, 2009, 03:56 AM
  4. SQL Query Multiple Tables Question
    By mendir200 in forum SQL Development
    Replies: 6
    Last Post: February 25th, 2009, 05:24 AM
  5. Query multiple tables
    By Snappy Larry in forum Microsoft Access Help
    Replies: 9
    Last Post: May 31st, 2004, 06:38 PM

IMN logo majestic logo threadwatch logo seochat tools logo