Help with standard many-to-many form for beginner, please

[Accesstrep]

Would be grateful for some help with this problem which I can then apply to many similar cases.

There are two main tables, tblCommunities and tbl HealthProblems, with a join-table, tblCommunityHealthProblems.

Attached is a zip file which shows the form I have made to try to add multiple Health Problems to particular Communities by using a combo box. It doesn't work.

Should the form be based on a query rather than the tables?

 

[jal]

Some of us don't have Access 2007 installed, so we can't read your data.

You might want to see if the Tools menu has a Backup Database option that allows you to save it to an MDB file (Access 2003 format). Not sure if that option exists.

 

[georgedwilkinson]

Take a look at Pat Hartman's many to many sample database. Her example may help to clear some of the fog.

Many-to-many example

 

[Accesstrep]

Fair point - thanks.

.mdb version attached. Look forward to your comments!

 

[Accesstrep]

Thanks, George for your recommendation to look at Pat Harman's excellent example. I had worked through it very carefully before posting this. Her's does not use a combo box unfortunately, nor could I clearly see at the time whether the joins were outer or inner.

 

[jal]

Fair point - thanks.

.mdb version attached. Look forward to your comments!

Maybe the problem here is that I don't know anything about many-to-many relationships. Not sure I can help.

On the other hand I do know a little about "ordinary" joins (i.e. one-to-many relationships). With an ordinary join, you are dealing with two tables that have a column in common (or in some cases more than one column in common).

For example a customer named John Smith (in the Customers table) might have 10 orders (in the orders table). So we could display the 10 like this:

Select * from Customers as C
INNER JOIN Orders as O
ON O.CustomerID = C.CustomerID
WHERE CustName = "John Smith"

The join works only because the two tables each have a column in common (CustomerID). We join on that column held in common.


I looked at your uploaded data. I'm left scratching my head as to what column is held in common between the two tables you are trying to join. Care to clarify?

 

[Accesstrep]

OK Based on Pat Hartman's many-to-many example (my apologies, Pat,I'm not a good pupil!)

I have three tables -
Communities with CommID, Community;
HealthProblems with HealthProbID, HealthProb;
and a link table with CommFKID and HealthProbFKID.

I have a query, qryCommunitytoHealthProb which has the fields
CommFKID,HealthProbID and HealthProb.

I make a subform with a combo box based on the query and place it in a form (frmCommunityHealthProblems) with CommID and Community as the one part of the one to many relationship.

The result does not work and I would be very grateful if someone could tell me why and what I need to do to make it work.

 

[jal]

I looked at your last upload. Your form looks good, but the data-problem that I mentioned still exists.

You say you have a many-to-many relationship. Friend, looking at your tables, I am once again forced to conclude that you haven't even established a one-to-many relationship as yet.

You have two query obects in your DB. The point of such join-queries, is to display all the records involved in a one-to-many or many-to-many relationship. As you don't have such a relationship as yet in your tables, naturally the queries are returning zero records. Expect nothing more.

Go back to my example. A one-to-many relationship means that you have a table with a customer named John Smith. (That's the one). This one is related to a "many" - he has 10 orders in the OrdersTable. This is a one-to-many.

Some applications don't even need a one-to-many to begin with, so maybe you're seeking after the wrong thing. Tell us what it is you are trying to accomplish - why does the user need your form? What is he going to do with it? What is he going to view or learn from your display?

 

[jal]

I dunno, maybe the problem here is my lack of experience with link tables. Earlier you called it a "join table" and now you seem to be calling it a link table. In the upload, that table is empty (tblCommunityHealthProblems). I am surmising then, that this table is populated on your side even though it is empty when we look at it?

If so, I apologize - I thought you were trying to do a join on an empty table, in which case zero records, naturally, would return. Sorry about my misunderstanding.

 

[jal]

Well, then again, I went into the Linked Table Manager - and it says you have no linked tables, so I don't still don't know what you mean.

 

[Accesstrep]

I finally managed to crack this first stage. Just to say thanks to everyone for their patience, and particular thanks to Pat Hartman for her example. Following it exactly was the secret!