Non-Updatable Query Due to Sums (1 Viewer)

[mab9]

I'm struggling with a non-updatable query issue. I have a table at the category level that should allow entry but it needs to pull in information from another query. In this 2nd query, its doing group by/counts/sum's from an item level table. From reading about the dynasets and trying to fix it, it looks like this the major problem.

The item level table has information that is changing based on other user input so I don't think using a make table would work. I tried setting the queries/form to dynaset (inconsistant updates) but no luck there. It still wouldn't allow any changes to the entry field. Any clue on how I could get around this?

Main Table
Category Entry
A. . . . ._____
B. . . . ._____

Item Table
Item Category Sales
1. . . . . .A . . . . $10
2. . . . . .B . . . . $15
3. . . . . .B . . . . $10

This query built using the main table joined with a group by/sum of the item table.
Goal Query for Updates
Category Sales Entry
A. . . . . . . $10. ____
B. . . . . . . $25. ____

 

[Jon K]

.... it needs to pull in information from another query. In this 2nd query, its doing group by/counts/sum's from an item level table.

If a query is linked to a Totals Query, the query will become non-updatable.

Instead of pulling data from a Totals Query, try pulling the data from the table using domain aggregate functions DCount() and DSum(). These functions will not make a query non-updatable, though they are less efficient than a Totals Query.
.

 

[mab9]

Using the domain aggregates was the next thing I was going to try. When I put them in, it was summing up the whole table.

Manager, Category
Ben, A
Ben, B
Ben, C

Each item has both a manager & category assigned to it. Is it possible to use the domain aggregates holding to both of these? I need to have the totals split out per manager, per category.

 

[Pat Hartman]

When creating forms to update tables, the form should be updating the "child" record, not the "parent" record. You are showing "child" data (sales) and even summarizing it but you want to update "parent" data (Entry).

Don't do the DLookup() in the query, do it in the form or better still, create a subform to show the "child" data. Then you can create a footer that sums the data as well as showing the details. That will allow both tables to be updated without issue.

 

[mab9]

Resolved

Don't do the DLookup() in the query, do it in the form or better still, create a subform to show the "child" data. Then you can create a footer that sums the data as well as showing the details. That will allow both tables to be updated without issue.

This is the route I went, works perfectly. Thanks!