True/false looping only showing the false.

[Ultimateneo]

Hello,



I was wondering as i have a 1 to many relationship. I want to show only the records that fail but never pass.

I have a serial number field and that is the 1 to many relationship, I have the pass fail as a true and false field.

I want to show only the serial numbers that have failed and have no records of passes, for the current month and previous month.

So what is happening is i run some tests and then fail the unit, then i makes a change and run the tests and pass or fail it depending on results.

I would like to show only the results that have no passes in the results.

Thank you for any help provided.

Not sure if i can do this as a query, might have to do it as VBA.

[Micron]

Query should be doable. Your explanation isn't as clear as you think it is, so can you post sample data and clarify the desired result? Or just put False in your pass field criteria? I must be missing something because if it were that simple you probably wouldn't be posting this issue.

[kd2017]

Here's my stab at it:

SELECT Child.ParentId 
FROM   Child 
WHERE  (( ( Child.TestDate ) Between DateSerial(Year(Date()), Month(Date()) - 1, 1) And DateSerial(Year(Date()), Month(Date()) + 1, 0) )) 
GROUP  BY Child.ParentId 
HAVING (( ( Sum(Child.Passed) ) = 0 ));  

Given that True = -1, and False = 0, I did a summation query on the pass/fail field for all the records in the current and previous month and then selected the parent ids (serial number?) of those with a sum of zero.

[Ultimateneo]

Hello kd2017 looks good and seems to work i shall give it some more testing but looks good thank you