Using calculated fields in multiple queries

[Guest]

I have a database with one basic table. I have several different queries
querying the table for different criteria. I am using the datasheet view of
these queries to enter / update values in certain fields.

All these queries, however, use calculated fields that are the same for any
of the queries. I could create these calculated fields for each of the
queries separately. But the calculations behind the calculated fields are
quite complex, and there are many calculated fields. Therefore, I thought I'd
create a basic query that contains all these "shared" calculated fields I
need for the other queries. This way I could maintain and modify the
calculated fields in one location and reuse them in multiple queries.

So I created a Basic Query containing the fields Calculated1, Calculated2
etc. Then I created QueryA, QueryB, QueryC. For each of them, I added both
the basic Table and the Basic Query, and I added the fields Calculated1,
Calculated2. Everything works fine, the fields and correct values are shown
in the datasheet view of my queries, but: All records are locked.

What should I do differently?

Many thanks in advance for any help!

 

[JohnFol]

A calculated field is always locked.

Do you mean that non-calculated fields are also locked? If so this is more
likerly to do with the joins / relationships between the tables that the
calculations.

 

[Guest]

Yes, the non-calculated fields also get locked in the queries. Let me check
the relationships between the table and the basic query...

 

[Guest]

The join-properties between the main table and the Basic Query, both of which
I have included in my queries, does not show anything that would indicate
that records get locked.

Perhaps the entire approach is wrong? Is it possible to "share" / refer to a
calculated field from one query to the other? I have been trying this, using
the syntax [BasicQuery]![FieldToBeReused], but then I just get a "Please
enter parameter value" message indicating that Access is not able to find the
Basic Query I am referring to.

 

[Rick Brandt]

The join-properties between the main table and the Basic Query, both
of which I have included in my queries, does not show anything that
would indicate that records get locked.

Perhaps the entire approach is wrong? Is it possible to "share" /
refer to a calculated field from one query to the other? I have been
trying this, using the syntax [BasicQuery]![FieldToBeReused], but
then I just get a "Please enter parameter value" message indicating
that Access is not able to find the Basic Query I am referring to.

Any time a query is based on two or more inputs (tables/queries) there is a good
chance that the result set of the query will not be editable. When one of the
inputs is a query the likelyhood is even higher.

There is a help topic that describes some of the rules and work-arounds. I
generally avoid multiple input queries if I require editability.

 

[JohnFol]

One other approach, is to pass the fields to a function for it to do the
complex calculation. It takes 1 table out of the query and allows you to
structure the expression using VBA

The join-properties between the main table and the Basic Query, both
of which I have included in my queries, does not show anything that
would indicate that records get locked.

Perhaps the entire approach is wrong? Is it possible to "share" /
refer to a calculated field from one query to the other? I have been
trying this, using the syntax [BasicQuery]![FieldToBeReused], but
then I just get a "Please enter parameter value" message indicating
that Access is not able to find the Basic Query I am referring to.

Any time a query is based on two or more inputs (tables/queries) there is
a good chance that the result set of the query will not be editable. When
one of the inputs is a query the likelyhood is even higher.

There is a help topic that describes some of the rules and work-arounds.
I generally avoid multiple input queries if I require editability.

 

[Guest]

Thank you both for your replies, they helped me understand the problem better.

I have now simply based all my queries exclusively on the Basic Query and do
not combine the basic table and the Basic Query in the queries anymore. The
Basic Query now contains all fields of the basic table plus all the
calculated fields I need in any of the queries I am looking for. That way, I
can base any other query exclusively on the Basic Query, without having to
reference the basic table. That helped, even though it seems a bit tedious to
have to reference all fields in my basic table in the Basic Query.

Thanks again!