Access Relationships

I am learning about relationships and linking tables. I have reconfigured a prior db to try new relationships and I am having some problems.

In this db I want Lawyers to be assoicated with a specific deal just once. At present they can be assoicated with more than one deal.

I also want to have a specific organization to be associated with a specific deal just once. At present they can be assoicated with more than one deal.

I know the answer is somewhere in the the links table but I can't seem to get it to work.
AttorneyDB3.accdb
James CoatsComputer Info. Sys. StudentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
from my iPad, so cannot view your database.

Normally for this type of setup, you would have a number of tables associated with this type of structure:

tbl_Lawyers
LawerID (auto)
LastName
FirstName
...

tblOrganizations
OrgID (auto)
OrgName
...

tblDeal
DealID (auto)
DealName
Desc (memo)

and then you would have additional tables that assign lawers and organizations to deals.  These additional table would have unique indices on the combination fields to prevent you from assigning a lawyer to the same deal more than once, or from assigning the same organization to the same deal more than once.  But would allow you to assign multiple lawyers to the same deal, or multiple organizations to the same deal.  Each of these table might list additional information that applies to the lawyer/deal or org/deal combination.

tbl_Lawyer_Deal
LawyerDealID (auto)
LawyerID
DealID
LawyerRole (might be numeric to indicate rank of lawyers assigned to the deal)

tbl_Org_Deal
OrgDealID (auto)
OrgID
DealID
OrgPOCID (might be used to indicate the person from the Org that is handling the deal)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James CoatsComputer Info. Sys. StudentAuthor Commented:
Dale,

Thank you for this. This is very helpful.

Sam
0
Dale FyeOwner, Developing Solutions LLCCommented:
Glad to help

BTW, Looking at your question history, it appears that many of the questions relate to "lessons" or "exercises".  If these questions are to assist you in performing homework, the Experts here are prohibited from providing answers to homework assignments.

Dale
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

James CoatsComputer Info. Sys. StudentAuthor Commented:
Dale,

These are related to "lessons" in general not directly to a particular question. I don't know how else to learn this other wise.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Sam, (is it really Samuel Clemmons?)

If you are doing this on your own, say with an Access for Dummies book, or some other technique, then helping you learn is not a problem.  If you are posting homework assignments, that is another issue.
0
James CoatsComputer Info. Sys. StudentAuthor Commented:
Dale,

It's really "James". I made this account long ago. Samuel Clemmons (I took out the "e") is my favorite author.  The books I read by him in school some are now banned. I'm really quite old over 60.

I am in school to keep cob webs to a manageable level in my brain. I have worked several different versions of this db without much luck. I have tried downloading similar dbs from Access but most are too complicated for me.

If I can see a working example (reverse engineering) I can then understand and apply that to other db that I create down the road. I had a tough time with drop boxes / list boxes and the SQL that you can associate with them. I can now do quite a lot without any help in this area.

Everything I have "really" learned is by looking at a "working example" of whatever I am trying to learn. Once I understand the process I come up with different ways of doing the same thing. This is how I learn.

James
0
Dale FyeOwner, Developing Solutions LLCCommented:
James,

Good for you!  You've gotta keep learning, I'm not far behind you, but work now as a full time Access/SQL Server consultant.

I would recommend the Northwind database that generally comes with Access.  It has a little bit of everything you might need for an application like this.  

Were I creating a "Deals" database, my main form would probably be bound to tbl_Deals, with a textbox or two in the form header to search for the deal by ID, Name, or Description, which would then jump to that record.

It would probably have two subforms, one for Participants (organizations) and one for Lawyers (assuming this is a list of lawyers from your company, not from the organizations involved).  These subforms would be based on queries that include the join tables mentioned above and which are linked to the Deals form on the DealID field.  This would allow you to see the basic information about each deal, the organizations associated with that deal, and the lawyers involved.

I would probably have some form of record selector in each of the subforms, which would allow me to select the organization or Lawyer and then click on a details button to display the organization or lawyer details in a popup form.
0
James CoatsComputer Info. Sys. StudentAuthor Commented:
Dale,

Thanks for the info. I do have the Northwind database but it's still a bit much for me to handle. I have not got to forms just yet. I am still learning the table relationships.

In this db I will likely not make any more tables until I get the relationship working. I'll then save it as a "working example" and then save as a new db and add forms or some other functionality to it and go from there.

This is fun for me and I might be able to put it to use as in a job where I don't have to put forth much physical effort. I feel certain I could do this type of work until I slump over dead on my keyboard. Hopefully not spilling my coffee in the process

James
0
Dale FyeOwner, Developing Solutions LLCCommented:
developing database structure is an art.  Have you read Crystal's articles on planning a database?

There are 5 normal forms, but most developers only use the first three, and sometime make a conscious decision to violate #3.  As an example, you might hard code two phone # columns to your contacts table instead of creating a separate ContactPhoneNumbers table, which would give you the ability to have as many phone numbers for a contact as you want (although I would generally do the latter, which would also require a PhoneTypes lookup table).
0
James CoatsComputer Info. Sys. StudentAuthor Commented:
Dale,

Thanks again. I am happy to have the link. I have the book Database Design for Mere Mortals (the SQL book too) and it was fairly easy to read and understand most concepts.

And yes I can see how db design would be an art. You would be advised to follow guide lines but your creative mind is more likely to come up with some very functional databases that no book could really teach you. I can tell that there are many ways to skin a database.

I have to stop now as the wife is bitching about me spending all day on the stupid computer. I have to go entertain her. Maybe later I'll have some more questions.

Thanks for all the help so far. I have really got quite a lot done on the current version of this db. I would like to have you look at it maybe tomorrow.

James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.