Recordset not Updateable in Query (1 Viewer)

[KKilfoil]

I have a select query that includes two related Record Sources; a table, and a query based on that same table that includes an aggregate function (count).

As soon as I included the query, my recordset became non-updateable. (Yes, I know that's what supposed to happen!)

Hoping to find a workaround, I searched this forum and came across this statement from Pat Hartman:

"Queries that include aggregate functions such as Max() are NOT updateable. You need to rework the query so that the Max() value is retrieved via a sub-select. That will probably fix the problem."

What did she mean by a 'sub-select'? and how do I use it?

 

[Pat Hartman]

Any good book on SQL will explain sub-selects. They are also called subqueries or nested queries in some books. My favorite SQL book is "Access Database Design & Programming" by Steven Roman and published by O'Reilly. This book concentrates on Access so I don't have to keep translating the syntax as happens with generic SQL books. The SQL for Dummies book is also pretty good.

Here is an example of a correlated subquery. It finds the invoice number of the invoice with the maximum TotalAmt. This query will return more than one row per customer if the customer has more than one invoice for the maximum amount:

SELECT Customers.Customer, Invoices.InvoiceNumber, Invoices.TotalAmt
FROM Customers INNER JOIN Invoices ON Customers.Customer = Invoices.Customer
Where Invoices.TotalAmt = (SELECT Max(Invoices.TotalAmt) AS MaxOfTotalAmt
FROM Invoices
Where Customers.Customer = Invoices.Customer
GROUP BY Invoices.Customer);

"Correlated" means that the subquery references a field in the main query.

That's all I can tell you without knowing what you are trying to do.

 

[KKilfoil]

Thank you for your reply.

I think I understand what you are saying.

In my case, I was trying to count the number of records matching several criteria on the 'many' side of a 1:M relationship, and show this count on the same form used by the user for record entry/modification of the '1'-side table.

To get around the 'recordset not updatable' problem, I wrote a vba function to return the appropriate count from a summary query instead.