[MS_AccessPros] Re: Many to Many question

 

Jim

It could actually go either way, I suppose. You need to determine if this is a management application or an HOA application. What is the purpose of the database?

I guess I'm not explaining myself properly. The Association really has only one management company at a time. The only need for a m-t-m relationship is to keep a history of the management companies it's used in the past.

The management company has (hopefully) several HOAs. So your setup would have a management table, an HOA and a table with BOTH foreign keys and whatever data that applies to the HOA's agreements.

The projects table needs a foreign key to the HOA's table.

So what I'm saying is you can still have the management form as the main and the HOA as the sub.

Bill

--- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@...> wrote:
>
> Bill
>
> I am confused a little. Are you saying that the tblAssociation should be the highest level of the hierarchy, not the tblManagementCo? Right now I have the form with the ManagementCo at the top and then a subform with the Association table and inside the Association form is a subform linking the Projects table. 
>
> If I set up like that, do I need a many to many relationship?
>
> If I do not have a foreign key in the associations table to the management co table they will not be linked. 
>
> Thank You
>  
> Jim Wagner
> ________________________________
>
>
>
> ________________________________
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Thursday, September 27, 2012 7:48 AM
> Subject: RE: [MS_AccessPros] Re: Many to Many question
>
>
>  
> Jim
>
> Feel free to ask. You know you’ll get lots of help here. I love working on re-designing. But not as much as designing from scratch.
>
> Regards,
>
> Bill
>
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
> Sent: Wednesday, September 26, 2012 1:43 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Re: Many to Many question
>
> Bill
>
> When the database was first designed, the question of the HOA's moving to another Management Co was defined as "That has never happened". But then it became common place and the information did not come from someone who knew the data very well. So I designed it without the many to many as an option. I put the ManagementCoID in the projects table because I thought possibly I might need to. I over thought it I guess.
>
> I will make some changes and let you know how it goes. I will have some additional questions afterwards
>
> Thank You
>
> Jim Wagner
> ________________________________
>
> ________________________________
> From: Bill Mosca <wrmosca@... <mailto:wrmosca%40comcast.net> >
> To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> Sent: Wednesday, September 26, 2012 8:25 AM
> Subject: [MS_AccessPros] Re: Many to Many question
>
> Jim
>
> You need to rethink your design in this area. The Association table should not have a Management foreign key. Think of the fields in a table as elements of an entity. The Management company works for the HOA just like an employee works for a company. The employee is not a property of it. It does not make up a piece of it in any way. The management company could fold and the HOA would still exist.
>
> And as I suspected, you need a linking table with the Assoc ID and Mgmt ID. That table's Primary key should be an AutoNumber.
>
> You can fill that table by using the two existing fields in the Assoc table.
>
> INSERT INTO tblAssocMgmt(AssociationID, ManagementCoID)
> SELECT AssociationID, ManagementCoID FROM tblAssociation
>
> And There is no need to have a ManagementCoID in the projects table. You can always get that by joining the Association table to the projects table and then join the linking table to the project table.
>
> Then join the management table to the linking table.
>
> Capeesh?
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> , Jim Wagner <luvmymelody@> wrote:
> >
> > thanks Bill
> > Â
> > Jim Wagner
> > ________________________________
> >
> >
> >
> > ________________________________
> > From: Bill Mosca <wrmosca@>
> > To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Sent: Tuesday, September 25, 2012 2:52 PM
> > Subject: [MS_AccessPros] Re: Many to Many question
> >
> >
> > Â
> > Got it, Jim. But my work day is over and my commute is about to begin so I'll loo at it tomorrow.
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> , Jim Wagner <luvmymelody@> wrote:
> > >
> > > Bill
> > >
> > > I uploaded it to assistance needed and named it many to many help
> > > ÂÂ
> > > Jim Wagner
> > > ________________________________
> > >
> > >
> > >
> > > ________________________________
> > > From: Bill Mosca <wrmosca@>
> > > To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Sent: Tuesday, September 25, 2012 1:35 PM
> > > Subject: [MS_AccessPros] Re: Many to Many question
> > >
> > >
> > > ÂÂ
> > > Jim
> > >
> > > Sure. Use my Comcast address. It might be a couple days until I can get to it.
> > >
> > > Did you zip it? That might reduce the size enough to squeak it into our Files here.
> > >
> > > Bill
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> , Jim Wagner <luvmymelody@> wrote:
> > > >
> > > > Bill
> > > >
> > > > Even with no forms and reports and empty tables the database is too large to upload. I compacted and repaired and still to big. Can I send it to you?
> > > > ÃÆ'‚ÂÂ
> > > > Jim Wagner
> > > > ________________________________
> > > >
> > > >
> > > >
> > > > ________________________________
> > > > From: Bill Mosca <wrmosca@>
> > > > To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > Sent: Tuesday, September 25, 2012 12:54 PM
> > > > Subject: [MS_AccessPros] Re: Many to Many question
> > > >
> > > >
> > > > ÃÆ'‚ÂÂ
> > > > Jim
> > > >
> > > > How are you storing the HOA-Management connection now? If it is in a single table you can run an append query to load the linking table with those two fields.
> > > >
> > > > Without knowing your table structures I can't be more specific than that. If you'd like to upload a sample with just the empty tables I could take a look.
> > > >
> > > > Bill
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> , Jim Wagner <luvmymelody@> wrote:
> > > > >
> > > > > BillÃÆ'Æ'‚ÃÆ'‚ÂÂ
> > > > >
> > > > > I will give that a try. What about the other part of my question
> > > > >
> > > > > ÃÆ'Æ'‚ÃÆ'‚ÂÂ I can not find out how to populate the junction table without manually adding the records.ÃÆ'Æ'‚ÃÆ'‚ÂÂ ÃÆ'Æ'‚ÃÆ'‚ÂÂ
> > > > >
> > > > > ÃÆ'Æ'‚ÃÆ'‚ÂÂ
> > > > > Jim Wagner
> > > > > ________________________________
> > > > >
> > > > >
> > > > >
> > > > > ________________________________
> > > > > From: Bill Mosca <wrmosca@>
> > > > > To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > Sent: Tuesday, September 25, 2012 11:23 AM
> > > > > Subject: [MS_AccessPros] Re: Many to Many question
> > > > >
> > > > >
> > > > > ÃÆ'Æ'‚ÃÆ'‚ÂÂ
> > > > > The Associations table should have the demographics for each HOA. A second table would have demographics for management companies.
> > > > >
> > > > > The AssociationManagement table would have a foreign key to each table. This table would have the duplicate HOA keys if it has had more than one management company in its history. This is your linking table for the many-to-many relationship.
> > > > >
> > > > > Bill
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> , Jim Wagner <luvmymelody@> wrote:
> > > > > >
> > > > > > The Database is for a CPA firm. The projects are those projects dealing with the Associations only. They track the things they do for them in regards to billing the Associations.ÃÆ'Æ'Æ'ÃÆ'¢â‚¬Å¡ÃÆ'Æ'‚ÃÆ'‚ÂÂ
> > > > > > There is a field currently that indicates whether the association is a current client.ÃÆ'Æ'Æ'ÃÆ'¢â‚¬Å¡ÃÆ'Æ'‚ÃÆ'‚ÂÂ
> > > > > >
> > > > > > It seems that if I create this field, I would still have duplicate Associations in the tblAssociations table.ÃÆ'Æ'Æ'ÃÆ'¢â‚¬Å¡ÃÆ'Æ'‚ÃÆ'‚ÂÂ
> > > > > > ÃÆ'Æ'Æ'ÃÆ'¢â‚¬Å¡ÃÆ'Æ'‚ÃÆ'‚ÂÂ
> > > > > > Jim Wagner
> > > > > > ________________________________
> > > > > >
> > > > > >
> > > > > >
> > > > > > ________________________________
> > > > > > From: Bill Mosca <wrmosca@>
> > > > > > To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > Sent: Tuesday, September 25, 2012 10:54 AM
> > > > > > Subject: [MS_AccessPros] Re: Many to Many question
> > > > > >
> > > > > >
> > > > > > ÃÆ'Æ'Æ'ÃÆ'¢â‚¬Å¡ÃÆ'Æ'‚ÃÆ'‚ÂÂ
> > > > > > Jim
> > > > > >
> > > > > > I'd set up an AssociationManagement table with a field Named Current (Yes/No).
> > > > > >
> > > > > > That way if an HOA changes management companies you can keep the old record but unmark it as Current.
> > > > > >
> > > > > > Are projects related to Management in any way? The HOAs I've dealt with use the Management to deal with the contracts, billing, etc. That would be handed over to the new Management so they are not really tied to the projects. Just to the HOA.
> > > > > >
> > > > > > Regards,
> > > > > > Bill Mosca, Founder - MS_Access_Professionals
> > > > > > http://www.thatlldoit.com
> > > > > > Microsoft Office Access MVP
> > > > > > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> > > > > > My nothing-to-do-with-Access blog
> > > > > > http://wrmosca.wordpress.com
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> , "luvmymelody" <luvmymelody@> wrote:
> > > > > > >
> > > > > > > Hello all,
> > > > > > >
> > > > > > > I am recreating a database that should have had a many to many relationship set up. There are 2 tables one with Management Companies and the other is the Home Owners Associations which are managed by the Management companies. A third table is called Projects where this table is linked to the Home Owners Association table, as there is 1 to many projects.
> > > > > > > The issue is that Home Owners Associations are now moving to and between Management companies. I have studied on how to define and set up the many to many relationships but I can not find out how to populate the junction table without manually adding the records.
> > > > > > >
> > > > > > > And how do the history of projects for associations not go away when the associations move to a new Management companies?
> > > > > > >
> > > > > > > So my question is how do I set up this many to many relationships?
> > > > > > >
> > > > > > >
> > > > > > > Thank You
> > > > > > >
> > > > > > > Jim Wagner
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > [Non-text portions of this message have been removed]
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > [Non-text portions of this message have been removed]
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (18)

Recent Activity:

• New Members 4
• New Files 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___