I need help constructting an SQL statement

[syscrash]

I am working with VB6 looking at an Access database and I am trying to setup an SQL statement with a sub-query. My joined tables have multiple lines/rows for an item (DrawHdrID), and I need to collapse them all into one single line, and show the highest number of one column (Request number) and the total of another column (Contract amount). Can someone look at what I have so far and help me get pointed in the right direction? I have been looking at this for days trying to figure out the syntax but I can't seem to get it right. Thanks in advance for any help that you can give in this issue!

strSQLtmpDrawLookup = ""

strSQLtmpDrawLookup = " SELECT JCDrHdr.DrawHdrID, JCDrHdr.ReqNo AS Request, JCDrHdr.PrjNo AS Project, JCDrHdr.JobNo AS Job, JCDES.DesName AS JobName, JCDES.DesLoNo AS LotNo,(Select Sum (ContractTotal)=('RevConAmt','JCDrDtl','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & ' And DrawNo=' & Max(ContractTotal)= ('ReqNo','JCDrHdr','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & '')) AS ContractTotal From JCDrHdr)"

strSQLtmpDrawLookup = strSQLtmpDrawLookup & " FROM (JCDrLndr RIGHT JOIN (JCDrHdr INNER JOIN JCDrDtl ON JCDrHdr.DrawHdrID = JCDrDtl.DrawHdrID) ON JCDrLndr.LenderID = JCDrHdr.LenderID) LEFT JOIN JCDES ON (JCDrHdr.JobNo = JCDES.DesJbNo) AND (JCDrHdr.PrjNo = JCDES.DesPrNo)"


strSQLtmpDrawLookup = strSQLtmpDrawLookup & "
GROUP BY JCDrHdr.DrawHdrID, JCDrHdr.ReqNo, JCDrHdr.PrjNo, JCDrHdr.JobNo, JCDES.DesName, JCDES.DesLoNo"


strSQLtmpDrawLookup = strSQLtmpDrawLookup & "
HAVING (Select Max( (JCDrHdr.ReqNo)=('ReqNo' & 'JCDrHdr' & 'DrawHdrID=' & [JCDrHdr].[DrawHdrID] & ''))as MaxRequest From JCDrHdr)"

Debug.Print strSQLtmpDrawLookup

 

[BobRodes]

This part looks to be where your problem is:

(Select Sum (ContractTotal)=('RevConAmt','JCDrDtl','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & ' And DrawNo=' & Max(ContractTotal)= ('ReqNo','JCDrHdr','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & ''))

First, you have to enclose the strings you're concatenating with the & in quotes, so:

(Select Sum (ContractTotal)=('RevConAmt','JCDrDtl','DrawHdrID='[red]"[/red] & [JCDrHdr].[DrawHdrID] & [red]"[/red]' And DrawNo=' & Max(ContractTotal)= ('ReqNo','JCDrHdr','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & ''))

The rest of it is too unintelligible to provide an answer of any kind, because if I just do the quotes in the manner I've demonstrated, the result looks all wrong.

Suppose you first get the statement working in Access with an example string for "jcdrheader.drawhdrid" and publish it here? Then maybe we can unravel it.

Bob

 

[syscrash]

Hey Bob, thanks for the reply. I can get this to work in Access but I can't use the DMax and DSum statements in VB6 because of DAO, so I was told to use a subquery. Can you (or anyone) help me convert this over to use a subquery instead?

SELECT JCDrHdr.DrawHdrID, JCDrHdr.ReqNo AS Request, JCDrHdr.PrjNo AS Project, JCDrHdr.JobNo AS Job, JCDES.DesName AS JobName, JCDES.DesLoNo AS LotNo, DSum('RevConAmt','JCDrDtl','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & ' And DrawNo=' & DMax('ReqNo','JCDrHdr','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & '')) AS ContractTotal


FROM (JCDrLndr RIGHT JOIN (JCDrHdr INNER JOIN JCDrDtl ON JCDrHdr.DrawHdrID = JCDrDtl.DrawHdrID) ON JCDrLndr.LenderID = JCDrHdr.LenderID) LEFT JOIN JCDES ON (JCDrHdr.JobNo = JCDES.DesJbNo) AND (JCDrHdr.PrjNo = JCDES.DesPrNo)

GROUP BY JCDrHdr.DrawHdrID, JCDrHdr.ReqNo, JCDrHdr.PrjNo, JCDrHdr.JobNo, JCDES.DesName, JCDES.DesLoNo

HAVING (((JCDrHdr.ReqNo)=DMax('ReqNo','JCDrHdr','
DrawHdrID=' & [JCDrHdr].[DrawHdrID] & '')))

 

[BobRodes]

Oh, that thing. So, you want to take your dsum and dmax expressions and translate them into select queries. So, for example, your [tt][blue]DSum('RevConAmt','JCDrDtl','DrawHdrID=' & [JCDrHdr].[DrawHdrID][/blue][/tt] translates into [tt][blue]SELECT SUM(RevConAmt) FROM JCDrDtl WHERE DrawHdrID = [current value of field][/blue][/tt]. So have a go at replacing your dsum and dmax statements with select statements, and making them work that way. I'd suggest you do that first in Access (if you have trouble, you'll probably get better help in the Access forum, too), then try to get your strings right in VB.

 

[syscrash]

Alright thanks Bob, I will give that a try.

 

[BobRodes]

Let us know how you get on.

 

[syscrash]

Thanks Bob. I am just having a hard time with the syntax and finding anyone to help me figure it out. I have never had this hard of a time getting help with an issue.

 

[BobRodes]

Well, have you done what I suggested, or do you need further help with it?

 

[syscrash]

Thanks Bob, I did get it but I had to post on a different site than Tek Tips. And I did do exactly what you suggested, I posted it in the other forum for Access (in Tek Tips). But either way I was able to get some help with it and was able to get moving. Thanks again!

 

[BobRodes]

What did you wind up doing in the end?