Queries meant to limit data end up adding data to query

[russdwright]

I have a single table created from an Excel import (yeah, yeah, I know ... data normalization, etc. ... Doubt that is causing this issue) that I am processing multiple queries on. The idea is for each query to further drill down and then show the data the meets those specific criteria.

Example:
- Total Count = table.field <> "Value"
- Total Count handled by group x = former query + table.field2 = "Value2"
- Etc.

Once I get further down into my queries, the filters increase. The goal is to account for all of the entries and not have repeated entries as I drill down. The problem is, as the queries become more detailed/complex, I run into issues where upon running the query I start seeing records that appeared in earlier queries. In fact, I had to take one query that previously used a LIKE constraint and, after getting updated information on the criteria to use, had to change it to separate queries for each single criteria rather than using ="Value1" OR "Value2" OR "Value x" or Like "Value*" AND <> ("Value X" AND "Value Y") as my arguments. Either time I tried one of these, the results included data that was already in the previous queries I had created.

Is there some kind of limitation to Access in terms of query length that is making this happen? I looked at the SQL Statement of one of these more complex queries and it was less than 600 characters. I have tried both building new queries from scratch as well as building queries off of existing ones and end up with the same result.



Unfortunately, this is for work and they are picky about confidential information, so I cannot share the file. I may be able to share the queries, if you are willing to deal with me changing the names of the fields being used.

[June7]

Parentheses are critical when mixing OR and AND operators.

All these criteria are for one field? This will not work. Probably need to utilize IN() or NOT IN() somewhere and maybe even subqueries.

Really need to provide sample data and sample desired result. Follow instructions at bottom of my post.

[Micron]

IF the criteria applies to one field, you might have success with UNION SELECT DISTINCT?
To create sample data, you could copy a table and use update query to modify fields to D.Duck, M. Mouse... or create field aliases and save that query as a table. Maybe not entirely suitable, but no doubt you get the idea.

[russdwright]

Using IN/NOT IN took care of the issue! Thanks!