Link to home
Start Free TrialLog in
Avatar of PaulStu
PaulStu

asked on

Query not updateable

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
FROM tblCommissions
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.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>I assume it is because of the Max function on the Comm ID.
Correct.  Using aggregate functions in a query make it non-updatable.
Dont know if this makes it updatable, but try this

SELECT AccountID, CommID
FROM tblCommissions
WHERE CommID IN (SELECT Max(CommID) FROM tblCommissions)
try this:

SELECT tblCommissions.CommID, tblCommissions.BaseComm
FROM tblCommissions where tblCommissions.CommID in (Select MaxOfCmmid from qryqryAcctComm)

This should be updateable.

AW
ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok, so why only a grade of C?  I will be very cautious about offering you answers in the future.

AW
Avatar of PaulStu
PaulStu

ASKER

Thanks for making my first experience at posting a question a negative one.
1) You got the points for what turned out to be a pretty easy question.
2) You had spelling errors in your answer even after you attempted to correct it which I had to correct before I could apply your suggestion.
3) You gave me no explanation of your answer so it does not help me learn from the scenario.

So yes Arthur Wood - GRADE C Average Response for just providing the minimal answer.
I wish I knew how to email or draw the attention to the moderator for your threat!
One would normally ask for additional information if it was deemed to be necessary.  I tend to be rather terse in my answers, and someone who gives a grade of C is almost universally seen as not warrnating further assistance.  We are all volunteers, and I don't 'need' the points (after all, with 10,000,000 points and $4.50 I can go to Starbucks and get a Tall Mocha Latte).

You are definitly now on my 'do not help' list.  ( I was only being 'cautious' before this)

AW