CODEWITHSUNDEEP
ms-accessms-access-2010
Eadd22
Eadd22

Reputation: 15

Saving a field's expression for later use in Access

I have a query I've created in MS Access. The query hits up against an enterprise data warehouse via an ODBC. It is for pulling ad-hoc data and needs to remain fluid in nature (ie add/remove fields as needed) as the data requested often varies. This isn't an issue as long the field is a straight pull from a table, I simply add or drop the field from the query. However, I have a number of query fields that are expressions calculated from various table fields. Is there a way to create a "favorites list" of expressions inside this query that would display similar to a table in that the user could drag 'n drop desired pre-written expression into the design grid? I realize I could create a "master" query with all fields in it and they could simply uncheck box in the "show" column for the ones they don't wish to see. But I have found this is very cumbersome since the typical record set winds up being 10-15 fields and I would have 70-80 fields on a "master" query if I were to make one. I'm sure this maybe a little hard to visualize so don't hesitate to ask for further clarification. Any insight/advice is appreciated!

Upvotes: 1

Views: 86

Answers (1)

Heinzi
Heinzi

Reputation: 172220

I suggest that you create two queries. This is similar to your "master query" idea, but without cluttering the column list in the lower half of the query editor.

  1. Create a query ("master") with all (potentially required) fields and expressions. Your users are not allowed to touch that query.

  2. Let your users create their own query, using the master query as the data source (rather than the actual tables). Tell them to only drag and drop those fields to the column list in the lower half of the query editor which they actually need.

That way, you keep the column list of the user's query short, but still allow the user to use your pre-defined expressions when needed.

(Pro tip: For increased performance, you could create that "master query" as a view in your data warehouse, add that view as an attached table and skip step 1.)

Upvotes: 1

Related Questions