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:
And is populated as follows:
I then set up an invoices table defined as:
And is populated as follows:
I have connected the tables using the Del_ID as follows:
I then queried the data as follows:
This gives me the following output:
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
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:
And is populated as follows:
I then set up an invoices table defined as:
And is populated as follows:
I have connected the tables using the Del_ID as follows:
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:
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