[AccessD] Let me try this another way - please help me understand this?

A Many-to-Many is easily resolved, Kath.

Given tables A and B with PKs APK and BPK then

Create table A2B having (now here comes the controversy (enter JWC LOL))
either an autoNumber or alternatively a compound PK consisting of APK+BPK
(both of which columns must be present and not null). In either setup,
you're good to go. In the case of MySQL I would go with the compound PK on
the associative table, rather than the autonum, but that's strictly because
of the behaviour of MySQL rather than a recommendation that can be
generalized to all implementations of SQL.

A.

On 7/22/07, Kathryn Bassett <kathryn at bassett.net> wrote:
>
> Steve pointed out the error in my thinking - tho collections and names are
> many to many, collections to join are many to one - duh! And that link was a
> good one.
>
> Now the question remains of which things I check, but maybe that needs
> more info. Once I get this working in Access, I have to get it all fixed up
> in MySql as the server doesn't "do" Access.
>
> Kathryn
>
>
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Steve Schapel
> > Sent: 22 Jul 2007 4:07 pm
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Let me try this another way - please
> > help me understand this?
> >
> > Kathryn,
> >
> > You may be interested to read this short article...
> > http://accesstips.datamanagementsolutions.biz/many.htm
> >
> > If I correctly understand your example, it seems to me that
> > there should be a one-to-many join between:
> > tblCScollections.tblCScollectionID and
> > tblCSjoin.tblCSjoinIDcollection ... and also a one-to-many
> > relationship between:
> > tblCSnames.tblCSnamesID and tblCSjoin.tblCSjoinIDnames
> >
> > Regards
> > Steve
> >
> >
> > Kathryn Bassett wrote:
> > > Maybe a screen capture will get someone to help me.
> > >
> > http://www.genealogylibrarycenter.com/images/manymanyrelationship.jpg
> > >
> > > First, the word foreign in the tblCSjoin bottom field name
> > has been corrected, so ignore that. The word was intended to
> > be in the description. Now, here's my problem. I've only ever
> > done Many-One or One-Many, I've never done a Many-Many and
> > don't know how to join the tables. I've tried some different
> > combos and nothing will turn that One-To-Many into
> > Many-To-Many. And I'm not clear on Enforcing and the Cascading either.
> > >
> > > A name can belong to many collections.
> > > A collection can have many names.
> > >
> > > There is never going to be two people of the same name.
> > Don't be concerned with why not, just be assured I've got my
> > reasons, and a solution.
> > >
> > > So, how do I drag the tblCSjoin's foreign key's to the
> > primary keys in the other tables, and what all do I checkmark?
> > >
> > > I'll get to other question after this part is solved.
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.5.476 / Virus Database: 269.10.12/910 - Release
> > Date: 21 Jul 07 3:52 pm
> >
> >
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.5.476 / Virus Database: 269.10.12/910 - Release Date: 21 Jul 07
> 3:52 pm
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>