creating database - very confused

[Jenny1]

I am creating a very simple database,

It has 3 tables
People
Employment
Organisation

The people table contains:
surname
given name
email - primary key
phone number
course end year

Employment is the linking table it contains:
Role
start year
end year
employer email - foreign key same as email(above)
org_url - foreign key same as url (below

Organisation

orgname
add1
add2
add3
url - primary key

All I want to do is have three tables - enter a persons details in one form

 

[millertimecu32]

Use the form wizard. Then add each of the tables into the form.

 

[neileg]

First of all, I wouldn't use either email or url as a PK. Both are subject to change. Use an autonumber as the PK in all tables and it won't matter one jot if the email or url change and you'll only need to change one record.

To bring together the three tables, join them in a query and base the query on that. Or you could use a form/subform relationship. Which depends on the real world relationship between the data in the tables.

 

[Jenny1]

I have created the forms and linked the tables

Once I add one record through the form, it wont let me add any more records, why is this.

I need to keep email and url as primary keys as the person leading this project doesnt want additional fields 'for fields sakes'

When I click on the form it presents the first record I added but doesnt let me add anymore????

I have attached the file pleaase help?

 

[rainman89]

is your form set to dataentry mode?

 

[Jenny1]

dont know, what do you think oof the attachement

 

[rainman89]

um it does let u add records. are u clicking the arrow with the * next to it?

 

[Jenny1]

sory I was clicking the next button on the subform not the next button for the person details.

Have a look at the relations ships it wont let me change them it automatically sets them to 1 to many but in my case 1 person has 1 employment why wont it let me change this.

Also I am having to enter the url twice once in the organisation table and once in the person table, its not meant to be like this you should only have to enter it once and then it should automatically update the field in the person table

OR maybe I am wrong

 

[rainman89]

first of all your structure is off. take neils advice and add an id field as your pk. itll save you trouble in the long run.
also. dont use spaces in your field names.again. itll save u trouble.

when u enforce referential integrity it automatically creates 1-many. thats why u cannot change it

 

[Jenny1]

but the user doesnt want additional fields.

Is it ok to have a 1-many relationship when I dont want this type of rel.

How do you edit the form to change things like the referential integrity

can you edit using the wizard or do you have to do this manually.


Also I am having to enter the url twice once in the organisation table and once in the person table, its not meant to be like this you should only have to enter it once and then it should automatically update the field in the person table

 

[rainman89]

the id fields would not be seen by the user. they are just reference fields that will help u uniquely identify each record.
can more that one person work at a certain place?

 

[ColinEssex]

but the user doesnt want additional fields.

does the user know how to write databases? or how they work? RainMan is quite correct, the ID is an internal link number nothing else, the user will not know it's there

Col

 

[Jenny1]

the id fields would not be seen by the user. they are just reference fields that will help u uniquely identify each record.
can more that one person work at a certain place?

more than one can work at a certain place

 

[Jenny1]

the id fields would not be seen by the user. they are just reference fields that will help u uniquely identify each record.
can more that one person work at a certain place?

so are you saing I need an auto number ID in both the organisation and people tables?

 

[rainman89]

Should look like this

[B]Person[/B]
person_ID (PK)
surname
givenName
email
phonenumber
courseEndYear
Employment_ID (FK)

[B]Employment [/B]
Employment_ID (PK)
Role
startyear
endyear
employeremail 
org_ID(FK)

[B]Organisation[/B]
org_ID (PK)
orgname
add1
add2
add3
url

 

[GaryPanic]

expanding on Rays outline

you will see each table has a link number to another table (these will not be visible to end user)
and this sort of layout is pretty much standard and what most Access users/programmers would expect to see--
everything works off Primary Key numbers and each record set may contain foerign keys- that are the primary keys in another table

this way you can then be quite creative with reporting - form design

so
persons become numbers

mr Smith becomes number 1
and he belongs to a group =organisation this organisation will have its own number again company mr smith word for is SlowSnails and this company is entered into the table and has a primary number 1
Slow snails may/will have more members to it so it could have 100 employees in it.

get the idea ...

 

[Jenny1]

Thats very clear, thank you guys.

One question, in my old system when the user wanted to add an new contact they first entered the organisation, the system generated an organistaion ID.

When the user then went to enter the person, he/she had to search for the organisation, note down the org_id return to the person form and enter the org_id then enter the rest of the record.

If as you say the linking key is the id, wont this need to be entered at some point and therefore will be seen by the user.

This is why I wanted to use url, because atleast this has some meaning and can be remembered

 

[neileg]

Use a combo box. The first column would be the ID but hidden from the user. The visible column should be the name of the organisation. This way the user selects the meaningful name, but what is stored is the ID.

 

[Jenny1]

Thanks guys,

I have done as you asked, and included the auto number IDs. Now when trying to create the relationships between the tables I keep getting errors saying invalid definitions?

Any Ideas?

 

[Jenny1]

Attached is my updated database, can someone help with the relationships.