Question Manage several bookings

[crillux]

I am having problems with a system for lending products.

I have created two tables, one for a new order containing customer name, order no. and booking date. In the other tables I have the products.

Now, my problem is this:
Each order can contain several products, but each product can also be booked in several orders but not at the same time of course.

I started out by trying to add the order no. to the products table, but that works only if the product is booked on one single order. I need to be able to have several bookings for the same product. At the same time, I need to be able to stop several orders of the same product on the same day...

Any suggestions on where to start? Do I need an extra table containing the orders connected to the products?

 

[jzwp22]

Welcome to AWF!

Correct, you need what is called a junction table to tie the products to the orders. Here is example of a structure:



tblProducts
-pkProductID primary key, autonumber
-txtProductName
-currPrice


tblOrders
-pkOrderID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-dteOrder

tblOrderProducts
-pkOrderProductsID primary key autonumber
-fkOrderID foreign key to tblOrders
-fkProductID foreign key to tblProduct
-currPrice (You will probably need to copy this from tblProducts at the time a product is ordered so that you can capture the price of the product at that time. This will allow you the flexibility to increase product prices in your tblProducts without impacting already placed orders. You would accomplish the copying of the price by including the price in a combo box on your product order form and then using an after update event of the combo box to populate the control tied to the price)

 

[crillux]

Thanks!

How is the tblOrderProducts populated? Do I build my forms from all the tables involved?

 

[jzwp22]

You would have a main form based on tblOrders. On that form, you would have a subform that would be based on tblOrderProducts. You would link the main form to the subform via the pkOrderID--fkOrderID relationship. On that form you would have a combo box based on tblProducts.

If you build your relationships in the relationship window before doing the forms, Access should take care of the form/subform linking automatically.

 

[crillux]

And another question on the design of the usage of junction table:
Since this is a system for renting out products, the dates are crucial as I do not want the products to be double booked on the same date. The dates are set in the tblOrders, but should I also link the dateOut and dateIn to the junction table to make it easier to handle queries to secure that the same product is not booked twice on the same date?

 

[jzwp22]

That adds an interesting twist. I think you are right in that you will have to use a couple date fields in the junction table to handle tracking of each product.

You will have to filter the combo box holding the products such that only those available for rental are listed. (i.e. where the most recent Outdate has a not null InDate)

 

[MarkK]

You can also create a table that records a +1 for products returned and a -1 for products loaned or rented. Record said value with a date and time, and a link to the product. Then, for any time past, present, or future, you sum the existing records for date/times < the time for which you'd like to know the availability of the product. If the result is zero for a given date/time the item is, or was, available. Sums to -1 and the item is on loan.

[B]tInOutEvent[/B]
EventID (PK)
ProductID (FK)
DateTime (Past or future date/time)
Value (-1 for loaned or +1 returned)

So to find out if PrioductID 12 will be available at 1PM on 7-Sep-2010 you run this query ...

SELECT SUM(Value) As Available 
FROM tInOutEvent 
WHERE ProductID = 12 
  AND DateTime < #7-Sep-2010 1p#

or

DSum("Value", "tInOutEvent", "Product = 12 AND DateTime < #7-Sep-2010 1p#")