Re: designing for several many-many relationships

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which illustrates a couple
of ways to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"Jay Wolfe" <jayw710@xxxxxxxxxxxx> wrote in message
news:Lr6dnVtEBYKFKljbnZ2dnUVZ_uiknZ2d@xxxxxxxxxxxxxxx
Hi database experts,

I'm designing a db to store client personal and business information, and
I'm trying to figure out how to best design the forms for input. I've got
several many-to-many relationships that I broke down into one-to-many
tables by use of an associated link table. I can post a jpeg of the
relationships, but I don't think this group allows that.

Everything is 3rd order normal, so I'm fine there. It's planning for
efficiency and ease of queries and forms that I'm not sure about.
Essentially my relationships look like a daisy chain as follows:

tblClients (1->oo) tblClient_Parents_LINK (1->oo) tblParents (1->oo)
tblParent_Child_LINK (1->oo) tblChildren (1->oo) tblCollege_Child_LINK
(1->oo) tblColleges

where (1->oo) means One-to-many, if it wasn't clear. Each of the LINK
tables have 2 fields, an ID field to match each of the tables. (e.g.
tblClient_Parents_LINK has ClientID and ParentID fields)

A client consists of a set of parents and children. Parents can have many
children, and Children can have multiple parents, including biological,
step, and adoptive. One question is whether Children should be directly
related to Parents (as they are now) or directly related to the Client.

If my main form is a Client entry form, I will need to enter the parents
and children that represent the client. Can this be done with a standard
query, or will VBA code be needed? I've looked in a number of books, but
never found this situation precisely. Is there a book that addresses
precisely this situation?

I know I can have the Client form based on the Client table, and then a
subform based on the LINK table, but how do I enter parents and children?
What should I enter first? How can parents and children be automatically
associated with each other? Can this be done via a well designed query so
that 2 tables are update at the same time?

How do I do this as efficiently (& as painlessly) as possible?

Thanks!

Jay



.

Relevant Pages

  • designing for several many-many relationships
    ... I'm designing a db to store client personal and business information, ... A client consists of a set of parents and children. ... I've looked in a number of books, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Pricing
    ... If a client was to purchase a Ms Access Database FE (User Face, Forms, ... Would that BE/SQL be on there SQL Server (they consistanly maintain - ... Would it be possible to deploy a MS ACCESS FE/BE - SQL Server via zip email? ...
    (comp.databases.ms-access)
  • Re: some advice please
    ... where it's possible to access database from VB. ... "Jeff Cochran" wrote in message ... > elements as others are selected (check a client side or DHTML group ... >>form needed because no variable to pass to ASP. ...
    (microsoft.public.inetserver.asp.db)
  • Re: some advice please
    ... where it's possible to access database from VB. ... "Jeff Cochran" wrote in message ... > elements as others are selected (check a client side or DHTML group ... >>form needed because no variable to pass to ASP. ...
    (microsoft.public.inetserver.asp.general)
  • OT: E-mail habits (Re: [Ruby-Talk] can we add a tag like this to all the mail at the ruby ta
    ... Do you give your home phone number to a client? ... business phone number to your parents? ...
    (comp.lang.ruby)