linking multiple tables for form and subforms

[hjgwright]

Hi.
I'm quite new to Access (97) and have a problem.

I have the following tables:
1. Company (with CompanyID, Company, Addrs_ID, HomeAdd_ID, Notes)
2. Address (Addrs_ID, Address1-5 etc)
3. Home Address (HomeAdd_ID, Address 1-5 etc)
4. People (PeopleID, Name, Surname, Addrs_ID - lookup to Addrs_ID in Address table, HomeAdd_ID - lookup to HomeAdd_ID in Home Address table)
4. People/Address (PeopleAdd_ID, Addrs_ID, PeopleID)
5. People/Home Address (PeopleHome_ID, HomeAdd_ID, PeopleID)
6. Employee (Employee_ID, PeopleID, HomeAdd_ID, Addrs_ID, CompanyID)

Relationships:
One to many, ref. integrity:
1. CompanyID in Company to CompanyID in Employee
2. Addrs_ID in Address to Addrs_ID in People/Address
3. HomeAdd_ID in Home Address to HomeAdd_ID in People/Home Address
4. PeopleID in People to PeopleID in People/Address, People/Home Address, Employee

I need to be able to match the company to the employee (which I've done using the Company and Employee tables in a query, it works fine.
Then I need to match the Home Address to the PeopleID. It works fine until I match up the company/employee details, too. The info comes up, but I can't add anything new (in datasheet format, it doesn't have a blank line at the bottom).

Is this because I've got relationships misconfigured?

I want to use a query which looks at the CompanyID form on my main form (I must set up a subform with the Employee Home Address details on the Company form which has company address details).

I don't know how to go any further. I've tried various things, creating a table with CompanyID, PeopleID, EmployeeID, HomeAdd_ID and Addrs_ID, and setting up its relationships with the relevant tables, but it hasn't worked.

Thanks,
HW.

 

[RichMorrison]

When you join 2 tables that have a one-to-many relationship, the result set of the query is not updateable. That's because Access no longer "knows" which copy of the one side to update.

One-to-many relationships that require updates are ususally accomplished with a main form and a subform.

RichM

 

[Pat Hartman]

You need to rethink how you are handling the addresses. Your current structure uses two tables where it should use only one. It looks like you are trying to create two many-to-many relationships with tables 4 and 5 but you have put the address ID's in the company and person tables so this doesn't make any sense. Try one of the following:

1. Company (with CompanyID, Company, AddrID, Notes)
2. Address (AddrID, Address1-5 etc)
3. not needed
4. People (PeopleID, Name, Surname, AddID, EmployeeID, CompanyID)
4. not needed
5. not needed
6. not needed

One to many, ref. integrity:
1. CompanyID in Company to CompanyID in People
2. AddrID in Address to AddrID in People
3. AddrID in Address to AddrID in Company
4. n/a


or if you really want a many-to-many relationship

1. Company (with CompanyID, Notes)
2. Address (AddrID, Address1-5 etc)
3. not needed
4. People (PeopleID, Name, Surname, EmployeeID, CompanyID)
4. CompanyAddress (CompanyID, AddrID)
5. PeopleAddress (PeopleID, AddrsID)
6. not needed

One to many, ref. integrity:
1. CompanyID in Company to CompanyID in People
2. CompanyID in CompanyAddress to CompanyID in Company
AddrID in CompanyAddress to AddrID in Address
3. PeopleID in PeopleAddress to PeopleID in People
AddrID in PeopleAddress to AddrID in Address
4. n/a

Suggestions, get rid of the underscores unless you really like them. If you keep them, be consistant with their usage. ie, if you want to see something_ID, use that format for EVERY ID. Do not under any conditions use embedded spaces or special characters EXCEPT the underscore in table/column/form/report, etc. names.

Using either of the two suggested structures, you will need to join people to company to address to obtain a person's work address.

I eliminated the separate employee table and added an employee id to the people table. If the EmployeeID has a value, then the person is an employee. If the EmployeeID is null, the person is a contact or something else. I added CompanyID also to the people table so you can tell which company a person should be associated with.

 

[hjgwright]

Hi.
I'm working on a database that's been in use for a couple of years.
There is already the structure config. with Employee tables etc.
The tables I added were:
Home Address
People/Home

I'm going to try what you've said.
Thanks..
HW.