Associated Rules with Codes ideas

[Ebes1099]

I need some ideas for how to implement something I'm trying to do. I have one idea but I was hoping there might be a better way to do it.

I have a table full of Codes with information on each code. And then a table full of Rules. Each code can have multiple rules associated with them. And rules can be associated with multiple codes.

So I need a way, on a form, to pull up the Code and then relate which rules apply to it. The only idea I can think of right now is to have the code pull up, then have a check box for each rule, and the user can go check each box (rule) that applies to that Code. This seems like it's not very dynamic though. It won't accommodate for the addition of new rules, and if there becomes a lot of rules, there's going to be too many check boxes that its overwhelming. Also, It seems like if I did it this way, I would need a field in my Codes table for each Rule that is a Yes/No that is controlled by the Check Box.

I'm thinking there must be a better way to implement this. Any ideas? Thanks.

 

[PWise]

you need a juction table

fields

coderuleid
codeid
ruleid

 

[Ebes1099]

Then which field is the PK? The CodeRuleID i'm assuming. And I would need to relate the CodeID and RuleID from their tables as One-Many relationships?

Now, how do I allow the user to specify which Codes relate to which rules?

 

[TheAceMan1]

How are ya Ebes1099 . . .

You've just described a [blue]many to many relationship[/blue] between your tables. The junction table described by [blue]pwise[/blue] affords this type of relationship to be realized.

Have a look here: Accommodating a many-to-many relationship in Access

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Ebes1099]

I'll take a look at that...I have one other question. My wizard never pops up anymore when I place a Combo Box or Subform on my forms anymore? How do I make that come back?

 

[randy700]

On the Tools toolbar, make sure the Wizards button is
selected. It's the button that looks like a wand on
the top row.

Randy

 

[Ebes1099]

Alright, I've got the Junction table working and I'm using a form and subform with a combo box on the subform for the user to select which rules go with which codes. It seems to be working fine. Just one question. For the file in my junction table called CodeRuleID, should I have that as an AutoNumber? It would seem that by doing that it would allow for duplicates in the table. A user might accidentally select Rule 2 to go with code AB123 twice from the combo box, and with the AutoNumber field being the primary key, it would allow this.

Other options I could see are creating the primary key to be a combination of the fields CodeID and RuleID and it wouldn't allow doubles. But then what is that other field for? And how do I set up that other field? Thanks for the help everyone.

 

[Ebes1099]

One more question. It's easy for the user to add Rules to each code using the combo box. But how can the user delete a rule from a code on the form?

 

[randy700]

For that, you'll need to create a DELETE query.
You will also have to provide a method for the user to identify which rule needs to be removed.


Randy