I need advice on creating a query with totals but where records are still editable. (1 Viewer)

[wmphoto]

Hi all.

I want to create a query based on a table 'X' which is on the 'one' side of a one-to-many relationship with table 'Y'. The purpose of the query is to provide the recordset to a form to edit the records in table X but I want to include totals from the linked records in table Y for information purposes.

Table X is 'Items' (as in financial items to be put into my forecasts), table Y is 'Transactions'. The reason I've done it this way is that I can have an item, the cost of which is spread out over several transaction, for example the item 'Banking Charges (2012)' contains 12 transactions, one for each month, I feel this is neater than just having 12 items in the items table.

Basically, I want the form I use to manage my items (which contains a subform listing the transactions for that item), to also display for information purposes the total of all the transactions, the number of transactions and the date of the first transaction.

I know how to use the 'Totals' function within the query, but it seems that because of the way Access works, this prevents me from editing the underlying data in the Items table. I have looked into SQL Aggregate Functions as one possibility, another possibility is if I can create these totals within the form rather than having to create a query, and then just use the Items table as the source for the form. But my skill-level with Access is fairly basic so I'm unable to understand Microsoft's help files myself.

 

[RainLover]

In your Query do you have a separate Field for the SUM Function.

If not create one like;

MySum (Sum NameOfField)

 

[wmphoto]

Thanks, I've tried that, but I get the error message 'You tried to execute a query that does not include the specified expression 'Item ID' as part of an aggregate function'

That is with a field with this expression:
Total Transactions: Sum([Calculated Transactions]![Cashflow Input])

Calculated transactions by the way, is a query based on the 'Transactions' table that calculates what amount to actually input into my cashflow for each transaction (that being what the 'cashflow input' field contains)

 

[bob fitz]

Hi
I would do this by putting a field for each total required in the Footer Section of the sub form. The Control Source property would need to be set to = Sum(NameOfFieldToBeSummed)
If the subform is shown in data sheet view the footer section will not be shown, but you could show the values they hold in a textbox on the main form.

 

[RainLover]

I think the Sum Function is not your problem.

Copy the Query under a different name. Remove all instances of "SUM".

Open the Query in Datasheet view.

Can you now edit?

 

[wmphoto]

I tried this already, I did have to remove the transactions table from the query too, but then I could edit it.

Bob_fitz's advice of generating the totals in-form rather than in-query works perfectly though... well almost perfectly, one slight niggle that I can live with if it can't be fixed.

If I display the form in split view, the total fields I have create work OK, but only once you've clicked on the record, until you select each record it shows '£0.00' for the total.

One other question, a lot of this seems to have to do with whether a form or query's recordset is set as 'dynaset' or 'dynaset (inconsistent updates). I figure that this refers to how the object generates the records that are displayed in the query/form and I can guess that the other option 'snapshot' just creates a display of the information in the source tables, not a link to the fields themselves, but other than being something to try selecting when you're finding a query isn't editable, what does 'inconsistent updates' mean? That word 'inconsistent' make it sound like it comes with a catch.

 

[RainLover]

I can't answer your question fully but if you read this link it may help.

http://office.microsoft.com/en-us/access-help/edit-data-in-a-query-HA010097876.aspx

 

[bob fitz]

If I display the form in split view, the total fields I have create work OK, but only once you've clicked on the record, until you select each record it shows '£0.00' for the total.

I use A2003 which doen't have forms in split view so I can't test it and could be wrong but I think this could be overcome with a bit of code that sets focus and requeries controls.