Show all records where multiple fields contain False

[dcdimon]

Hello.

I've been searching the 'net for hours and haven't found a solution for my query question. It's quite possible I'm wording the search incorrectly so I've decided to post it.

I have a table that tracks maintenance reports on forklifts. Each area that is checked can either be good (True) or bad (False). There are 10 areas (fields) that are checked for compliance. I want to be able to query the table to show any records where there is a False response to any of the areas - any reports that have an open maintenance issue. So for every record where there's a False in one of these fields that record would appear in the query.

Is there a way to do this either through an expression or vb scripting? I've tried using the OR constant in the expression builder with the field names, but I'm not getting all records where any of those 10 fields contain a False.

I feel as though I'm missing something simple, but the solution is escaping me at the moment.

Thanks for your help.

[JoeM]

First off, it sounds like your database is not normalized, which make the task harder than it has to be. Instead of having one record with 10 fields, you should have 10 records for each forklift (each area would be a separate record for each forklift).
Then, you could simply do an Aggregate Query, grouping by Forklift and counting the False results.

However, working with data that is not normalized (which is what it sounds like you have), you should be able to use OR statements in your criteria. If using the Query Builder, each criteria under each area would be on a separate line.
If you cannot get it to work, please switch your query to SQL View and copy and paste the your attempt here, so we can see how you have written to see if there are any issues that stand out.

[dcdimon]

I'm not sure how I'd be able to set this up with the forklifts as the fields (which is what I think your suggesting) with what my table is doing.

The table is collecting data from a maintenance form. The form includes the forklift number as well as areas on the forklift to be checked (brakes, hydraulics, wheels, lift, steering, etc). Each form creates a new record with the forklift number and the maintenance check (T/F) result. My current table has fields for the forklift number and each maintenance point as well as some for comments and when a repair was done. How would I turn that into a table where the forklifts are the fields? Or am I missing your point entirely -- which is more than likely because I occasionally get stuck in Excel mode when talking about Access.

[ItsMe]

I'm not sure how I'd be able to set this up with the forklifts as the fields (which is what I think your suggesting) with what my table is doing.

EquipTableExample.zip
.

Here is an example of a table for equipment. In it you will see descriptions of fields with FK. This field is designed to hold a Foreign Key relative to a child table. The child table's Primary Key is represented in tblEquip as a Foreign Key field.

The table example has a Primary Key. This primary key can be represented in other tables as a foreign key. You could have a table to document maintenance. In this table you will have many records that represent an individual EquipmentID. Each time maintence is performed on a specific vehicle a new record is created and the EquipmentID is placed within the new record.

The tblEquip example illustrates fields and what data types may be suitible for the field. It shows how to include descriptive fields within a table. If a specific vehicle's description is "White In Color". You can include a field named "EquipColor" that a user can update. The color description will follow the EquipmentID throughout the data base.

[dcdimon]

ItsMe: My table is already sort of set up that way. The forklift number and other identifying information lives in a table called Equipment. The maintenance table is using the EquipmentID number called from the Equipment table. In my tables the EquipmentID is an actual number and not an auto generated one and is used to link the tables. We did this because the EquipmentID number is used to derive a lot of information and having that visible was more helpful than the random auto generated number. These numbers are unique to each piece of equipment and are not duplicated throughout or facility. Users like seeing the real world number versus the auto generated one. the Equipment ID is our primary key too - or it should be. I'll have to go check on that since there are multiple people working the tables.

The maintenance table contains some date and time info and then the maintenance check area fields. I can send you a copy of my tables, but I'd have to blank out the data as my employer has some proprietary info in there.

I think my problem was that I was only illustrating a portion of my database structure that was germain to my question and not fully explaining how thing were set up.

If our structure could be improved upon then let me know if I need to revisit it.


Thanks again for your help.

[ItsMe]

I like to have my Equipment numbers in a separate column from the primary key of the table. I will use an autonumber type almost exclusively for PK's. Then I will add another column for things like Driver License, Social Security, Forklift Number, etc. All are unique to a person or a forklift but don't always work well in a complex DB as a PK.

I will look over the thread again to see if I am understanding your original question/issue.

[JoeM]

If you cannot get it to work, please switch your query to SQL View and copy and paste the your attempt here, so we can see how you have written to see if there are any issues that stand out.

If you are stuck with that structure, we may be able to help you get the query you were trying to write to work, if you just post it here like I mentioned in my previous post.

[ItsMe]

I have run into this problem before. I will see if I can upload an example. I have solutions that I have created in the past. There may be a better way to do it.

The problem I encounter is creating an OR condition within my WHERE clause. I can do it with AND but for some reason I don't know how with OR.

If someone knows please post an SQL example. Meanwhile I will upload what is probably a workaround.

[JoeM]

The problem I encounter is creating an OR condition within my WHERE clause. I can do it with AND but for some reason I don't know how with OR.

The most common mistake I see is that people try putting all the ORs on one single Criteria line, which is incorrect. ANDs all go on the same Criteria row, but each OR must be on its own, separate line.
Another mistake I have sometimes seen is that people treat boolean expressions (TRUE/FALSE) as text fields, and mistakenly put text qualifiers around "TRUE" or "FALSE".

That is why I would like to see his code, so I can see if anything like that sticks out.

[ItsMe]

The most common mistake I see is that people try putting all the ORs on one single Criteria line, which is incorrect. ANDs all go on the same Criteria row, but each OR must be on its own, separate line.
Another mistake I have sometimes seen is that people treat boolean expressions (TRUE/FALSE) as text fields, and mistakenly put text qualifiers around "TRUE" or "FALSE".

That is why I would like to see his code, so I can see if anything like that sticks out.

Agreed. I think I found the problem... Maybe.

So I went through all of the OR scenarios you mentioned. I need to learn my SQL anyway.

I think the issue is zero vs. negative one.

I can get the OR to work fine on -1 but not a False or zero.

So I have a working model of selecting multiple columns within a recordset if there is a TRUE -1 value but not a False 0 value.

Did I explain that OK?

[ItsMe]

In an effort to help the OP here is some code Iworked up

Dim frm As Form
Set frm = Me.sfrmMaintSumm.Form
Dim bolFalse As Boolean
bolFalse = False
Dim strWhere As Variant
strWhere = ""
strWhere = "(((qryMaintLog.MultiPointPass)= -1))"
strWhere = (strWhere & " OR ") & "[qryMaintLog].[SmokeInspPass] = -1"
strWhere = (strWhere & " OR ") & "[qryMaintLog].[LubeComplete] = -1"
strWhere = (strWhere & " OR ") & "[qryMaintLog].[CertifiedPass] = -1"

frm.RecordSource = "SELECT * " _
                   & "FROM [qryMaintLog]" _
                   & "WHERE " & strWhere _
                   & " ORDER BY [EquipNum];"

It works fine with -1 but not anything else to locate a False yes/no field.
I tried declaring booleans and tried using "LIKE FALSE"
I went to the table and eliminated NULLS
I used the query builder.

So anyway I know a couple things that don't work.

[JoeM]

Agreed. I think I found the problem... Maybe.

So I went through all of the OR scenarios you mentioned. I need to learn my SQL anyway.

I think the issue is zero vs. negative one.

I can get the OR to work fine on -1 but not a False or zero.

So I have a working model of selecting multiple columns within a recordset if there is a TRUE -1 value but not a False 0 value.

Did I explain that OK?

I'd really need to see your code.

[ItsMe]

I'd really need to see your code.

Post # 11

It is not all of the ways I tried. I did not save all of that. What is in post # 11 works. It breaks when I use a boolean or integer or litteral zero (to represent 0) in place of -1

[JoeM]

I'd really need to see your code.

Post # 11

ItsMe,
Not your code, the OP's (dcdimon's) code, specifically the Query Code that he originally tried and couldn't get to work correctly.

[ItsMe]

In post # 11 I was trying to get an SQL where clause to find False or 0 in a yes/no field. For some reason I was letting the Null value trip me up. What it boils down to is what your default is for your Yes No field.

I try to either assign a -1 or a 0 value as a default for any YesNo. This helps to eliminate problems of filtering out your Yes/No fields. Yes/No can have three values. True or False or Null

If you give a Yes/No field a default value of 0, it will apear as False until the user changes it. It won't have the ability to show as Null.

FWIW the below code worked after I took the time to make sure all of the fields had either a Yes or No value. Something I know to watch out for but let it trip me up again.

Dim strWhere As Variant
 
strWhere = ""
strWhere = "(((tblEquip.HaveRegCard)= 0))"
strWhere = (strWhere & " OR ") & "[tblEquip].[InService] = 0"
strWhere = (strWhere & " OR ") & "[tblEquip].[TollTransp] = 0"
strWhere = (strWhere & " OR ") & "[tblEquip].[TitleInHand] = 0"
Me.RecordSource = "SELECT * " _
                  & "FROM [tblEquip]" _
                  & "WHERE " & strWhere _
                  & " ORDER BY [EquipmentID];"