If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using calculated fields in multiple queries
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! |
#2
|
|||
|
|||
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. "Linguist" wrote in message ... 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! |
#3
|
|||
|
|||
Yes, the non-calculated fields also get locked in the queries. Let me check
the relationships between the table and the basic query... "JohnFol" wrote: 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. "Linguist" wrote in message ... 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! |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Linguist wrote:
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. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
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 "Rick Brandt" wrote in message m... Linguist wrote: 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. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#7
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Many-to-many implementation problem | Al Williams | Database Design | 15 | April 29th, 2005 05:19 PM |
multiple make table queries to increase performance | anthonysjo | Running & Setting Up Queries | 1 | April 27th, 2005 01:00 PM |
Need One Query to Search Multiple Date Fields, If Possible | Marcia | Running & Setting Up Queries | 4 | October 24th, 2004 05:28 AM |
Exporting Multiple Queries to 1 Excel Workbook with VBA | Anthony | Running & Setting Up Queries | 2 | June 3rd, 2004 07:59 PM |
Mail Merge two fields that have multiple lines with new line control code | Eric Li | Mailmerge | 7 | May 25th, 2004 06:10 PM |