Add Instructions Table - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Add Instructions Table

John Muir

2 years ago

I have large clients (many people). In each individual client record I have a notes section where I can display their preferences in the [preferences] field, as to how they want formatting to be, etc.,

However, the client corporation has special operating procedures that apply to all its people. The only way I can handle this now is to put the same procedures text in each individual's record, which is crazy/impossible. I should have created a table for Client Procedures, a long time ago but need to add it now.

My contacts table is called "tContacts". I'm thinking I need to create another table called SpecialInstructionsT and join to the tContacts table in a query, but not sure how to do that. Add a number field into my tClients table called, SpecialInstructionsID and then join the together. I get confused on when to use one to many, many to many etc.I've watched the Relationship video with the cars examples, but find that hard to apply to what i'm doing.

would appreciate help in setting this up.

Thanks,

John

	Richard Rost @Reply 2 years ago
What do your tables look like now?

John Muir OP @Reply

2 years ago

I'm not sure exactley how to answer. My "tContacts" table contains all of my client information, name, address, phones, preferences, etc., - I didn't use seperate tables for different addresses and many phones, but should have.

I have several tContacts tables: tContacts_1 throu9gh tContacts_6, because my contacts may take on different roles. For example, contacts in tContacts_1 may have referred business to me, but may also be my client in another transaction.

Another main table is tNewCases. This table contains all of the different ID fields like ClientID, AttorneyID and so on. I have this table in a query with my 6 contacts tables. The tContacts tables are joined to tNewCases via referential integ #2 button.

If i didn't answer your question, let me know if i need to send a screen shot of something.

Thanks,

John

	Kevin Robertson @Reply 2 years ago
6 contact tables? Too Many Tables

	Richard Rost @Reply 2 years ago
Yeah, I would put those all together in one contact table and have a specific field that represents their role.

	John Muir OP @Reply 2 years ago

	John Muir OP @Reply 2 years ago

	John Muir OP @Reply 2 years ago

John Muir OP @Reply

2 years ago

Thanks. The trouble is, each client may have up to 3 or 4 different roles [Type] in a single transaction. Overall this has worked very well over the last 15 years. I think an access master helped me set this up...

Now i'm thinking i need to create a table of just companies and in that table have special instructions so AIG has its own special instructions and Hartford has its own special instructions. This table would be tied to tContacts. And i may have 100 different people with AIG.

Does any of this help or make sense?

	Richard Rost @Reply 2 years ago
Again, I would put all of the contacts together in one table. Make it a one-to-many relationship or even a many-to-many if the contacts can be linked to multiple companies. And again, if you need to distinguish between AIG and Hartford, that should also be in a separate related table. Don't shoe-horn yourself in to a specific set number of items (i.e. 6 contacts).

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View