relationship type 1

[davikokar]

Hallo, i'm building a database with different tables. The main table is tbl_Organisation linked to tbl_Person. An Organisation can have 0 person or more persons, but a person has to be in one organisation. After inserting data in the database I realized that there are persons without organisation. This should be impossible because they are created through the organisation form. I don't know exactly what kind of relation would be the most appropriated in this case, to prevent this kind of things to happen. Does someone have a suggestion?

 

[BNPMike]

It's not a relation that's the problem. It's a relationship that you need to define. If you define a relationship between Organsisation in Employee and the key of Organisation you can tick Enforce Referential Integrity and Access will do the necessary. I'd leave the cascades blank.

 

[ZmrAbdulla]

Structure your table like below

TblOrganisation		TblPersons
			     PersID (PrimaryKey)
OrgID   ======>  OrgID (One to many relation)	
OrgName			PersName
More…			  More…
More…			  More…

regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[davikokar]

Hi BnPmike,
I tried to enforce the referental integrity, but I get this message: "relationship must be on the same number of fields with the same data type", the two field that are listed below are: Org_ID (autoanumber key) and Per_OrgID (number)...

 

[ZmrAbdulla]

Hi Kareda,
Change the Org_ID from AutoNumber to something you define and use the same in the Per_orgID. So it wil be matching with same data type.
regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[davikokar]

The problem is that Org_ID is the ID field of the organisation...it has to an autonumber in order to automatically increase every time I add a new organisation... and in the person table I refer to the organisation ID in order to define that a person is related to a specific organisation... if I change the datatype of these two fields the database won't work...

 

[ZmrAbdulla]

I don't prefer use an Autonumber field as Primary Key if you link the field to another table. Your list won't be in the order if you delete any Organisation form the list
Like
001
002
003
005
008
the deleted numbers will be missing. Your code number will be showing 8 orgs and actually 5
Keep it as it is (if you want)and use OrgCode(a shortform of the OgrName) as PrimaryKey and use the same in the Pers Table. I believe I explained well.


Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[davikokar]

I don't really understand... I thought that the only aim of the ID is to be different for each record and then you can use counting function when needed. Another problem if I don't use an autonumber would be that I will have to add manually an ID everytime I add a new organisation... how do you solve this?

 

[KenReay]

karerda

your explanation of how and why to use autonumber of other 'meaningless' id is perfectly correct. Do not change the datatype of the Id field in table tblOrganisation, in the tblPersons table you need the Organisation id column to be a type numeric long (autonumber is a long)

You may need to amnd your data before you can apply referentional integrity, since your data at present already breaks the rules

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[davikokar]

Kenray
I deleted all the blind records (person without a company) and this should have been the problem of breaking integrity rules (are there others?), but the message I get is about data type ("relationship must be on the same number of fields with the same data type"). If I change the Per_OrgID (organization Id in tblperson) to autonumber will it work? considering that they are linked and when I add a new person I do it from a subform in the organization main form.

 

[KenReay]

Hi

from my earlier post "in the tblPersons table you need the Organisation id column to be a type numeric long"

Is it?

DO NOT CHANGE THE Organisation column in tblPersons to autonumber!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[davikokar]

thanks KenReay, now I'm on the right way. My number field was a "double". One question more: number fields that I use to refer to id in other tables are set by access with a default value of 0. For example there is a table with organization types, but if an organization has no type access put a 0 in the common column in tblOrg. Is this correct or the field should just be blank?
thanks

 

[KenReay]

hi

depends what you want, if you want blank (null to be precise), just delete the 0 in default value in table design for the column

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK