I need help re-constructting an SQL statement

[syscrash]

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 anyone help me convert this over to use a subquery (using Max and Sum instead) instead?

Recap of what is happening...My joined tables have multiple lines/rows for an item (DrawHdrID), and I need to collapse them all into one single line PER DrawHdrID, and show the highest number of one column (Request number) and the total of another column (Contract amount)PER DrawHdrID. 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!


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] & '')))

 

[syscrash]

Ok, so maybe I was asking too much before. I am now trying to do this a section at a time. So I have stripped it down andI am just working on the Having statement. Can someone please help me work through this? I just can't seem to get the syntax right.

This is what I have so far and when I try to run it I get a error... Syntax Error (missing operator) in query expression 'Max( (JCDrHdr.ReqNo) FROM JCDrHdr Where DrawHdrID=('ReqNo' & 'JCDrHdr' & 'DrawHdrID=' & " [JCDrHdr].[DrawHdrID] & ''" ))

Here's exactly what I have as of now....
SELECT JCDrHdr.DrawHdrID, JCDrHdr.ReqNo AS Request, JCDrHdr.PrjNo AS Project, JCDrHdr.JobNo AS Job, JCDES.DesName AS JobName, JCDES.DesLoNo AS LotNo


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

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

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

 

[lespaul]

First off, Access uses the HAVING clause incorrectly.

The HAVING clause is used to filter on the aggregate. So if you only want to return records where the sum of the sales field is greater than 10000, that's a HAVING clause.

SELECT State, SUM(Sales) FROM ORDERS HAVING Sum(Sales) > 10000

So, on to your issue. I can't decipher Access JOIN statements, so I'll just try to work in pieces here. You said above:

one single line PER DrawHdrID, and show the highest number of one column (Request number)

I would write a query that gets that information:

SELECT DrawHdrID, Max([Request Number] FROM TableName

If there are other fields in this table that you need for each Request Number, now would be the time to get that information. You can then use that query as part of the JOIN:

SELECT AllYourFields
FROM TableName
INNER JOIN (SELECT DrawHdrID, Max([Request Number] As MaxRequest FROM TableName) As B On TableName.DrawHdrID = B.DrawHdrID and TableName.[Request Number] = B.MaxRequest

That way your "main" table only returns requests that match the MAX request....

You may find this article helpful

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com