My table maintains a log of commission changes. Fields are CommID (Primary Key, AutoNumber), Date, AccountID and CommAmt.
I use this query named "qryAcctComm" to pull the most current CommID:
SELECT tblCommissions.AccountID, Max(tblCommissions.CommID) AS MaxOfCommID
GROUP BY tblCommissions.AccountID;
When I create any other query based on qryAcctComm for example:
SELECT tblCommissions.CommID, tblCommissions.BaseComm
FROM tblCommissions INNER JOIN qryAcctComm ON tblCommissions.CommID = qryAcctComm.MaxOfCommID;
I cannot make updates. I am looking to update BaseComm. I assume it is because of the Max function on the Comm ID. I know I can VBA my way around this but I figured there must be a way to create an updateable query for this.