Many to Many Relations - Need help on using it.

[buzcut]

I have 3 tables that I am using: tblCaseData, tblContacts and tblCaseContacts (the junction table). I need to enter data in the tblCaseData then add contacts and clients in another screen. Once done, for example, I should be able to see what cases clients have with me, and which cases does a specific client have with me (the reverse view). I can't seem to get my junction table to work right. It's not saving my entries. Individually, tblCaseData and tblContacts work great. Trying to link the two- forget it! Help! Thanks folks!

buzcut

 

[Eradic8or]

The thing to remember about linking tables is that there must be a common key between them.
In your case, I would be inclined to use the case number as the common key and link your remaining tables using this key.
It will look like you have a lot of duplicate information in the other 2 tables but when it comes to querying them, you can use the case number along with the relationships to filter out the records on a report.
After all, the table only hold the data and can be made transparent to the operation of the database.

 

[buzcut]

Eradic8er- I understand what you're saying, but that would mean that I would end up with quite a bit of duplicated data. There are many records for both the tblCaseData and tblContacts. There must be a way to show that each table has many records in the other table.

buzcut

 

[Eradic8or]

You must have common link between the data and in this scenario, the case number is that common link.
You must have a reference to this in each of the indexes otherwise Access doesn't know that these fields are related.
The duplicate data will not show up in queries.

E.G. I wrote an I.T. helpdesk which allows the other software engineers to record what they have done against a call number.
In a related table the call number is the common link and there may be 10 instances of the same call number and 10 different actions against that number. This is the only way you can keep track. However, when I run the report, it only displays the call number once and then lists the 10 actions against that number.

Eradic8or

 

[RickSpr]

Your many-to-many junction table seems like the right approach to me. I've used the same approach myself many times. These tables typically consist of nothing more than a key from each of the parent tables.

You described a case entry form and a client entry form, which is fine for populating the parent tables, but are you trying to populate the junction from one of these forms as well? What are you using as recordsources for these forms? What is your workflow--do you add the case first, then the client, or the other way around? If this is truly a many-to-many, I'm not sure this approach will work anyway, since you can't add several cases and then several clients and expect to keep everything straight about who goes with which case.

Maybe you should try another approach: Add a case on the Case Entry form, then go to a Case/Client form where you can pick clients from a list. This form's recordsource is your junction table; the client list's rowsource is the Client table. If the client is not in the list, you have an Add Client button that opens a third form, Client Entry, as a modal dialog based on the Clients table. After you add the client and close that form, the second form (Case/Client) refreshes the client list, so you can then pick the client.