Linking tables

[WJReid]

Hi All,

I have a database that includes two tables, one for contract dates and one for vehicles. I need to know which vehicles are available during the contract dates. At the moment, I have fields in the Contract table of ContractNo, ContractStart, ContractEnd. In the Vehicles table, I have VehicleNo, VehicleType. I need to allocate vehicles to contracts, so the vehicle is not available to any other contracts during those allocated dates. Whenever I have a new contract, I need to know which vehicles are available during the contract dates, but I don't know how to go about it. Does anyone have any ideas?

Thanks in advance for any help.

Regards,

Bill

 

[Russell Hauf]

Personally, I'd probably add Vehicle # as a foreign key to your Contracts table. Then you could query on vehicles that do not have a Contract End Date after (Today)...

HTH,

Russell

 

[WJReid]

Hi Russell,

Thanks for the reply. Would it matter if a contract could have many vehicles added to it or if a vehicle could be allocated to many contracts at different times?

Regards,

Bill

 

[Russell Hauf]

Yes, it would matter very much. Since it is very early in the morning here, let me stew on that one for a while. Also, I'm leaving early today so it will most likely be tomorrow...

 

[Russell Hauf]

Hi Bill,

This is hard to determine when I don't know about your system of allocating the vehicles. But if you can put a date/time field in for when the vehicle is reserved from and to (so 2 fields, like OUT and IN), then you should be able to query off of that...

HTH,

Russell