Design Question

[fenhow]

Hi, I have three tables.
1) Lease Agreement
2) Customers
3) Inventory
The Customers and Inventory table are populated. I am trying to figure out how to relate a customer and an inventory selection to a Lease Agreement.
The idea is one of my customers comes in and buys a truck. I want to associate that customer and truck to a lease agreement. So when I am in the Lease Agreement I can click on the customer name or the truck and see the respective record in the respective table. Am I approaching this correctly? Is there a simple way to do this?
Thank you.
Fen

 

[spikepl]

A customer can have one or more lease agreements
A truck can only belong to one lease agreeement at a time but can be re-leased later?

 

[fenhow]

Yes a customer can have one or more lease agreements. A truck can only belong to one lease agreement but can be potentially released later.
Thanks

 

[spikepl]

It seems a many-to-many would be in order.

tblLeases
----------
LeaseID
CustomerID
TruckID
FromDate
ToDate

would formally store the information about each truck on the most essential level. Instead CustomerID you could have a LeaseAgreementID - encompassing more than one truck and also the customer

 

[fenhow]

Thanks, I am running into a bit of trouble.
On my Lease form I have a lookup field "Stock Number" which pulls from the Inventory table.

In my Inventory table I have a field "Stock Number" What I am trying to do is when I select a "Stock Number" from my lease form it creates a relationship on "Stock Number" so I can add a subform to my Lease Form that shows the Stock detail of the Stock Number selected for that lease.

I am not sure how to explain it...

Thanks..
Fen

 

[spikepl]

How about explaining what "stock number" and "stock detail of stock number" are

 

[fenhow]

Ok sure, the stock number is a unique value assigned to a truck, ie last 6 of VIN. The stock detail would be all other information about that truck like Color, Make, Model etc.
The idea is when I am creating a new lease, I can use the Inventory table to select the truck and relate that truck to that deal. So in the Lease form view I can select from the inventory ie Stock Number and then have a subform on that so we can see the details of the truck assigned to that lease.
Fen

 

[fenhow]

Best case would be if I select a stock number in the Lease Form and when I double click on it it would open the Inventory form and go to that truck.
Fen

 

[jdraw]

fenhow,

A few questions for clarity.

one of my customers comes in and buys a truck.

Perhaps it's just me but leasing and buying are not the same.Was this just a misstatement or do you consider them the same?

You refer to StockID, but mention Truck. Do you lease all sorts of vehicles or just Trucks?

Do you deal with Finances? or Maintenance? Just trying to determine the scope of your application.

Along the line of spike's thinking, I see this for consideration.

Customer -->jncLeaseAgreement<----Inventory--->lkupVehicleType

From experience get your tables and relationships set up and tested to ensure they support your business requirements before getting too deeply into Forms and reports etc.