Jump to content

Many-to-many quandary


This topic is 6078 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have a database with referral records to different medical clinics in our area. I've hastily simplified and modified the file if you feel like looking at it. This is an already made database that I'm trying to restructure to work better. There is a table for the referrals called "ReferralData"

Also, there is a table I've just created for types of services provided called "Terms", examples: "general physical", "cholesterol blood test". This is related to the "ReferralData" table in a Many-to-many relationship.

I also have a table for groups of these service terms called "TermGroups". This is related to the "Terms" table in a many-to-many relationship so that i can have overlapping term groupings.

I put in some portals to better pick out relating terms/groups/referrals on related tables. My portals between "Terms" and "TermGroups" work great, I made value lists so as to be able to pick related items better.

I tried to do the same thing with "Terms" and "ReferralData".

As far as I can tell, this table relationship is exactly the same as "Terms" to "TermGroups". All the settings for the relationship and the portals are the same for both relationships. But when I try to select a line of the portal in either "Terms" or "ReferralData", I can't. Just to figure out what was wrong, I made a record in the join table "JoinTermReferral" with two preferred related ID numbers. the relationship showed up in both portals. But in the portal on "ReferralData" I tried to change the ID number list to change which "term" the referral was related to, but instead it modified the ID number of the "Terms" record that I was trying to change from.

How do I make my "ReferralData" to "Terms" relationship work just as well as "Terms" to "TermGroups"?

I hope my descriptions aren't too horribly confusing. The file is attached, all relevant layouts are at the top of the layout list. All relevant fields are at the top of their respective layouts. Relevant value lists are at the bottom.


Link to comment
Share on other sites

When you have an enterable, modifiable ID field in a "join" portal, it should be a foreign key OF the join table, but never the primary key of the original table. Otherwise you're changing the primary ID of the original table. Yikes! You did the same thing in both your portals.

Link to comment
Share on other sites


I am about to attempt something similar as to what you are discussing (my first run at it).

If my


is a "Number" "Auto-Enter Serial, Cant Modify Required Value, Unique...can I use it as a the FK in my join, or do I need to create something else?



Link to comment
Share on other sites

This topic is 6078 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.