Database Relationships

[TheExample]

I have 4 tables.

Project table (main project information)

SubProject table (events each related to the main project table with a foreign key)

Project Progress (progress toward main projects)

Sub Project Progress table (progress observed towards sub projects.)

The problem is created when all of these tables are joined with either one query or different queries. The tables are many to many and even have attempted to set up a junction table to hold everything. I must have the relationships set up wrong. Not sure how but I get repeated values within the sub projects when the progress tables are added into the equation. Very frustrating. Please help someone. Anyone!!!

 

[GingerR]

Seems the main table ("Projects") should be "one" not "many"? please list the key fields in each table, sample data, and please list your problem query.

Thanks.

 

[TheExample]

Thanks for the quick response.

Ok here it goes. Thanks so much for even trying to help me

Project Table (One to many relationship)
EventID
EventName
StartDate
EndDate
AssignedTo


SubProject table (Many to one EventID but Many to Many progress)
SubEventID (One)
EventID (Many)
StartDate
EndDate
AssignedTo


EventProgress Table (Many to each eventID)
EventID (many)
ProgressID (one)
ProgressDescription
EnteredBy

SubeventProgress Table (Many to each SubeventID)
SubeventID (many)
SubProgressID (one)
SubProgressDescription
Enteredby

Basically there is a form for people to enter a main project and who is working on it and when it is due. they can then enter sub events to complete this project in addition to entering how much progress they have made towards the main project or the sub events. Not more complicated than I first thought.

I am trying to create a query that will show

Event Name
EventProgressdescriptions
Subevent Name
SubEventProgressdescriptions

So far a query is giving me a very strange dynaset. It keeps repeating the Progress descriptions for each event and subevent. I have tried to create a query for just the main events and the progress. No problems. I have even created queries for the Main events and subevents. No problems. It is when these progress descriptions are added to the query that everything becomes too complicated. I have tried to query a query but to no avail. I hope you have some other ideas for me.

 

[GingerR]

The query is behaving exactly as you are asking it to. "it becomes too complicated": Say for one event you have three sub-events and two Progresses. You will get six records, repeating some of the info in some of the fields.

Are you trying to build a report with this query? Or a form for viewing the info? Instead of trying to cram all of the related info into one query to use as a recordsource, try using subreports and subforms. Will that work for you?

 

[TheExample]

I do have forms and subforms. I was trying to create a query that would pull all of the data together. I will try a report/ subreport. Any ideas of which would work best? Main project then have the sub projects as sub events?

 

[GingerR]

Depends on what you are trying to accomplish. If you are just wanting to display each Main Project and all of it's details, you'd make your report/subreports probably just the same as your existing form/subforms. In fact you can copy/paste code and controls to reports from the forms.

Main Report #1= based on table Project.

Subreport #2 = based on SubProjects
Subreport #3 = based on EventProgress table
Subreport #4 = based on SubeventProgress table

#4 will be embededded into #2, with Master/Child links = SubEventID.

#3 and #2 will both be embedded into #1 (MAIN) with Master/Child links = EventID.

Unless I'm not understanding your structure )
=================================================
Say later you are only wanting to show those projects that have ProjectProgress = "INCOMPLETE" or something like that. You'd handle that in the recordsource of the #1 (MAIN) form, with the results just being a unique list of those EventID's that are in the results you want. Their cooresponding details will be listed below, as usual.