Search code examples
sqldatabasems-accessrecordset

Query with subquery is not updateable. Is there a work-around?

I'm working with a database in Microsoft Access, and this is the scenario I'm trying to find a solution for:

Companies send in applications to apply for more capacity. The application can contain multiple smaller capacities which add up to the total capacity of the application. The applications are stored in the table tApplication and the capacities associated with the application are stored in tCapacity.

I wish to present information about the application including the total capacity. Therefore I have a form with the following Data Source:

SELECT tApplication.ID,tApplication.CompanyID, tApplication.statusID,
(SELECT SUM(tCapacity.Capacity) FROM tCapacity WHERE tApplication.ID = tCapacity.ApplicationID) AS TotalCapacity
FROM tApplication;

This query gives me the information I need, but it hinders me from edit anything in the recordset (Using subqueries apparently makes your query not updateable in Microsoft Access). Is there a way around this? I've been playing around with the idea to store the applications and capacities in the same table, but I'm not sure it's a good design choice.

Solution

  • I used DSUM() which worked great. Thank you to June7.

    SELECT tApplication.ID,tApplication.CompanyID, tApplication.statusID,
DSUM( "tCapacity.Capacity" , "tCapacity", "tCapacity.ApplicationID = " & tApplication.ID) AS TotalCapacity
FROM tApplication;