Managing Contacts in a multi-tier database

[MSAccessNewGirl]

I have created a database which we are going to use to track various projects at work. The main table is Company (ID_Company is the primary key), which relates to Company_Address (ID_COMPANY and ID_COMPANY_ADDRESS are the primary keys), which relates to Company_Contacts (ID_CONTACT and ID_COMPANY_ADDRESS which are the primary keys), and Project (ID_PROJECT and ID_COMPANY are the primary keys). The idea of the database is to first add a Company name. Next, you can fill in the address of the company (expecting that there could be several different addresses for a given company ... ie. offices could be in New York and in Denver). Each address has the ability to have both Contacts and Projects assigned to it. I have set up relationships on each of the tables already.

My issue is being able to add new contacts and/or projects for a given address. I am completely unfamiliar with using VBA and would prefer to use Macros so that I can recreate what has already been done. I am attaching a copy of the database with the hope that someone can assist me! Thanks so very much for any help!

[June7]

I opened the Company_Projects Subform (you have a misspelling in the name as Subforum) and it looks exactly like the Contacts Subform.

What exactly is the 'issue' you have with adding new contacts and/or projects for a given address? Do projects really need to be associated with specific address? Then have field for Address ID instead of Company ID in Project table.

I don't think you should have compound primary keys.

[azhar2006]

See thes
Is this what you want

[MSAccessNewGirl]

Sorry, it isn't. I need to be able to add Contacts & Projects to a given Company Address.

[MSAccessNewGirl]

Yes, you are correct ... the Projects subform looks just like the Contacts subform. What I am trying to accomplish on each of these is the same. I have attached a modified dbs.

I have to have contacts associated with a company at a specific address because we may deal with several offices of a given company; thus, each office has different personnel working there and each office will have different projects. Because of this, I believe that I need compound primary keys. Am I incorrect?

I have tried to use SetTempVar on the Contacts Button (On Click, using a Macro); however, it is not working.

Thanks so much for your help!

[June7]

No db attached to your last post.

Still don't see need for compound key.

The primary key for Project table is ID_Project. ID_Company in that table is a foreign key.

Similar case for Address and Contacts.

[MSAccessNewGirl]

Sorry. I loaded the second Contacts database under my original message ... it's the larger file.

Can you then show me how I should set up the database so that I can do the following:
- Have one entry for each Company
- Have multiple addresses for each Company
- Have multiple contacts for each company address
- Have multiple projects for each company address

[June7]

Again, I still don't think compound keys are required but will probably work anyway.

Table structures seem good. Don't think should be INNER joins on the relationship links.

I am sure you are aware the embedded macros for the buttons are not working. I don't use macros, only VBA.

You could set the CustomerAddress subform as Single view then have the Projects and Contacts forms as subsubforms.

Here is a method to have the Contacts and Projects subforms sit next to the Addresses subform: http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

Can each project can have only one company address?