Many-to-Many Relationship in Same Table?

[trypsin25]

Hi! I am working on a volunteer project and am admittedly a super-newbie to Access. Would someone point me to a tutorial (thread, book, anything) to help with the scenario below?

The non-profit I am working with needs to track recipients, their family and household members (former and current). My first thought is to create one table with "people" and add a checkbox for "recipient" then somehow create a junction table that connects these people with a role value (Father, Mother, Child, Other) and a household member value (current, former, no). Am I on the right track? Is there a resource that can show a simple example or how-to?

 

[Micron]

Really not much info to go on. No idea what makes one a recipient or what makes one a former or current "household" member - different/same address? Anyone who is a parent is also potentially a child in your brief scenario so then what? Maybe what you need is a genealogy database (family tree db)? Hard to tell. Could also just be that you need a table for recipients and then one to list the associated family members. The PK of members would be a foreign key in tblAssociates or whatever you'd call it. That way you wouldn't have a lot of empty fields, assuming most members are not recipients, nor would you have a lot of repeated data. If there are 5 members in a household and address is a field, you have the same info in 5 times. Not sure how you'd distinguish household members if using 2 tables though. Then again, I'm just rambling here because of limited info as to what process the db is supposed to support.

 

[xenou]

In general you probably just want to sketch out some structures and see how it works - try some sample data with 5 or 6 members and associated relationships. I do recommend drawing things like this on paper before just creating tables.

 

[jackd]

I agree with micron and xenou - more info is required. Suggest you provide "a day in my business" in plain English. A few examples to show a recipient entering your facility(ies) and the info you need for the things/processes/activities you do.

I'm attaching a link to this RogersAccessLibrary tutorial in which he leads you through the process of designing a database (tables and relationships) from a plain English description of the business requirement. You will learn a process for creating and vetting tables and relationships if you work through the tutorial.
There are a number (probably too many at this point) of articles at Database Planning and Design etc.
Good luck.