WHERE for a single column on a JOIN?-CodePudding

I have an SQL statement that's working with one exception.

RewardSQL = "SELECT tblMembers.ID, Max(tblRewards.IssueDate) AS LastReward, Last(tblRewards.IssueAmount) AS LastRewardAmount FROM tblMembers INNER JOIN tblRewards ON tblMembers.ID = tblRewards.CustomerID GROUP BY tblMembers.ID HAVING ((([Forms]![frmRewards]![txtID]) = [tblMembers]![ID])) "
DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))
DoCmd.Requery "lstCustomers"

I'm trying to get the MAX of Issue Date, which is working, and the most recent Issue Amount, which isn't.

Neither LAST nor MAX is giving me the most recent $$ for the query. It's giving me the highest amount.

I figured I could accomplish it with a WHERE clause, but I'm just not sure where to put it.

Ideas? Thanks!

EDIT: Example data:

IssueDate     IssueAmount
4/1            5.00
4/2            4.00
4/3            3.00
4/4            2.00
4/5            1.00

Currently the statement is pulling the highest date (4/5) and the highest amount (5.00). I need to pull the highest date (4/5) and the most recent amount (1.00).

CodePudding user response：

This query might do it (but I do not have clue how to do that in ms-access):

SELECT
   tblMembers.ID, 
   tblRewards.IssueDate,
   tblRewards.IssueAmount
FROM tblMembers 
INNER JOIN tblRewards ON tblMembers.ID = tblRewards.CustomerID 
WHERE tblRewards.IssueDate = (
   SELECT
      Max(tblRewards.IssueDate) AS LastReward
   FROM tblMembers 
   INNER JOIN tblRewards ON tblMembers.ID = tblRewards.CustomerID)

CodePudding user response：

If I understand this correctly, you want the amount on the most recent date. If you order the query by IssueDate descending, the record you want should be the first (TOP) row.

SELECT TOP 1
    m.ID,
    r.IssueDate,
    r.IssueAmount
FROM
    tblMembers AS m
    INNER JOIN tblRewards AS r
    ON m.ID = r.CustomerID
WHERE m.ID = [Forms]![frmRewards]![txtID]
ORDER BY r.IssueDate DESC