Determine units in stock available for hire

[questril]

I am developing a database for the hiring of equipment. The 'Order' Form displays the type of equipment required, the number of units of equipment required and the dates between which it is required. I need to display also the number of units of equipment _available_ for hire between those specific dates.
Example. 10 widgets kept for hiring out. Customer A wants to hire 8 widgets between 23 October and 28 October. So if Customer B wants 4 widgets on 25 October the Order form needs to display that we don't have enough stock (as we will only have 2 then that Customer A is not using). But if Customer B can wait until 29 October the form can display that he can have them as Customer A will have returned his by then.
Any ideas?

Thanks - Questril

 

[euskadi]

One way to approach this is to create one table with your widgets, and a second table with records indicating that a widget is hired. Quick example

Widget Table
Widget ID Widget Description
1 Widdgie
2 Widdgie
et cetera

Hire Table
Widget ID Hire_Date_Start Hire_Date_Stop
1 10/1/2001 10/28/2001
1 10/31/2001 11/5/2001
2 10/15/2001 10/20/2001

You should then be able to determine how many widgets are available on any give date or date range by looking for widgets which aren't hired out on those dates.
In this example, you've got 1 widget from 10/1 to 10/15, no widgets from 10/15 to 10/20, 2 widgets 10/29 and 10/30, etc.