add entry to multiple table using relational model

[Adrien G]

Hi all
I am not new in database design, but I am totally new in using access.
I've been struggling now for 2 days, and i resigned in asking for help.

I have multiple problems, but i think if i solve this one, it will solve 90%
of my problems.

It is a common problem and it is related to adding staff for a store.
On all the example i've seen, the employee, staff, customer information is
grouped into one table. But i've relationalized the model to extract it into
more table.
This means that when i had an employee, i would not update one table but
multiple, and this is where my problem reside.

this is a short view of my model (i had a picture, but i cannot add
attachements)
Table Entity_types (domain table with static data)
entity_type_id (PK)
entity_type (string)

Table Entities
entity_id (PK)
entity_type_id (FK) (many to one with Entity_types)

Table Persons
person_id (PK)
first_name
last_name
dob
other_detail

Table Contacts
contact_id (PK)
phone
email
web
(... Other stuff)

Addresses
address_id (PK)
line_1
line2
city
(....)

Table Staff
staff_id (PK)
entity_id (FK one to one with Entities)
address_id (FK one to one with Addresses)
contract_id (FK with contacts)
person_id (FK with person)
date_joined
date left

So the model is pretty straigh forward.
I could group Address and Contacts but here is not the problem

When i create my form, i import fields from addresses, contacts and persons.
I haven't firgured out a way to update the FKs in staff and entities.
I know i am doing something wrong but i do not know why.

Now the other weird thing, is that when i create the form and try to display
it in view mode, the form shows up as empty. I do not see the labels and
other stuff.
After a lot of time investigating, i have realized that it has something to
do with
the property RecordSet Type for the form properties.
If it is set to Dynaset only, then it does not display,
If it is set to Dynaset (inconsistent....) then it shows up.
Any ideas why?

I think it could be related to my relationships but not sure.

Thanks for your help.
thanks a lot

Adrien

 

[Golfinray]

Anytime that I have run into that problem, it is relationships issues. One
thing I would do is create a query that brings all your information together
into one group. Then see if 1. everything shows up 2. There are no
duplicates. If there is a problem with either then relationships are the
problem. Resolve that and then run the form off the query.

 

[Danny Lesandrini]

Adrien:

If I read you description correctly then I think your question really is:

How does one represent One-To-Many relationships in Access forms?

The answer is ... Subforms.

Try this: Create a form for each table with only that table as a recordsource.
Drag and drop the forms that represent MANY onto the form that represents
the ONE. Set the LinkChildFields and LinkMasterFields to the key field, if
they were not automatically discovered.

Open the form and see what happens.

 

[a a r o n _ k e m p f]

stored procedures allow you to write stuff to multiple tables.

Access (Jet) does not.

You need to upsize to 'Access Data Projects' in order to be able to do
this

 

[Adrien G]

Thanks all for the responses.

On thing i forgot to mention is that i am reaaallllly new with access and i
am discovering a lot of things.
If i could i would use java or other languages, but i am stuck with access
which is a product that i know nothing about.

Now may be i should focus in one problem at a time.

I am not familiar with the recommendation that you are mentioning.
So when i create my form, i have fields from contacts, addresses, and person.

The questions is how do i make sure that data is entered in the staff table
(meaning that the primary keys from the 3 tables are populated into the
staffs table)

thanks a lot

 

[Adrien G]

i forgot to mention
All my tables are empty except a couple dimension tables

thanks

 

[BruceM]

It is best to ignore Aaron. He has one "solution" to every problem.

 

[Aaron Kempf knows nothing]

I need fiber. I am full of S#@^ so I need fiber - badly.