Ask for Max Cost Only 1

[Trudye]

When I run the statement below it is returning 3 records. I only want the MAXimum Cost. I tried DISTINCT to see if that would help but it didn't. How can I get it to give the the max cost?

strSQL = "SELECT DISTINCT Max(DealDetail_Hdr.COST) AS MaxAppear, DealDetail_Hdr.Item, Deals.From " _
& "FROM (Clients " _
& "INNER JOIN Deals on Clients.ID = Deals.ClientId) " _
& "INNER JOIN DealDetail_Hdr " _
& "ON Deals.DealID = DealDetail_Hdr.DEALID " _
& "WHERE clientid = " & sID & " AND Item = 'Appearance'" _
& " GROUP BY DealDetail_Hdr.Item, Clients.ID, Deals.From"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sMaxRate = rst!MaxAppear
sItem = rst!Item
If IsDate(rst!From) Then
sMaxFrom = rst!From
End If
End If

Thanks
Trudye

 

[Golom]

Your query is giving you the Max(DealDetail_Hdr.COST) for each unique combination of DealDetail_Hdr.Item, Clients.ID, Deals.From.

If you just want the MAX(DealDetail_Hdr.COST) regardless of the value of other fields then eliminate the other fields from your Select clause and get rid of the GROUP BY clause.

 

[PHV]

Something like this ?
strSQL = "SELECT MaxAppear, H.Item, D.From " _
& "FROM (Deals D INNER JOIN DealDetail_Hdr H ON D.DealID = H.DealID) " _
& "INNER JOIN (SELECT Max(DealDetail_Hdr.COST) AS MaxAppear " _
& "FROM Deals INNER JOIN DealDetail_Hdr " _
& "ON Deals.DealID = DealDetail_Hdr.DEALID " _
& "WHERE ClientId = " & sID & " AND Item = 'Appearance'" _
& ") M ON H.COST = M.MaxAppear " _
& "WHERE D.ClientId = " & sID & " AND H.Item = 'Appearance'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

Ok I've got it now the Statement is structured wrong. Because of the way the dbase is structured I may have to write a subquery.
It must be giving me the max for each Deal. Maybe the subquery should be structured to select all of the deals for each person, and then select for that the max cost.

Does that sound about right? Or even close?

The relationships are
Client is linked to Deals based on ID/ClientID
Deal is linked to [Deal Detail] based on DealID

The hierarchy is client, Deals sits below client and [Deal Detail] sits below Deals.

Thanks for responding
Trudye

 

[Trudye]

In an attempt to fix (one of) my SQL problems I created this Subquery. However SQL does not like the JOIN. Also is 'WHERE IN' the correct conjunciton?

strSQL = "SELECT Max(COST) AS MaxCards " _
& "FROM (tDealDetail_Hdr " _
& "Where IN " _
& "(SELECT Item, Deals.From, COST " _
& "FROM (Clients " _
& "INNER JOIN Deals on Clients.ID = Deals.ClientId) " _
& "INNER JOIN tDealDetail_Hdr " _
& "ON tDeals.DealID = tDealDetail_Hdr.DEALID " _
& "WHERE clientid = " & sID & " AND Item = '" & strCards & "')"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sMaxRate = rst!MaCards
sItem = rst!Item
If IsDate(rst!From) Then
sMaxFrom = rst!From
End If
End If


Thanx
Trudye

 

[PHV]

Trudye, have you tried my suggestion ?

 

[Trudye]

Kinda, that what I was trying to do when I created the subquery. The report is Client driven, I have to provide the MAX cost for each product a client has purchased.

So I figured since SQL was giving me multi recs due to the Deals tbl. I would select the multi recs first and then select MAX.

But now I am getting an error on the JOIN. Here is how I ATTEMPTED to do it:


strSQL = "SELECT Max(Deals.From) AS MaxDate " _
& "FROM (Deals " _
& "WHERE IN " _
& "(SELECT Deals.From, tDealDetail_Hdr.Item, tDealDetail_Hdr.COST " _
& "FROM (Clients " _
& "INNER JOIN Deals on Clients.id = Deals.clientid) " _
& "INNER JOIN tDealDetail_Hdr on Deals.dealid = tDealDetail_Hdr.dealid " _
& "WHERE clientid = " & sID & " AND item = '" & strCards & "')"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sItem = rst!Item
sCurrRate = rst!COST
If IsDate(rst!MaxDate) Then
sCurrFrom = rst!MaxDate
End If
GoSub Write_Recs
End If
Return

Thanks PH for responding
Trudye

 

[Golom]

The problem may be with

"FROM (Deals " _
& "WHERE IN "

First you have an open paren that isn't matched with a closing one.

Second, the syntax of the Where should be

WHERE SomeFieldName IN ...

 

[Trudye]

This is my first subquery so I'm having some problems phrasing it properly. I saw the paren inbalance and I changed to WHERE statement. But SQL does not like my new WHERE statement, I'm getting

Syntax Error in Query Expression Cost IN....

Here is the latest;
strSQL = "SELECT Max(COST) AS MaxCards " _
& "FROM tDealDetail_Hdr " _
& "WHERE Cost IN " _
& "(SELECT Item, Deals.From, COST " _
& "FROM (Clients " _
& "INNER JOIN Deals on Clients.ID = Deals.ClientId) " _
& "INNER JOIN tDealDetail_Hdr " _
& "ON tDeals.DealID = tDealDetail_Hdr.DEALID " _
& "WHERE clientid = " & sID & " AND Item = '" & strCards & "')"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sMaxRate = rst!MaCards
sItem = rst!Item
If IsDate(rst!From) Then
sMaxFrom = rst!From
End If
GoSub Write_Recs
End If
Return

 

[PHV]

You may try this:
strSQL = "SELECT MaxCards, H.Item, D.From " _
& "FROM (Deals D INNER JOIN DealDetail_Hdr H ON D.DealID = H.DealID) " _
& "INNER JOIN (SELECT Max(DealDetail_Hdr.COST) AS MaxCards " _
& "FROM Deals INNER JOIN DealDetail_Hdr " _
& "ON Deals.DealID = DealDetail_Hdr.DEALID " _
& "WHERE ClientId = " & sID & " AND Item = '" & strCards & "'" _
& ") M ON H.COST = M.MaxCards " _
& "WHERE D.ClientId = " & sID & " AND H.Item = '" & strCards & "'"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sMaxRate = rst!MaxCards
sItem = rst!Item
If IsDate(rst!From) Then
sMaxFrom = rst!From
End If
GoSub Write_Recs
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

Thanks PH for the suggestion. But which file is "M"?

Also is there a less convoluted way to do this. If not maybe I should just go back to Jet calls for this report. I have not idea how to interpret your statement. If I have to revisit this report to add/change/enhance something I would have not idea how to go about it.


Thanks again,
Trudye

 

[PHV]

D is an alias for Deals
H is an alias for DealDetail_Hdr
M is an alias for an embedded view selecting the max cost for the given client (sID) and the given item (strCards)
this view (M) is joined to DealDetail_Hdr (H) to retrieve the DealID necessary to get the From date.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

Thanks PH for hanging in there with me. I think I'll take the long way around with VBA for this report.

I have enough trouble (as you well know) with apparent views. I don't think I'm ready for hidden or implied views. I hope to get there one day soon with your help and other MVP like you.

Be well,
Trudye

 

[PHV]

BTW, perhaps a simpler way:
strSQL = "SELECT TOP 1 H.COST AS MaxCards, H.Item, D.From " _
& "FROM Deals D INNER JOIN DealDetail_Hdr H ON D.DealID = H.DealID " _
& "WHERE D.ClientId = " & sID & " AND H.Item = '" & strCards & "' " _
& "ORDER BY H.COST DESC"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

Now that I can handle! Thanks for reminding me about TOP 1. This will save me a significant amount of code.

You do good work, take $100.00 out of petty cash and tell them I said it was ok. (lol)

Be well,
Trudye

I'm still be looking for that assignment that will allow me to EASE into subqueries.