Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Error multi-part can't be bound

When I run the code below it works fine.  I just need to add pulling the data from the Charges field in tblTXSummary.

Thanks guys,

Jerry
SELECT Distinct Q.DATEOFSALE, C.ZIP, C.STATE, C.CITY, C.ADDRESS, C.LNAME, Q.ID, C.FNAME, Q.TOTAL, C.[CURRENT], C.OVER30, C.OVER60,
        C.OVER90, C.LASTPAIDAMT, C.DATELASTPAID, Q.TXNUM, Q.[DESC], Q.ITEMPRICE, C.CKEY, C.Fullname, C.BeginningBALANCE, Q.STATEMENTDT,
        [current]+[Over30]+[Over60]+[Over90] AS CurrentBalance, C.ADDRESS2, Q.[TYPE], Q.ID FROM tblCustomers as C
        Left Join (SELECT S.STATEMENTDT, S.DATEOFSALE, S.HOH, D.ID, S.TOTAL, D.ITEMPRICE , S.[Type], S.TXNUM, D.[Desc]
        FROM tblTXsummary as S INNER JOIN tblTXdetail as D ON S.TXNUM = D.TXNUM Where S.STATEMENTDT Is Null 
        AND S.[TYPE] in ('CHG','PAYMENT','ADJUSTMENT')) as Q ON C.CKEY = Q.HOH WHERE ([current]+[Over30]+[Over60]+[Over90])>= @Min AND C.HSACard = 'False' order by 1,2

Open in new window

0
JDL129
Asked:
JDL129
  • 2
  • 2
  • 2
1 Solution
 
Paul JacksonCommented:
SELECT Distinct Q.DATEOFSALE, C.ZIP, C.STATE, C.CITY, C.ADDRESS, C.LNAME, Q.ID, C.FNAME, Q.TOTAL, C.[CURRENT], C.OVER30, C.OVER60,
        C.OVER90, C.LASTPAIDAMT, C.DATELASTPAID, Q.TXNUM, Q.[DESC], Q.ITEMPRICE, C.CKEY, C.Fullname, C.BeginningBALANCE, Q.STATEMENTDT,
        [current]+[Over30]+[Over60]+[Over90] AS CurrentBalance, C.ADDRESS2, Q.[TYPE], Q.ID FROM tblCustomers as C
        Left Join (SELECT S.STATEMENTDT, S.DATEOFSALE, S.HOH, D.ID, S.TOTAL, D.ITEMPRICE , S.[Type], S.TXNUM, D.[Desc],
S.[Charges]

        FROM tblTXsummary as S INNER JOIN tblTXdetail as D ON S.TXNUM = D.TXNUM Where S.STATEMENTDT Is Null  
        AND S.[TYPE] in ('CHG','PAYMENT','ADJUSTMENT')) as Q ON C.CKEY = Q.HOH WHERE ([current]+[Over30]+[Over60]+[Over90])>= @Min AND C.HSACard = 'False' order by 1,2
0
 
Kevin CrossChief Technology OfficerCommented:
As shown above, you will need to add the column in the derived table query against the tblTXsummary, just remember on the outside you have to refer to it through the Q alias.

SELECT DISTINCT Q.DATEOFSALE, C.ZIP, C.STATE, C.CITY, C.ADDRESS, C.LNAME, Q.ID
     , C.FNAME, Q.TOTAL, C.[CURRENT], C.OVER30, C.OVER60, C.OVER90, C.LASTPAIDAMT
     , C.DATELASTPAID, Q.TXNUM, Q.[DESC], Q.ITEMPRICE, C.CKEY, C.Fullname, C.BeginningBALANCE
     , Q.STATEMENTDT, [current]+[Over30]+[Over60]+[Over90] AS CurrentBalance, C.ADDRESS2
     , Q.[TYPE], Q.ID, Q.Charges
FROM tblCustomers as C
LEFT JOIN (
   SELECT S.STATEMENTDT, S.DATEOFSALE, S.HOH, D.ID, S.TOTAL
        , D.ITEMPRICE , S.[Type], S.TXNUM, D.[Desc], S.Charges
   FROM tblTXsummary as S
   INNER JOIN tblTXdetail as D ON S.TXNUM = D.TXNUM
   WHERE S.STATEMENTDT Is Null
      AND S.[TYPE] in ('CHG','PAYMENT','ADJUSTMENT')
) as Q ON C.CKEY = Q.HOH
WHERE ([current]+[Over30]+[Over60]+[Over90])>= @Min
   AND C.HSACard = 'False'
ORDER BY 1,2
0
 
JDL129Author Commented:
jacko72!!!!
Thanks for the response!!

Unfortunately it does not return a column.  There is not a column in the returned query name Charges.
The 'Q.ID' is the last column.  It looks like it only pulls data from the fields After 'Select Distinct' and before 'From Customer'

Thanks again,

Jerry
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Paul JacksonCommented:
see mwvisa1's solution he has added the extra bit needed to my solution
0
 
JDL129Author Commented:
mwvisa1 and jack072 thank you both for your responses. I apoligize that I didn't see mwvisa1's response the first time I looked.

Thanks again,
Jerry
0
 
Kevin CrossChief Technology OfficerCommented:
No worries.  Just glad we could help.
Best regards and happy coding,

Kevin
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now