Newbie Table structure help (1 Viewer)

gico1972

Registered User.
Local time
Today, 09:50
Joined
Oct 19, 2008
Messages
25
I have a customer database which consists of 2 tables.

1. Companies
2. Contacts

In the relationships there is a one to many between company and contacts (often more than one contact at each company).

I now want to create a meetings notes table to log notes on meetings held. this will consist of 4 fields.

1. Company
2. Contact
3. Date
4. Notes

What i am trying to do is select a company (Lookup) and then when selecting which contacts from the company selected was present at the meeting.

So if i select the company [ABC TOURS] i want to then select from a lookup list of only contacts that work for ABC Tours. This could be multiple contacts.

Appreciate any help with this.
 

plog

Banishment Pending
Local time
Today, 04:50
Joined
May 11, 2011
Messages
11,611
I don't think your proprosed structure is quite right. You will need 2 tables to accomodate this:

table: Meetings
fields: MeetingID, MeetingDate, MeetingNotes

table: Attendees
fields: MeetingID, ContactID


Since people can attend multiple meetings and meetings can have multiple people, you need to define a many-to-many relationship. That is done via a junction table (http://en.wikipedia.org/wiki/Junction_table), specifically in this case it would be the Attendees table.

Also, I don't believe Company is stored in either of these tables, that information can be obtained by linking to the Contacts table and finding out which company they belong to.

From a data input perspective, I would use a form based on Meetings, with an unbound drop down for all Companies. Then a continous subform based on Attendees which has a drop down for contacts. Those drop downs would show all contacts associated with the company in the Company drop down. For the specifics to accomplish this, search these forums for "cascading drop downs".
 

gico1972

Registered User.
Local time
Today, 09:50
Joined
Oct 19, 2008
Messages
25
Thanks so much for responding however i feel that with my limited knowledge of MS Access i need to do some more research as i am not quite sure on how to effect the Meetings Table as per your response.

My database essentially has 2 tables and a query that helps me combine the fields of the FIRST NAME & LAST NAME to give a complete name [NAME] Field within Contacts query.

In my new table i want to select the Company Name (look up) from Appointed Agents and the be able to select the [Name] from the Contracts Query, but only those names that are associated to the Company. I hope that this better explains as i feel that i didn't the first time around.

Thanks for your patience
 

Attachments

  • Relationship.JPG
    Relationship.JPG
    40.5 KB · Views: 111

plog

Banishment Pending
Local time
Today, 04:50
Joined
May 11, 2011
Messages
11,611
What I first see is that you've used a text field name as the primary key. You shouldn't link on text fields between tables. Instead you should use autonumbers as primary keys. Instead of Company Name in both tables you would have something like CompanyID in both tables to link them.

You should also eliminate non-alphanumeric characters in names. That means spaces, slashes and dashes ("Appointed Agents", "E-mail", "First Name", "Agent Offers / E-news"). It just makes coding and writing queries easier later on.


For what you want in terms of selecting contacts, you are not going to be able to accomplish it at a table/query level. You will need to build forms to employ the logic you want to limit the contacts you can select.
 

Users who are viewing this thread

Top Bottom