My database has multiple many-to-many relationships, demonstrated in attached flow chart. I want to be able to search for information by choosing a hazard, then a response activity, then a resource category. Within each resource category are resource descriptions and this is where my difficulty lies. I have created a junction table and parameter query that returns the resource category, but I want to reach a list of resource descriptions in that category, select a description, and show only those records. I could do this by adding each matching record to the primary key in the junction table, but then every time a new record is added, you would have to go into the junction table and place it in all the correct categories. Is there another way to return all the records that match the ResourceDescription field when it has a many-to-many relationship with ResourceCategory?

Hi
I'm afraid your flow chart makes no sense to me. Sorry. Can you post your database structure?
Jim

I cannot post the database as it is on a separate laptop, but maybe I can clarify. This is a resource management database. There is a resource table, sorted by the ResourceDescription field. I want to access a particular resource description a number of ways. There are Hazards, (blizzard, etc.), and Response Activities associated with each hazard. In turn there are Resource Categories associated with each Response Activity. Then I have several Resource Descriptions for each category. The resource descriptions can fit multiple categories, the categories can fit multiple descriptions, and so on. (These overlap- Communications, for example, is associated w/every hazard.) I created a junction table that uses a composite primary key, Hazard, Activity, and Category.

For example: Blizzard, Communications, Radios.

Blizzard, Communications, Data Terminals.

The form I created from a paramater query will display the above results.

My problem is that the resource description applies to several records. I can reach a unique record by adding all specific record numbers to the right categories in the primary key, but then each new record would require you to re-enter all possible categories, activities, and hazards that the description falls into. I want to reference the matching ResourceDescription field, not a particular record, so I will see all the matching records.

I wasn't looking to get the Db, just the structure of the tables so that I can visulize your relationships. (sorry that sounded abit poncy! )
A quick point, scattered throughout this forum is a plethora of discussions on why not to use composite Primary Keys, by people far more experienced and knowledgeable (but not as good looking ) as me. I have found from experience that they Composite PK) can be a real pain to work with.
On re-reading your original post it sounds like Cascading combo's and subforms might be useful to you.
If this isn't helpful tho' post back - it would be helpful to me to be able to see the structure of your tables tho'
HTH
Jim

I will do my best here to do what you are asking- I am still a little unsure exactly what you mean by structure, but here goes. If there is another way to do this I'm all ears!

The resource table uses an autonumber, and contains the ResourceDescription field, Rate field, etc. I have three tables that contain the hazards, response activities, and resource categories, they all contain only two fields, an autonumber and the name of the hazard, etc. The junction table actually uses the autonumbers from these tables (FK's) as the composite PK. ( The query form returns the written word. ) Part of my problem may be in the table structure- I can't figure out how to link the same resource record to than one category. So right now my junction table and resource table have no relationships between them, obviously a problem. I would like the ResourceCategory field to be the link. The hazards, response activities, and resource categories all have only unique entries, but there will be several records for each ResourceDescription.

I like the idea of cascading combo boxes- where would I look for more info about them? And I have a little experience with subforms, and was trying that approach, but it got too confusing. I got the idea for the junction table here at UA, I would never have thought of it on my own, and I'm certainly not attached to it, but I don't know another way to establish all the many-to-many relationships. I keep getting close to what I want but there's always that missing crucial link. Thanks for the help, and I hope this is what you meant.

I just re-read this post, and wanted to correct the statement - The hazards, RA's, and RC's all have only unique entries... This is actually not true b/c of the many-to-many, I'm not sure now what I was trying to say! Too late to edit previous post. :( Please disregard that statement. Also I'm reading up on the cascading combo boxes.

Edited by: rosesarefree on Wed Dec 29 13:22:10 EST 2004.

Hi Rosesarefree (but only if you nick 'em )

Look at this post for an example o a posted table structure here

look at this post for one of many discussions on relationships and subforms here (although I haven't read right through it, ScottGem is involved and he really knows his stuff.
As for the junctionn table, this is the correct/ only approach to use (when properly implemented) when handling many to many relationships.
Drop a line when you have completed (or even during) your research if you have any questions.
Jim