Only show if certain checkboxes are true

[JTQ911]

Hi all, I'm still learning so please be gentle lol

I have a few tables, lets say X,Y, and Z each with 10 fields associated with a yes/no checkbox

I want to run a query that will only show show the fields in tables X,Y, and Z that are yes (true).

This seems so easy, but I'm not getting it. If in the criteria I enter "is true" then if all of the fields arent true, nothing shows up.


Please help

 

[namliam]

remove the IS part....

Just put "True"

Only time you use IS, is in checking for nulls "IS NULL" or "IS NOT NULL"

 

[JTQ911]

Ah I guess I should have been more specific. I have a table with a field SN (serial number), Date, and and ten other fields, TEST1, TEST 2,....TEST10. On 8/12/08 the SN may undergo TEST1 and TEST 2. On 8/13/08, the SN may undergo TEST3. I want to run a query that shows the the SN and !! ONLY !! the tests that have been completed. Does this make more sense. I do appreciate all help. Thanks again!

 

[namliam]

Sounds like you need to re-organize your table...
Having a table with 10 test columns usually is not the best solution.

If you would have had a table with:
SN TestNumber TestDate
Then you could do everything you want.... I strongly advice you to look at your design and (re-)consider it...

Also having a column named "Date" is not smart/good practice. Date is a reserved word in Access you shouldnt use reserved words anywhere...

Now if you stick to your design you can make a union query to find what you want.
Select SN, TEST1 as TestDate from YourTable
UNION
Select SN, TEST2 from YourTable
UNION
...
etc.

Save this query in your DB as i.e. qryAllTestDates.

Now you can make a second query:
Select ...
From YourTable
Where SN in (Select SN from qryAllTestDates where TestDate = Date() )

This second query will return all your SNs that have been tested for one or more tests today.

Alternatively you can do something with OR statements...
Select
From Yourtable
where
Test1 = Date()
OR Test2 = Date()
OR ...
etc

I hope this is more like what you need if I understand you correctly

 

[dkinley]

Hey mailman ... I provided what assistance I could on this problem here ...

http://www.access-programmers.co.uk/forums/showthread.php?t=154757

It provides a little more background on why the tables are set up that way. The problem was I don't have '97 and JTQ only has '97.

I wasn't so sure on my query - my weakest spot - but provided an OpenRecordset solution based on the query to report what was what.

-dK

 

[namliam]

You can re-compile your DB from any new(er) version to 97 in the database screen
Tools => Database utilities => Convert Database => 97

So the dates are stages... well... I still think that design should be re-thought...
A state(date) is typicaly something you put into a related table... I dont know how many dates would be related to a single state... If that is one date, then it is SN State Statedate
If there are 3 then add Statedate1 2 and 3.... Probably....

 

[dkinley]

My version only shows that I can go back as far as 2000.

-dK

 

[namliam]

Well that shows the great "backwards compatibility" that M$ gives us

Guess > 2002 versions dont support 97 anymore

 

[dkinley]

JTQ ... here is an alternative solution that is popular for process-based data tracking and reporting ....

Let's say you have a gabillion tables tracking the processes through the checkbox/date method (for systems modeling purposes); these are all linked to the Unit table (tblUnit). This table holds serial number and other information regarding the unit. Add another table and call it .. idk .. UnitStatus (text).

Now let's say you have a checkbox and date field for process #2 for when they are completed (recall you have to have the entering service information for proper modeling); named chkProcess2Complete and chkProcess2CompleteDate.

Now, when you click the checkbox, you can automate some code. On the OnUpdate() event of the checkbox ...

Private Sub chkProcess2Complete_AfterUpdate()
    If chkProcess2Complete = True Then
        chkProcess2CompleteDate =  Now()
        txtUnitStatus = "Process 2 Complete"
    End If
End Sub

All you need to do is set up some sort of coding scheme for your unit status and make sure you have the correct syntax for referencing the unit status field (if using multiple forms) when you do your process updates.

Now, you aren't all off on trying to develop and elaborate query ... just set your query up against tblUnit with the fields of the unit info you want and the UnitStatus field.

Hope that helps.

-dK