Hi,

I want to make an access database but Im struggling with the relations a bit.
I understand the one-to-many and many-to-many concept but seems like there is more then that

I want to make a database that keeps track of things we lend.

I have several items with different properties we lend out:

eg

TANK
-ID
-Volume
-Weight

MACHINE
-ID
-hours
-fueltype

...

I made the following tables to keep track of it:

RENT
-ID
-Customer
-...

One-to- many relationship between these

RENTLINE
-ID
-RENT_ID
-Item


My problem lies with this Item.
I want this Item to have a relation with the ID of the different items we lend out. there needs to be a one-to-many relation here.
one rentline can exist of one TANK OR of one MACHINE.
A TANK or a MACHINE can exist on many orderlines.
So I made 2 one to many relationships from Rentline to the id's of Tank and Machine

the problem is that Access does not let me fill in the rentline with the id of a item. If I fill in the ID of a TANK it says a ID of a machine is needed and vice-versa.

I tried to solve it with an intermediate ITEM table that looks like this :
ITEM
-ID
-Sort ( Tank or Machine)
-ItemID (Id of the tank OR the Machine)

But this generates the same issue as directly in the rentline...

I guess this is pretty common stuff Im asking here but its confusing me like hell :(

thanks in advance for all advice

You need to have one table for the things that you rent. If the items are so different that they require very different attributes, you can create separate tables for tanks and machines to hold their specific attributes. Those tables will be related 1-1 with the Item table. All other relationships will be made with the item table. Nothing except the 1-1 with item will refer to the tank and machine tables.

BTW, it is really poor practice to label all your primary keys with the same name. A better techinque is to incorporate the table name in the primary key name. For example - tblItem - ItemID; tblMachine - MachineID; tblTank - TankID, etc. That way there won't be any confusion as to which table you are referring when you use them as foreign keys.

thanks for the advice.

I renamed my ID's.
Im still strugling with that one - to - one relation.

Ive got a Material table now wich looks like this:

Material
Mat_ID
Mat_Supplier
Mat_PurchaseDate
...
Mat_DetailID

that Detail ID should then be the 1 to 1 with the idea in the machine or tank table.

When thinking about it is quiete normal the DB does not let me make two 1 to 1 relations with relational integrity here...
Do I need to add a field that can contain a ID for every type of material I got?
Is this referential integrity required?

in the future I would like to have the ability for the user to create a new kind of material and the properties of it in another table. eg they recently descided to start renting bulldozers of which a complete other set of data properties needs to be held.

What model would be ideal for this?

Greetings,

( I might need an expert programmer but we are a starting firm without the resources that are required to pay for these expensive guys :). So I'm trying first to build something on my own...)

If I understand, you took Pat's correct advise to create ONE table for the items you rent. And then to have additional tables for each class of item to hold the info unique to that class. You would then have a one to one relation between the ONE table and each class table. In that case the MaterialID becomes a foreign key in the CLASS table you wouldn't have a mat_DetailID in the Material table.

right,

How do I set a foreign key in access?
I can only see Primary key.

thanks for the advice

You don't "set" a FK like you do a PK at least not in table design mode. An FK is simply a field that holds the value of the PK of the corresponding records. So is you have a material record with a MaterialID of 101 then the MaterialID in the Detail record would have a value of 101.

The only place where FKs are identified in any way is in the Relationships window. They are identified as being the other side of a join with a PK.

As has already been pointed out, Mat_DetailID should be removed from the material table. Mat_ID is the primary key of the material table and I presume is defined as an autonumber. When you place Mat_ID in each of the 1-side tables, use the same name "Mat_ID" to make it obvious which table is being pointed to and define the field as a long integer (that's the kind of number an autonumber generates). Define this field as the primary key in all 4 tables. With the keys properly defined, Access will now show 1-1 as the relationship cardinality when you define the relationship in the relationship window. If you don't see 1-1 but see undefined or 1-m, there is something wrong with how the keys are defined.

You don't get to specify the cardinality of a relationship. It is inferred by Jet by analyzing the primary keys and join fields of the relationship.