Relation Question

[formcreator]

Hi I need to design 2 tables as shown below

Resource Category and Resources

Each Resource category can have multiple resources.
Each resource in turn can be dependent on other resource categories. I know
I will have to set up a many to many relations. But I cam kind of confused on
what fields should I use to link.

The data will eventually have to look like this

category 1
res 1
dependent on category 3
dependent on category 5
dependent on category 6
res 2

res 3
category 2
res 1
res
category 3
-----
------
category 4


and so on

Thanks
Shri

 

[Evi]

Wow, what a brain twister. I can say that you will DEFINITELY need more than
2 tables.
Your 'lookup' tables will
ResID (primary key field)
Resource Name
Resource Number


CatID (primary Key field)
CategoryName
Other stuff about category.

You may want to have a record in each of these tables which has nothing in
the Name fields in case you are going to leave a joined field blank in your

Input Table

The usual structure is make the input table contain a primary key field and
then both ResID and CatID .

You will link to these via your Category and Resources tables

Now comes the bit I'm not so sure about (because the whole idea is so
mind-blowing)

What about a tick box which you will tick if the Category is dependant on
the resource in a record but leave blank if the Resource is dependant on the
category? Thats certainly the simplest idea if it is sufficient - I suspect
it isn't!

Or could you add ResID and CatID to your input table but then add DepResID
(dependent Resource) and DepCatID (Dependent Category.
In the relationships window and in query grids you add your Category and
Resource table twice so that you can link them to these fields.

Evi

 

[formcreator]

Thanks Evi,
I know it is a mind twister. But I still have to find out if there is
anything requirement for the relationship or is it just informational. But
here is my question
How do i show 1 resource has multiple dependencies. (I will at a later point
see if I can connect the multiple dependencies to the categories if needed)

Here is what I have
resource category table
cat_ID
cat_Name
res_ID(FK to connect to resource table)

resource Table
Res_ID
res_Name
res_Type


Dependency table
dep_ID
dependency
res_ID
category

I want to connect the resource table and the dependency table where I can
say resource 1 (res_ID) has dependency on category 1(dependency is a Y/N
field, if resource 1 is dependent on cat1 then dependency is Y if not it is
no)

How do I set up the relationships to do this

Thanks
Shri