Show Invoice number ONLY if all detail records are selected

[tagteam]

I am trying construct a form that will only show an invoice if all the invoice records have been approved. I have created a form that will allow the user to look at each line of an invoice and has a checkbox to approve it. If the user has selected all the boxes it will disappear from the Approval view, which is a query that shows unique values.



The problem is that if all of the lines but one are checked it will also show up in the view for the unapproved invoices AND the approved invoices. I would like to know how to structure a query that shows unique values but ONLY if ALL of the invoice line items have been approved.
Thanks

[pbaldy]

My first thought is a query that finds all invoices with an unchecked detail record, then use the unmatched query wizard to compare the table of invoices to that query.

[June7]

In addition to Paul's suggestion, maybe:

SELECT * FROM tablename WHERE InvoiceID NOT IN (SELECT InvoiceID FROM tablename WHERE check = False);

However, I have known the NOT qualifier to perform badly so maybe:

SELECT * FROM tablename WHERE InvoiceID IN (SELECT InvoiceID FROM tablename GROUP BY InvoiceID HAVING Sum(IIf(Check=False,1,0)) = 0);

Then there is domain aggregate (DLookup, DCount) - beware they can be slow:

SELECT * FROM tablename WHERE DCount("*", "tablename", "Check=False AND InvoiceID=" & [InvoiceID])=0;

[tagteam]

Thanks for your replies,
Pbaldy, I will not have the invoices table to compare against since I am using this stage to prepare the invoice to go in that table

i have a table that holds all line items from a vendor invoice. there will be thousands of vendor invoices and each vendor invoice will have from 3 - 100 lines. A staff member will review the lines and approve them. The problem I am having is if they approve 70 lines and there are 100 for that particular invoice then it shows up in the approved list AND the unapproved list.

I am trying to use the
SELECT * FROM tablename WHERE InvoiceID IN (SELECT InvoiceID FROM tablename GROUP BY InvoiceID HAVING Sum(IIf(Check=False,1,0)) = 0);

but I am not sure what the last part does. I see that it is a subquery but I dont understand this part HAVING Sum(IIf(Check=False,1,0)) = 0);

When I try to run it (with my table and field names) it pops up a dialog box that says Check and if i leave it blank or put true or false it still returns all of the records.

[June7]

What is name of the Yes/No field that is 'checked'? Use that in place of the Check name in my example.

[tagteam]

I see, so you are saying if checkfield = false then it is a 1 and if not a 0. then we are going to sum that and it should equal to 0 to be selected because if it is more than 0 then some of them are false.

I have tried that and it still doesnt work quite right

SELECT Distinct InvoiceNumber FROM tbl_ImportedRepairs WHERE InvoiceNumber
IN
(SELECT InvoiceNumber FROM tbl_ImportedRepairs GROUP BY InvoiceNumber HAVING SUM(IIF([BlakeApproval]=False, 1, 0)=0))

This still shows the invoices that have not been fully approved. So in the end it seems to do the same as a regular Distinct query.

Should i maybe plan to have another table that I move them to when the are all approved?

[June7]

I did a test of that query with my data and it works.

[tagteam]

Maybe my data is different or I am not doing it correctly.

When i try it I still get invoice that only have some of the line items approved. For example, I have one invoice that all lines are approved and one invoice where I only approved four lines but they both show up in the query result. The one with only four items should not show up in the results.

I am not sure how to show you but if I run the subquery as
SELECT InvoiceNumber FROM tbl_ImportedRepairs GROUP BY InvoiceNumber HAVING SUM(IIF([BlakeApproval]=False, 1, 0)=0);

I get the same result, it shows 2 invoices instead of the 1 that it should show.

I have attached a sample of the data

[June7]

The issue is misplaced parens. These work:

SELECT DISTINCT tbl_ImportedRepairs.InvoiceNumber
FROM tbl_ImportedRepairs
WHERE (((tbl_ImportedRepairs.[InvoiceNumber]) In (SELECT InvoiceNumber FROM tbl_ImportedRepairs GROUP BY InvoiceNumber HAVING SUM(IIF([BlakeApproval]=False, 1, 0))=0)));

SELECT tbl_ImportedRepairs.InvoiceNumber
FROM tbl_ImportedRepairs
GROUP BY tbl_ImportedRepairs.InvoiceNumber
HAVING (((Sum(IIf([BlakeApproval]=False,1,0)))=0));

[tagteam]

Thank you, that solved the issue. I am sorry, I am ok and understanding the logic of vba but not so good with all the necessary format.
Thank you for your help.