Simplifying Query

[bs0d]

I've built a query in Access that has a calculated field, combining strings from fields in multiple tables. I'd like to cut down on the length of the query by referencing this field also in the where clause. Is this possible?

For example, current query:

Select
table1.field & " " & table2.field & " " & table 3.field AS MY_FIELD

from table inner join (all the tables by a common key)

Where table1.field & " " & table2.field & " " & table 3.field <> "condition 1", table1.field & " " & table2.field & " " & table 3.field <> "condition 2";

Above, the where clause specifies the full calculated field. I'd like to just refer to it as "MY_FIELD", but it's not recognizing it. Is this possible?

Where MY_FIELD <> "condition 1" and MY_FIELD <> "condition 2";

 

[Joe4]

Access does not like using calculated field names in the criteria in the same query that they are defined/calculated. I am not sure the exact order of events that Access does. Perhaps it does the criteria selection before the calculated fields.

However, you can do want you want you either do this in series of queries, or if you use a sub query.

So, you just have your one query that does all your calculations and creates your calculated fields.
Then you have a second query that uses the first query as the source, and you can reference the calculated field names.

 

[bs0d]

Ok, I think that's a reasonable solution. Establish sort of a "base" query, then a query to filter by criteria and simplified filed names off that.

Thanks Joe.

 

[Joe4]

Yep, you got it.

I use this technique a lot when I have Criteria Selection Forms, where I allow the user to to select from a myriad of criteria, and when actually write the query in VBA behind the scene. Because the queries can be quite lengthy, I often create this "base query" with all the calculated and fields I need, and then in my VBA I simply use:

SELECT *
FROM MyBaseQuery
WHERE ...

So then in VBA, I am only really needing to create the Criteria on the fly, and not the whole entire query.

IMHO, it makes things easier to see, understand, and maintain (at least in that context I described above). There is nothing wrong with basing queries on other queries (of course, you probably want to avoid going overboard and nesting it too many levels deep).