Relationship issue

[ScottV]

Hi All

I'm trying to put together a fairly simple database that in my mind has 3 tables:
1) Businesses
2) Policy Issues (with a business ID foreign key and joined to Businesses)
3) Policy Issue Updates (with a business ID foreign key and joined to Businesses; and a Policy Issue ID foreign key and joined to Policy Issues)

I've attached a screenshot of the relationships.

Each business can have many policy issues, and each policy issue can have many updates. Each policy issue will only ever relate to a single business, and each policy issue update will only ever relate to a single policy issue. So in my mind I don't have any many to many relationships.

I want to create a (single) form whereby you use a combo box to select the business you want to view. This will retrieve a policy issue for that business, then on a sub form (datasheet view) will be all the policy issue updates with simple information like date of last action, what is expected next etc.

I can't seem to create this form structure where the relevant information is retrieved with each selection of business from a combo box.

The only other relevant info for you is that I can't code I think.

Can anyone help?

 

[Ranman256]

I dont see the need for POLYISSUEUPDATES table
to have
[business ID] key.

The PI tbl already has the BID key, and PIU is owned by PI.
(it doesnt hurt to have it but seems redundant)

 

[jdraw]

In your relationships jpg,

What is a Business Update? You refer to PolicyIssueUpdate in your post.
Where does LeadContact fit?
We need a more complete description, and so do you, to determine your design.

My first cut (based on your verbiage in the post) would be:

Business--->PolicyIssue--->PolicyIssueUpdate

Good luck.

 

[ScottV]

Sorry for the confusion. Business Update and Policy Issue Update is the same thing. I named the table before I thought it through fully.

 

[jdraw]

From experience--- DON'T start with Access until you have done some database tutorials, and write a description of the business opportunity/problem (start at the 30000 foot level, then work into the details), build a draft model(pencil and paper) using your description and some sample data.

Business Update and Policy Issue Update should not be interchangeable terms.

My recommendation is that you work through this tutorial which does include a solution. You will learn if you follow the examples.
And what you learn can be used with any database.

Good luck.