No Field Edit capability
in [General]
|
Prev: Sys table Query to Obtain List of Queries used in Reports
Next: Collect and update via email with a Table/spreadsheet
From: WT on 13 May 2010 17:08 I have a large data file as the parent, left joined to a child table which is many to one. and the parent also joined to a sub query that counts the number of records in the parent that match the child. when I run the query it delivers all the data in a table but it will not allow me to edit or change any of the records. I have tried all the record locking options and also looked in the Tool/options/Advanced tab for those settings and still I am unable to change a field. Help please....... -- Thank you
From: Dirk Goldgar on 13 May 2010 17:19 "WT" <WT(a)discussions.microsoft.com> wrote in message news:EEE2AC4E-51F8-4657-B466-671750D7A493(a)microsoft.com... >I have a large data file as the parent, left joined to a child table which >is > many to one. > and the parent also joined to a sub query that counts the number of > records > in the parent that match the child. > > when I run the query it delivers all the data in a table but it will not > allow me to edit or change any of the records. > I have tried all the record locking options and also looked in the > Tool/options/Advanced tab for those settings and still I am unable to > change > a field. Without seeing the actual SQL it's hard to be sure, but you've probably run afoul of the fact that the Jet/ACE database engine considers any query that includes grouping, or SQL aggregate functions like Count() and Sum(), to be nonupdatable -- even when it doesn't logically have to be. One workaround for this, albeit relatively inefficient, is to use a DCount() expression in your query to get the record count, instead of Count(). -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: WT on 13 May 2010 17:34 Even when I remove the sub query I am unable to edit or change a field in any record. -- Thank you "Dirk Goldgar" wrote: > "WT" <WT(a)discussions.microsoft.com> wrote in message > news:EEE2AC4E-51F8-4657-B466-671750D7A493(a)microsoft.com... > >I have a large data file as the parent, left joined to a child table which > >is > > many to one. > > and the parent also joined to a sub query that counts the number of > > records > > in the parent that match the child. > > > > when I run the query it delivers all the data in a table but it will not > > allow me to edit or change any of the records. > > I have tried all the record locking options and also looked in the > > Tool/options/Advanced tab for those settings and still I am unable to > > change > > a field. > > > Without seeing the actual SQL it's hard to be sure, but you've probably run > afoul of the fact that the Jet/ACE database engine considers any query that > includes grouping, or SQL aggregate functions like Count() and Sum(), to be > nonupdatable -- even when it doesn't logically have to be. > > One workaround for this, albeit relatively inefficient, is to use a DCount() > expression in your query to get the record count, instead of Count(). > > -- > Dirk Goldgar, MS Access MVP > Access tips: www.datagnostics.com/tips.html > > (please reply to the newsgroup) >
From: Dirk Goldgar on 13 May 2010 17:42
"WT" <WT(a)discussions.microsoft.com> wrote in message news:9D859531-E82C-4C66-A66C-27B1A45DF51C(a)microsoft.com... > Even when I remove the sub query I am unable to edit or change a field in > any > record. Please post the SQL of the query. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |