Filter Records using Update Queries - The Faster Alternative?

[Flo79]

Hello There,

I have been asking myself this question for quite some time and am curious about your experiences:

When Filtering for records based on multiple tables, I found it easy and fast to use a series of update queries.

For example, I have a table of employees and another table with Departments.

If I want to return emplyees in a specific department, I run an update query that marks a Bolean field in the Employees table to false for all employess not belonging to the department entered.

On each additional critiria that I filter for, I use the same logic (i.e. records that do not match my variables are marked as false)

In my result only records marked as True are displayed.

I find this a convenient solution, as all my action queries can be created in vba, which makes my databases easier to manage. This solution also seems to be faster than writing complex select queries.

---------

Is this a common solution?

What do you believe to be the best way to perform complex queries in a filter by form setting?

Can't wait to hear your opinions....

Regards,

Flo79

 

[dhookom]

I don't know how your solution would work in a multi-user situation. I think you are adding a lot of extra steps. Access/Jet queries are optimized to run quickly and doubt you are gaining any noticable increase in performance.

In certain instances, your method might work better than standard queries/sql but I can't imagine what they would be.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Flo79]

dhookom,

Thanks for your feedback,

One example where I found my update method to be better is when the query includes a table with memo fields to be searched.
If I include the search logic for sub-strings in memo fields in my overall search query, performance is decreased significantly. (there are about 90,000 memo fields to be searched alongside other fields in that magnitude)

Regards

Flo79

 

[dhookom]

I am not about to test this but it could be a viable and unique solution. I still don't think this would work in a multi-user application which is almost every application that I create.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]