Solved Query To Find Something Thats Not There (1 Viewer)

LGDGlen

Member
Local time
Today, 05:07
Joined
Jun 29, 2021
Messages
229
Hi All

another query question thats hopefully a bit different but possible.

I have created a very simplified version of what i want to achieve so i can post screen shots/table designs/data and whatever else necessary.

I set up a deliveries table defined as:

1634075773578.png

And is populated as follows:
1634075814733.png


I then set up an invoices table defined as:

1634075869874.png

And is populated as follows:

1634075930923.png

I have connected the tables using the Del_ID as follows:

1634075981227.png


I then queried the data as follows:

Code:
SELECT Delivery.Del_ID, Delivery.Del_Product, Delivery.Del_Customer, Invoices.Inv_Ref
FROM Delivery INNER JOIN Invoices ON Delivery.Del_ID = Invoices.Del_ID;

This gives me the following output:
1634076077444.png


All very straight forward so far. So now what i want to do is have the query ONLY show the delivery records that don't have an invoice with a reference that has INV at the beginning of it. So the end result should be showing Delivery record with the identifier 2 for Prod2 to Cust1

First thing i'm guessing would be in this instance not show the Inv_Ref column and have the query only return unique records, but then thats where i get a bit lost in the logic.

Hope this makes sense. There is a specific business reason for being able to see any deliveries where this is the case in my larger database, but the principle here is identical and is using the basic same data.

Let me know if you want me to upload this sample database i created to test this out or any other information

Kind regards

Glen
 

isladogs

MVP / VIP
Local time
Today, 05:07
Joined
Jan 14, 2017
Messages
18,186
Try using the unmatched query wizard. Basically its a LEFT JOIN with a selected field filter criteria set as Is Null
 

LGDGlen

Member
Local time
Today, 05:07
Joined
Jun 29, 2021
Messages
229
@isladogs i'll take a look, i'm not entirely sure i understand but i'll take a look and see what happens
 

plog

Banishment Pending
Local time
Today, 00:07
Joined
May 11, 2011
Messages
11,613
A = All Delivery Records
B = Delivery records with any Inv_Ref starting with "INV"
C= Delivery records without any Inv_Ref starting with "INV"

A = B + C
C = A - B

You've got A (Delivery), now you need to make a query to find B:

Code:
SELECT Del_ID
FROM Invoices
WHERE Inv_Ref LIKE "INV*"
GROUP BY Del_ID

Let's call that 'sub1'. From there you use a LEFT JOIN query to find records in A, but not in B:

Code:
SELECT Delivery.Del_ID
FROM Delivery LEFT JOIN sub1 ON sub1.Del_ID = Delivery.Del_ID
WHERE sub1.Del_ID IS NULL

That will give you the records you want. You can then use that last query to bring in whatever fields from Delivery you want.
 

LGDGlen

Member
Local time
Today, 05:07
Joined
Jun 29, 2021
Messages
229
@plog brilliant, and makes perfect sense now its laid out in front of me, i can apply this to my actual project to give me the results i want so cool thank you
 

LGDGlen

Member
Local time
Today, 05:07
Joined
Jun 29, 2021
Messages
229
@plog just to confirm i've implemented this in my actual project and its given me the results i was looking for so just wanted to follow up to thank you once again
 

plog

Banishment Pending
Local time
Today, 00:07
Joined
May 11, 2011
Messages
11,613
No problem. Glad you understand it and it worked
 

Users who are viewing this thread

Top Bottom