Complex Relationship Issue

[Gethzerion]

Hi All,

If possible I would like some help on the design of a database. Having gone through the process of determining what the DB must be capable of, I've come across a complex issue, that I cannot determine how to resolve.

For each Group of Tests requested (1 or many) there is a series of codes that has to be recorded, each has a description and a cost which is date dependant.

So far so good. But, there a three classes of test code, Test1, Test2, Test3 - each code in the subgroups are unique.

In each group there must be at least one code for each class, however there could be multiple Test1 codes, to one Test2 code, to multiple Test3 codes. Conversley there could be 1 Test1 code, multiple test2 codes, and multiple Test3 codes. There is no distinct relationship either between the various codes...

I need to retain the overall grouping.

In case this was as clear as mud, I could have

TestRequest001

TestA..........Test1..........TestF5
..................Test3
..................Test15

TestRequest002

TestA..........Test2..........TestF1
TestB............................TestF5
TestC
TestD

Any help on how to set up the tables, and to produce a "neat" looking form would be greatfully received. Oh and I need to record the results from each of the last class of tests....

Thanks in advance

 

[neileg]

You need a table that has one record per test which has a field that identifies the group and another that identifies the class. Create three queries that retrieve the tests by class and base three subforms on these queries.

 

[Gethzerion]

TYfor the help. If I get stuck, lost, etc in this, may I PM you?

 

[neileg]

It's better to post in the forum so that everybody can learn/contribute. I'll be watching!