asked on

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

Paul Jackson

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

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JDL129

ASKER

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

Paul Jackson

see mwvisa1's solution he has added the extra bit needed to my solution

JDL129

ASKER

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

Kevin Cross

No worries. Just glad we could help.
Best regards and happy coding,

Kevin