Editable query with agregates ?

[AlexT]

Folks

What is the best way to build an editable query when using aggregate
functions ?

I have a typical father / child (1: - :n) design and have a query that
returns the number of child records for each father.

If possible I'd like to show this data in a datasheet and let the
user delete all father that don't have linked childs.

Problem is that my query is not editable due to the use of aggregate
(COUNT) function.

What's the trick to solve this probably very classical problem ?

Regards

-alexT

 

[Marshall Barton]

What is the best way to build an editable query when using aggregate
functions ?

I have a typical father / child (1: - :n) design and have a query that
returns the number of child records for each father.

If possible I'd like to show this data in a datasheet and let the
user delete all father that don't have linked childs.

Problem is that my query is not editable due to the use of aggregate
(COUNT) function.

What's the trick to solve this probably very classical problem ?

Either use a subquery or the DCount function

SELECT F.fatherId, F.father,
DCount("*", "childtbl", "fatherId=" & F.fatherID)
As CountOfChildren
FROM fatherhtbl As F

 

[AlexT]

Hi

Thanks for your answer

As I try to code as much as possible without using Access specific
functions in order to ease an eventual migration towards SQL server /
ADP I will go the "subquery" way.

Would you mind expanding a little bit on the subject ?

Regards

alex

 

[Marshall Barton]

As I try to code as much as possible without using Access specific
functions in order to ease an eventual migration towards SQL server /
ADP I will go the "subquery" way.

Would you mind expanding a little bit on the subject ?

Different approaches have different effects. E.g. a
subquery in an Update query will not work. Give this kind
of thing a try to see if it does what you want.

SELECT F.fatherId, F.father,
(SELECT Count(*)
FROM childtbl As C
WHERE C.fatherId= F.fatherID
) As CountOfChildren
FROM fatherhtbl As F

 

[AlexT]

Hi

thanks for your advises

I have tried both approaches, but unfortunately on both counts Access
refuses to delete any record: DATA IS READ ONLY

Using the DCount function is unacceptably slow. Using a subquery is
much more responsive.

And yes, I have checked the query properties and it reads as
"DYNASET", which is, I believe, what it should be...

Any further suggestion welcome !

Regards

--alexT

 

[Marshall Barton]

I have tried both approaches, but unfortunately on both counts Access
refuses to delete any record: DATA IS READ ONLY

Using the DCount function is unacceptably slow. Using a subquery is
much more responsive.

And yes, I have checked the query properties and it reads as
"DYNASET", which is, I believe, what it should be...

When I tested my posted queries, I was able to delete
records. This implies that you have some additional
conditions that are getting in the way.

Are you sure the table is editable? Try opening the table
in sheet view and see if you can edit a record.

Is there an open form that has locked the table? Try
closing all other queries, forms, reports, etc. and opening
the query directly from the database window.

 

[AlexT]

Hmmm...

I have the database open in exclusive mode. No form open.

The table is editable without problem.

Create a news query from scratch that reads

SELECT Father.artDetails, Father.artIdx, (SELECT Count(*) FROM Child
WHERE Father.artIdx = Child.fatherIdx ) AS CountOfChild
FROM Father;

Open in data sheet. No luck (can't delete, data is read only).

Any further idea ?

Thanks & regards

--alexT

 

[Marshall Barton]

Hmmm...

I have the database open in exclusive mode. No form open.

The table is editable without problem.

Create a news query from scratch that reads

SELECT Father.artDetails, Father.artIdx, (SELECT Count(*) FROM Child
WHERE Father.artIdx = Child.fatherIdx ) AS CountOfChild
FROM Father;

Open in data sheet. No luck (can't delete, data is read only).

Hmmmm sums it up pretty well :-(

The only thought I have at this point is that I made sure my
test included the Father table's primary key, so make sure
the artIdx field is a Unique index.

Beyond that, I'm out of ideas, so if that doesn't help, and
I doubt it will, try reposting your question and maybe
someone else will have some fresh ideas.