[Table Relation] Problem

[fahur]

Hi!

I'm new to MS Access databases.

I'm trying to buil a database of a kind of elements. Every element has it's description (name, number, etc), but there are other parameters, which I want to add. I'll try to picture this:

Element_Table--
|
- Date
- Number

Element_Additional_Info--
|
- Data1
- Data2

For every element in Element_Table I want a seperate table Element_Additional_Info.

I tried to do this with relations, but for every element I have the same data in addition info.

Can anyone help me??

Thanks in advance...

--
Best regards
fahur

 

[neileg]

You need to have a unique identifier for each record in the main table. This is known as the primary key. In the second table, each record needs to hold the primary key of the record it relates to in the main table. This is known as a foreign key. The join between the two tables is established by linking the primary key and the foreign key.

However, if you have an exact one to one relationship between the two tables, I don't see the point. Why not have all the data in the same table?

 

[fahur]

You need to have a unique identifier for each record in the main table. This is known as the primary key.

Number field is autonumbered and this is my primary key.

In the second table, each record needs to hold the primary key of the record it relates to in the main table. This is known as a foreign key. The join between the two tables is established by linking the primary key and the foreign key.

However, if you have an exact one to one relationship between the two tables, I don't see the point. Why not have all the data in the same table?

The thing is that the additional info for each element will have several records (some kind of history - what was going on with the element in the past).

So there should be one record in Element_Table and to this record there should be a possibility to add several records from Element_Additional_Info_Table.

Is it possible?

Is it possible to fill such structure with a form?

Thanks in advance...

--
fahur

 

[neileg]

OK, you need to hold the PK as an FK in Element_Additional_Info_Table. If you will have several records per element, they will all have the same FK. Presumably you will sort or select these records by date ?

If you set up a form/subform arrangement with the element data in the form and the additional info in the subform, that should work for you. Providing the PK and the FK fields have the same name in each table, the Access Subform wizard should do the linking for you.