Query using two related tables

[crillux]

I have three tables in my database that I use for renting out products. The products in "tblProducts", the orders in "tblOrders" and a junction table "tblBookinglist" that saves ProductNo (from tblProducts), OrderNo (from tblOrders), and OutDate and Indate.

To be able to produce a list of available products for new orders I want to use a query to populate a listbox.

The query needs to show all the products that exist in the tblProducts but not in the tblBookings (as they are not booked at all). However, if a product exist in tblBookings, it should still be shown if the OutDate and InDate differs from what I am having in my order form.

Is this possible at all to accomplish with a query or do I need to get my nose in to complicated SQL code?

 

[jzwp22]

I need a clarification on this statement:

it should still be shown if the OutDate and InDate differs from what I am having in my order form.

You did not mention a date in tblOrders so what are you referring to?

I assume that OutDate is the date on which the product was rented out while the InDate refers to the date it was returned. Am I correct in my interpretation?

If so, those products available for booking would include all those products in the booking table where both date fields have a value or conversly, you want to exclude those products that have an outdate but no indate. In a query, you would have this:

SELECT tblBookingList.fkProductID
FROM tblBookingList
WHERE (((tblBookingList.OutDate) Is Not Null) AND ((tblBookingList.InDate) Is Null));

Now that you know which products are currently out, you need to exclude them from the list. So another query is necessary


SELECT tblProducts.pkProductID, tblProducts.txtProductName
FROM tblProducts


Now, we'll need to take the above query and exclude the products that are rented out. To do that we'll need to nest the first query into this second query. (first query shown in red below)

SELECT tblProducts.pkProductID, tblProducts.txtProductName
FROM tblProducts
WHERE pkProductID not in (SELECT tblBookingList.fkProductID
FROM tblBookingList
WHERE (((tblBookingList.OutDate) Is Not Null) AND ((tblBookingList.InDate) Is Null)))

 

[crillux]

You did not mention a date in tblOrders so what are you referring to?

My bad. There are dates in tblOrders. Sorry.

I assume that OutDate is the date on which the product was rented out while the InDate refers to the date it was returned. Am I correct in my interpretation?

Yes, you are correct.

Thanks a million for the solution!

 

[jzwp22]

You're welcome. Good luck with your project