When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 2007

[Lateral]

When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 2007

Hi Guys

I'm a newbie trying to add some functionality to an existing Access 2007 database (running on Windows 10).

I have a table called "Workorders" that I have created a split form for and have customised it so that it had combo boxes etc to enable the user to change the record data....so far so good.

I then needed to add some existing queries to the form so that I could calculate some totals from records in a few other tables. This all works fine EXCEPT that I can no longer change any of the fields in the records.

I am sure that it is something to do with the fact that the 3 queries I added to the form use the "Grouped by" option and based upon my Googling, this is probably the issue.

Here is one of the queries:

SELECT DISTINCTROW [Workorder Labor].WorkorderID, Sum([BillableHours]*[BillingRate]) AS [Labor Total]
FROM [Workorder Labor]
GROUP BY [Workorder Labor].WorkorderID;


How do I get around this issue? I need to have the totals and I also need to have the ability to change the data...

Thanks for any help you can provide.

Regards
Greg

 

[arnelgp]

Re: When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 20

aggregate query is not updateable.

 

[Lateral]

Re: When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 20

Thanks...is there an alternative?

 

[spikepl]

Re: When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 20

Show what you want.

" 3 queries I added to the form" is not exactly clear, since a form normally is based off one table or off one query combining tables/queries.

 

[Lateral]

Re: When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 20

Hi and thanks for your reply.

Here is a screen shot of the query that is actioned on the "Record Source"of t he Form.

 

[spikepl]

Re: When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 20

Your totals queries have no business being in the same pot as the other two

1. You can get a total while displaying a form by clicking on the Totals icon in the Ribbon - I guess this applies to a split form too, but not sure.
2. You can use the =Sum function to sum the values if the specified field and showing it in a textbox (in the header , when you have split form)
3. You can use the Dsum funtion to sum vaslues and display that in a texboxt (in the header , when you have split form)

 

[spikepl]

Re: When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 20

On reflection: using a split form for this is a bit weird. This data would typically be shown in form (customer) and subform (workorders), and the totals would either be collected from the subform and displayed on the main form, or shown on the subform directly using the Totals icon in the ribbon

 

[Lateral]

Re: When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 20

Thanks mate. I'll have a play around with this using your suggestions and I'll let you know how I go.

Thanks

 

[Lateral]

Re: When Using Query With "grouped By"option, I Can't Edit Record In Table, Access 20

I spikepl,

Just to explain why I am doing things this way...I already have a form based upon Customer and a subform showing the WorkOrder attached to that customer (see the attached screenshot).

I want to have the ability to display all of the WorkOrders that are in the database so that I can then select the workorder that I want and update it.