Access SQL Select rows that have value in common with results of condition

1
votes

Let's say I have a simple table, with the following format:

==================================
| ID | Invoice | Box | Delivered |
==================================
|  1 |   AA11  |  1  |   True    |
----------------------------------
|  2 |   AA11  |  2  |   False   |
----------------------------------    
|  3 |   AA22  |  1  |   False   |
----------------------------------
|  4 |   AA33  |  1  |   False   |
----------------------------------    
|  5 |   AA44  |  1  |   True    |
----------------------------------

ID is a unique integer, Invoice is a TEXT field, Box is an Integer, and Delivered being a boolean (or BIT, as it's know in Access).

A query like this gets a list of everything that has been delivered:

SELECT * FROM Deliveries WHERE Delivered = True

However, each invoice can have multiple boxes (as is the case with invoice 1111), and sometimes not all the boxes are delivered at the same time. If a box has been delivered, I would like to be able to get the status of the other boxes with the same invoice number.

I know I can run multiple queries to do this. The one I mentioned above, and then another which loops through all the return results, and then runs another select with Invoice = ####.

Is there a way to do this all in a single query? I think it might be WHERE EXISTS, but I can't find out how to structure the query.

Ideally, I want the rows returned for a single query of the above able to be rows with ID's: 1, 2, and 5. This is the output I am looking for:

==================================
| ID | Invoice | Box | Delivered |
==================================
|  1 |   1111  |  1  |   True    |
----------------------------------
|  2 |   1111  |  2  |   False   |
----------------------------------    
|  5 |   4444  |  1  |   True    |
----------------------------------

So that even though Delivered = False for ID 2, it is still returned because another item with the same invoice number has Delivered = True

When trying out some queries I got the error

Cannot join on memo, ole or hyperlink Object

2
sqlms-accessselectwhereexists

2 Answers

1
votes

Assuming you want something like this

select * from invoice where invoice in (SELECT invoice FROM invoice WHERE Delivered = 'True')

With the nested query your selecting an outputting the invoice numbers for reference in the parent query. Here the output of the nested query is used to 'filter' the results.

You already got it to work, but here is the another way, without changing the table.

SELECT invoice.ID, invoice.Invoice, invoice.Box, invoice.Delivered, invoice_1.Delivered AS Expr1
FROM invoice, invoice AS invoice_1
WHERE (((invoice.Invoice)=[invoice_1].[Invoice]) AND (([invoice_1].[Delivered])=Yes));

You can test it here.

For those getting into the same problem there is an explanation and a solution here

1
votes

Apologies if I messed up the Access syntax:

select id
from T
group by id
having sum(iif(Delivered, 1, 0) < count(*)

Call the above query IC (incomplete). You may want to see all the undelivered data along with the ids:

select * from T
where id in (<IC>) and Delivered = false