I’m trying to design a fairly complex database (at least to me). Right now I’m trying to figure out the best way to handle fields that will be combined in an and/or relationship.

For example, I have a field called “Test” and a field that is related to the “Test” field in an and/or way. The related field is called “Method”. A single test can be related to a single method, or it can be related to two or more methods or it can be related to one of two different methods.

Test |Method
TestA |MethodA
TestB |MethodA AND MethodB AND MethodC
TestC |MethodA OR MethodB

What is the best way to build the tables for these two fields? I was thinking along the lines of Method being in a table as a primary key and then a foreign key that would be related to the Test field in that table. The second table would have Test as a primary key only.



tblMethods |tblTests
Method (PK) |Test (PK)
Test(FK)

That way you could add tests, but any new methods would require an associated test. So, I’m not sure?

If one test can have many methods you can't have them both as a primary key in one table. I'd PK the test field only in both, set up a relationship to say that one test table.test can have many method.tests.

As an aside what do you mean by an and/or relationship? Something in Access 2007 I haven't seen?

Essentially you need a 1:M relationship.

The parent record can have none, one or more than one children, irrespective of the children available. As long as a child is available for selection then the relationship will be built.

The And/Or senerio is somewhat confusing you seem to be looking to deep into it.

David

Perhaps I'm not looking at it correctly. What I mean by an AND/OR relationship is that the database will be used to populate reports. In the report a specific test will have an associated method with that test. At times the test will have two or more methods that need to be used with the test. At times the test will have a method OR another method (but not both) associated with it.

If I query either or both tables and want to return the test and all its methods how would I know if the method is to be related in an AND/OR way? Do I need a field that specifies that relationship? If so, when do you stop adding more fields? What if I have one test that has methodA OR methodB OR methodC OR ....etc.,

Does that help explain a little better? I'll look at both the responses and consider them more intently.

Thanks!

That way you could add tests, but any new methods would require an associated test. So, I’m not sure?

I think what you need is a table of all the possible tests, one with all the possible methods, and one with a record of the tests and methods used:

Test table:
TestID
Test Description

Method Table:
MethodID
Method Description

Test Record Table:
TestID
MethodID
Notes etc....

This way you can have methods without associated tests, and you can use methods on as many different tests as you like.

Base your report on a query (use test and method ID from the record table, and descriptions from the other two tables). Put the test ID in its own header at the top of the report and the methods in the body of the report, set the report to continuous forms and bingo!

I'm still not sure what you mean about an and/or relationship, it doesn't seem to apply when talking about reports either. DCrake has it right (of course), you would have a one-to-many relationship between test and method tables.

Hope that helps.

It seems to me that it is your personal methodology/working practicies to adopt an and or descision. It's a bit like putting a bowl of fruit infront of alot of children and saying...

Susan, you can only eat apples
John, you can either have an apple or an orange
Bill, you can have an apple and an orange
Sally, you can have a banana and a grape or a pineapple

The important thing is that the bowl contains apples, oranges, banana, grapes and pineapples.

You need some sort of flag to dictate what choices are available to each child.

David

I’m sorry but I really don’t know how to make what I’m asking any simpler. I’ll try once more.

The database, in addition to other fields, will have a field that contains all possible tests. These tests are associated with various methods. The way they are associated varies. A specific test may use one, two, three, etc., methods; this will be the AND case TestA needs Method1 AND Method2 AND Method3. The next test may use only one method; TestB needs Method1. The next test needs a particular method OR another particular method; TestC needs Method1 OR Method2.

When someone needs a report that lists all the methods associated with a specific test, how will you know how the methods are related to the tests?

e.g., SELECT ALL Methods from table WHERE Test = TestA; would give something like:

Method1 Method2 Method3


SELECT ALL Methods from table WHERE Test = TestC; would give something like:

Method1 Method2

The person needs to know how the methods are related to the Test, do they use method1 AND method2 or do they use either method1 OR method2.

I think the flag idea is what I’ll have to do; but was hoping someone would see a better way. Because how many fields do you allocate to a flag? Unknown because you have no way of predicting the number of methods involved or how they will be related to a specific test.

Thanks for the ideas!

This is what I was trying to illustrate. In the DB you'll see I've created the three tables, and have related them which is what I thought you were talking about re and/or. So in the tests & methods table you'll see just a load of numbers, but in the report and the query you'll see the text descriptions. You can have as many different tests and methods as you like - create a form based upon the tests and methods table, enter your tests and methods, then run the report and you'll see what I mean.

If I've totally missed the point, my apologies for making this thread bigger than it had to be!!

Further your explanation it seems to me that a parent record can either have one or more methods that work in conjunction with each other (the AND's) OR one or more methods that work independantly from each other (the OR's).

That being the case then I think you need two child tables branching off the parent table one to hold the AND's methods and one to hold the OR methods.

This is what I was trying to illustrate. In the DB you'll see I've created the three tables, and have related them which is what I thought you were talking about re and/or. So in the tests & methods table you'll see just a load of numbers, but in the report and the query you'll see the text descriptions. You can have as many different tests and methods as you like - create a form based upon the tests and methods table, enter your tests and methods, then run the report and you'll see what I mean.

If I've totally missed the point, my apologies for making this thread bigger than it had to be!!


I will look over what you provided. I appreciate the effort you put into this!

Further your explanation it seems to me that a parent record can either have one or more methods that work in conjunction with each other (the AND's) OR one or more methods that work independantly from each other (the OR's).

That being the case then I think you need two child tables branching off the parent table one to hold the AND's methods and one to hold the OR methods.

Ok, now that's starting to make a little more sense to me. I'll see if I can develop using that concept. Thanks for you efforts and patience!

No worries - I've asked enough of members here, it's great that everybody gives so much on this website.