[AccessD] Many2Many try #2

Maybe the first time there was TMI because I only got one answer suggesting subforms. However, the ultimate goal is to duplicate this in MySql as the server in question doesn't do Access. I'm trying to understand the simplest approach in Access so that I'll be able to do the tranlation to MySql.

In understanding my problem, I'm now thinking of these equivalents:

tblCSnames = various people
tblCScollections = various organizations

A person can belong to many organizations.
An organization can have many people.

Here are the three tables so far.

tblCScollections
	tblCScollectionID int(7) autoincrement, primary key
	tblCScollectionName varchar(50)
populated with 11 collections for testing. 

tblCSnames
	tblCSnamesID int(7) autoincrement, primary key
	tblCSnameslastname varchar(50)
	tblCSnamesfirstname varchar(50)
	tblCSnamesMemo text
populated with 5 members for testing

tblCSjoin 
tblCSjoinIDcollection foreign key to tblCScollectionID
tblCSjoinIDnames foreign key to tblCSnamesID

There will only be one John Doe, one Jane Smith, etc, IOW one of each name, and the memo will take case of any explanations needed (and the memo may be empty if no explanation is needed). Don't worry about why, just accept that as fact. That means that all John Does will be tblCSnamesID=1 so I don't need any other fields to define John Doe.

Q1) In tblCSjoin do I need tblCSjoinID int(7) autoincrement, primary key - - ?

Q2) I will have provided to me a paper list of names that belong to a collection. So I need to make some sort of update query for collectionABC and add all the people who belong to that collection. If the name of a person already exists, then the query needs to figure that out, and update that person's list of collections. A dropdown list of names is not the answer because the list of names populating such a dropdown list would become unwieldly very fast. So the question is what would the sql be for the update query?

Q3) One name complication is that it's possible to had a lastname Doe with an empty firstname, so in figuring out if the person already exists, it will have to look for (empty) Doe, John Doe, and Jane Doe. (Does empty always mean null?). Q3) isn't really a question, other than my parenthetical one.

--
Kathryn Rhinehart Bassett (Pasadena CA)
"Genealogy is my bag" "GH is my soap"
kathryn at bassett.net
http://bassett.net   


No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.476 / Virus Database: 269.10.10/908 - Release Date: 19 Jul 07 6:10 pm