Multiple one-to-many relationships

[DNASok]

I thought this would be easy, but I was apparently incorrect.

I am trying to create multiple one-to-many relationships, but it does not appear to be working.

What I have is 2ea tables:
"00 Funds" - This is a checkbook for various fund sources and their details
- Primary Key = FundID
"10 Efforts" - This is a list of efforts with the effort specific details
- Primary Key = EffortID
- Child Field = FundID

FundID from 00 Funds has a one-to-many relationship with FundID on 10 Efforts. This works perfect for using funds from a single fund sources for multiple efforts; i.e. one source paying for multiple efforts.

My problem is that the user stated that they often have efforts which multiple funding sources are used to pay for them; i.e. multiple sources for one effort.

So I updated "10 Efforts" to include:
- FundID1
- FundID2
- FundID3
- FundID4
- FundID5
And then created a one-to-many relationship between 00 Funds FundID to 10 Efforts FundID1, FundID2, FundID3, FundID3, and FundID5.

This does not appear to be working however, so I either screwed the pooch or this is not possible.

Any thoughts and/or suggestions are greatly appriciated...still a novice here!

 

[MarkK]

Do some searching on "database many-to-many relationship" and you'll probably find what you're looking for.

 

[DNASok]

Do some searching on "database many-to-many relationship" and you'll probably find what you're looking for.

It took a bit of writing out on scrap paper, but I finally figured out how to make the many-to-many relationship work.
I know have three (3) tables:
1. Funds
· Fund ID
2. Efforts
· Efforts ID
3. Cost Junction
· Fund ID (one-to-many from Funds)
· Effort ID (one-to-many from Efforts)
· Cost

Now when an effort is created/modified, the user selects which fund will be used to pay for it and how much (i.e. all or specific amount). Under the Funds table, each the cost of each effort is correctly associated with it and references back to the effort which the cost derived from.

Greatly appreciate this lesson as I have never done a many-to-many relationship…awesome!!!

 

[MarkK]

Well, nicely done, and thanks for posting back with your success!