Query to select only those with completed criteria

[rocket2000]

Hi,

Hope I could get some assistance here?

I have a form and subform (datasheet) based on 2 tables, Customers and Customer Schedule.
Customer Schedule subform (datasheet) has a tick box named "Completed".

How do I select only those records for customers from this datasheet whose schedule have all been completed? I need to select ONLY those customers where ALL subsequent schedule lines in the datasheet have been marked completed. At the moment my query is selecting ALL customers with completed lines regardless if the last line is still not ticked completed(I do not want this). I only need to query those customers where ALL the datasheet lines have been marked completed from the first record to the last record of the datasheet

How can I separate customers with datasheet records ALL completed from those whose datasheet records may still have one record line not completed (usually the last line).

Any assistance would be greatly appreciated

 

[dhookom]

How about providing table and field names as well as the relationships?

Generically

SELECT [Your various fields]
FROM Customers INNER JOIN CustomerSchedule on cusCusID = cscCusID
WHERE cscCusID IN (SELECT cscCusID FROM CustomerSchedule WHERE Completed = 0);

Duane
Hook'D on Access
MS Access MVP

 

[rocket2000]

Hi Duane,

Thank you for the reply. I have attached link to the database.
Perhaps you could have a look and see on Customer Schedules Form

http://www.mediafire.com/?xe87a66egibw85n

Thanks

 

[dhookom]

Sorry, I am not able to browse to that site.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

In the database you posted, check this query:
qryScheduleComplete

Randy