Query Criteria Dependent on Two Field Values

[xyba]

I want to add criteria to a query where if field A is TRUE and B is FALSE the value/result is TRUE.

I'm sure this is easy but...not for me :/

 

[plog]

I want to add criteria to a query where if field A is TRUE and B is FALSE the value/result is TRUE.

That made sense until the last 5 words: 'the value/result is TRUE'. Huh?

When you said that it makes it seem like its not criteria any more but a calculated result. So, to help me understand can you please demonstrate with data what you would like to occur.

 

[xyba]

That made sense until the last 5 words: 'the value/result is TRUE'. Huh?

When you said that it makes it seem like its not criteria any more but a calculated result. So, to help me understand can you please demonstrate with data what you would like to occur.

Lol, sorry for the confusion.

I have in fact multiple fields that are similar but for ease of example I'll just say four fields. On the form they are checkboxes.

Field A is checked (TRUE) to identify a particular issue has been identified with a product, and Field B is checked when that issue has been resolved. Field C is TRUE when a different issue has been identified and Field D is TRUE when it has been resolved.

I want the query to return only those records where A is TRUE and B is FALSE and the same for fields C and D. As there are multiple fields for the various product issues identified, I can't simply set the criteria for A and C as TRUE and B and D as FALSE, as the query would only return those records where it meets all those criteria, hence my dilemma.

I started typing this reply trying to make things clearer but it's probably less clear now...

 

[plog]

Let me try again:

So, to help me understand can you please demonstrate with data what you would like to occur.

 

[Galaxiom]

WHERE [A] And Not Or [C] And Not [D]

Or in the query designer, put True on A and False on B. Then in another criteria row, True on C and False on D

 

[Galaxiom]

Really though you should be using a different data structure. What you have allows invalid combinations to be stored. You can have B as Yes without A. This means the problem that never happened has been solved. This is a breach of Normalization.

You should be storing a status in one field. Raised, Solved. Preferably store these as a number. This also supports any number of stages of the issue and allows querying with Greater Than, Less Than or Between to find issues at a range of statuses.

Moreover you should be storing this information in a related table. One field is the key to the main Product record, a second stores the IssueID and a third the Status of that issue. This allows any number of issues to be stored. You might also have another primary key field or use a Composite PK on ProductID and IssueID. These fields should be at least be indexed (no duplicates) so might as well form the PK.

The query to find the number of issues and the current status for every product becomes very simple and efficient.

 

[xyba]

The problem is that I have to have a historical record of issues recorded, even if they've been resolved, for quality purposes. I think I've solved it by doing individual queries for each issue.

Thanks all for input.