possible multi table many to many relationships design confusion

[chaeljc]

hi guys and gals



this is my first ever access related post to any forum so forgive me if im not as clear as some or miss some procall etc.

im having difficulties understanding the relationships between a database i am trying to design the tables are as follows:


tblProjects
pkProjectID autoNumber
txtProjectName Text

tblStages
pkStageID autoNumber
txtStageName text
fkProjectID number

tblRecords
pkRecordID autoNumber
txtRecordContent text
fkProjectID number

tblStageAssignee
pkStageOfficerID autoNumber
txtStageOfficerName text


Each Project can have a variable number of stages (of work items which need to be completed).
This i can administer via a form with subForm using a 1 to many link from tblProjects/pkProjectID to tblStages/fkProjectID.

Each Project can have many records.
This i can administer via a form with subForm using a 1 to many link from tblProjects/pkProjectID to tblRecords/fkProjectID.

HERE'S WERE MY DIFFICULTIES ARISE...

i can create a Parent form 'ProjectRecords' which brings together on single lines the Project and Records information. All goo

In a subForm of the 'ProjectRecords' form I would like to bring together the Stages related to the Parent Project and assign the StageOfficer who will complete the work on that particular stage. It could be any one of the StageOfficers who could be completing any of the stages for any of the records.

Ive attached a word document which has a diagram detailing my understanding of the tables' relationships.

Any help with this would be MOST appreciated as i have wracked my brain for a few days and can't figure out if there is a missing many to many junction table or not etc.

Many Many thanks in advance.

Michael

[June7]

I don't understand. If officers are assigned to stage why is the recordID a fk in tblStageAssignee?

What has a stage - Project or Record?

Seems to me Projects have Records and Records have Stages. That seems to be demonstrated by the 'IDEAL Form' graphic. Consider:

tblProjects
ProjectID (PK)
ProjectName

tblOfficers
OfficerID (PK)
LastName
FirstName

tblRecords
RecordID (PK)
ProjectID (FK)
RecordContent

tblStagesRecords
StageID (PK)
RecordID (FK)
OfficerID (FK)

Is there a standardized set of 'stages'? How many? Should there be a table for these standard stages? If so, then save StageID in tblStagesRecords as a FK.

[chaeljc]

Hi there June7

many thanks for your speedy reply. Much appreciated as this problem has been torturous for me for several days now. lol

you are indeed correct, Projects have Records and Records have Stages.

Yes there is there a standardized set of 'stages' held in a stagesMaster list. Potentially any stage in the list can be assigned to any project. Some projects may only have 1 stage, some for example 8. The number of stages in the master can rise if a new project is added to the database and the master list of stages does not include a work stage that is required by the new project.

The system process is as follows:

When a new project is added to the system at that point that the (work) stages are set up for that project. There is a master list of possible stages, for example stages 1 - 10. When a new project is being set up, any number of the stages from the stagesMasterList table can be assigned to that project in any order.

If a project is added to the database which requires stages which are not included in the example 10 stages already set up in the stages master list table, new stages can be added to the stagesMasterList to accomodate the new project.

These new stages have the potential to be used by any future projects.

The order that the stages are assigned to the project is the order in which they must be worked.

It is only once the Project data, including which stages (and their order of work) have been defined, that the records which relate to that project are added to the Records table. Additional records which relate to that project may be added to the records table at any point in the future.

It is probably embarrassingly simple but now that the project has its stages defined, and records added, i am looking a end form similar to the form in the attachment (??) which allows me to see a list of all projects with associated records on single lines in the in the parent form, with the sub form showing the stages previously set up for that project on the left, in their correct order, with a corresponding space on the right of the subform from which, by way of a combo box, from a master list of staff i can assign who is to work each stage of each record.

For each project, every record assigned to that project will have all the stages which were assigned to the project, assigned to that record.
each of the stages for the record is worked by one person.
Different persons, or the same, may be assigned to work each stage of each record.


A summary of the database order of process is:
1. New project details are added to database Projects table.
2. From a master list of stages the stages which apply to the new project are selected.
3. The records relating to that project are added to the database
4. Staff who are going to work each stage of each record are assigned by managers to each of each records stages from a master list of staff.

It's the way to bring together data so that point 4 is possible that has my head twisted. lol


'..I don't understand. If officers are assigned to stage why is the recordID a fk in tblStageAssignee?..'

to attempt to answer your question above (and it's fuzzy here for me too) .....

Stages are assigned to the projects - from the previouly mentioned stages master list.
Persons are assigned to work the (project's) stages of a particular record, hence the record ID

As i said, this is the area where im having the difficulty, so my logic, PK's and FK's etc could be waay off the mark. Also because im not clear hear i am not sure if there is a many to many relationship and junction table in there some where, futher clouding my thinking.


I really appreciate your speedy help to date and hope the above has clarified the problem (if it's clear to you your doing waaay better than me! lol)

If i can get it all to work i shall of course 'up' an example in the hope it is of use to others.


Kind regards


Michael

I don't understand. If officers are assigned to stage why is the recordID a fk in tblStageAssignee?

What has a stage - Project or Record?

Seems to me Projects have Records and Records have Stages. That seems to be demonstrated by the 'IDEAL Form' graphic. Consider:

tblProjects
ProjectID (PK)
ProjectName

tblOfficers
OfficerID (PK)
LastName
FirstName

tblRecords
RecordID (PK)
ProjectID (FK)
RecordContent

tblStagesRecords
StageID (PK)
RecordID (FK)
OfficerID (FK)

Is there a standardized set of 'stages'? How many? Should there be a table for these standard stages? If so, then save StageID in tblStagesRecords as a FK.

[June7]

I still don't understand what a 'Record' is.

A Project can have many Records?

The Stages assigned to a project must be tracked for each 'Record'?

So if a Project has 10 Stages, and 5 Records, all 10 Stages much be documented for each Record?

[chaeljc]

hi June7

thanks for your super speedy reply!

Yes you are correct


A Project can have many Records? - Yes for example it could have 10 or 1000

The Stages assigned to a project must be tracked for each 'Record'? - Yes

So if a Project has 10 Stages, and 5 Records, all 10 Stages much be documented for each Record? Yes.

(Once again, this may not be the correct way to do things but...) at the moment I have a table called RecordStages which is linked to the records table. Using your example above ... for each of your 5 records each of the 10 stages would be documented so that it is possible to view / report etc who was assigned to work each stage, the date each stage was started and date it was finished.

I hope this helps.

regards Michael

[June7]

Then also consider this, whichever will work best for you:

tblProjects
ProjectID (PK)
ProjectName

tblOfficers
OfficerID (PK)
LastName
FirstName

tblStages
StageID (PK)
StageDesc

tblProjectRecords
RecordID (PK)
ProjectID (FK)
RecordContent

tblProjectStages
ID (PK)
StageID (FK)
ProjectID (FK)

tblStagesRecords
ProjStageID (FK)
ProjRecID (FK)
OfficerID (FK)

[chaeljc]

hey june7

again many thanks for this, i cannot emphaisis how much this problem has been holding me back and therefore how much i appreciate the timelyness of your replys.

i attempt to use your suggestion below to create a trial version. if i may, i might text you again for clarification of what is related to what or what queries might be necessary to bring it all together.... but i shall not do so with out trying my best first.

many thanks for this advice

kind regards

michael (from not so sunny norther ireland lol )

Then also consider this, whichever will work best for you:

tblProjects
ProjectID (PK)
ProjectName

tblOfficers
OfficerID (PK)
LastName
FirstName

tblStages
StageID (PK)
StageDesc

tblProjectRecords
RecordID (PK)
ProjectID (FK)
RecordContent

tblProjectStages
ID (PK)
StageID (FK)
ProjectID (FK)

tblStagesRecords
ProjStageID (FK)
ProjRecID (FK)
OfficerID (FK)

[chaeljc]

hi june7

ive been working away ever since your last email and have the database in the format that i need, however i am unable to add the officer who is to work a particular stage of a record.

Rather than try to explain i have attached a zipped copy of the dB - Project Database.zip. with in the dB, if you open form 'projectRecordsStagesOfficers Parent' you will see the form and sub form basically as it is required to be EXCEPT i am unable to make it that i can assign an officer to the stages associated with each selected record.

Thinking that the solution might involve some combination of queries or many to many relationships which ill not get...I would be most grateful if you could take a look at the dB and were able to adjust that it you can assign an officer to each of the stages associated with each record.

i've been trying for 4 days solid (work and home) and am totally beat. its probably embarrassingly simple but i just cant work it out.

regards

michael

hey june7

again many thanks for this, i cannot emphaisis how much this problem has been holding me back and therefore how much i appreciate the timelyness of your replys.

i attempt to use your suggestion below to create a trial version. if i may, i might text you again for clarification of what is related to what or what queries might be necessary to bring it all together.... but i shall not do so with out trying my best first.

many thanks for this advice

kind regards

michael (from not so sunny norther ireland lol )

[orange]

I haven't looked at your data base but I did see you may have a many to many issue.
I recommend you review this free video re resolving many to many relationships.

https://www.youtube.com/watch?v=7XstSSyG8fw

[chaeljc]

hey orange

many thanks to the link.. will be having a look in a couple of mins.

tho no expert (obviously lol) i have successfully built databases before with many to many relationships and junction tables. this one is just torturing me. lol.

a record can have many stages and a stage can have many records.. that i know but i just can't square the circle this time.

many thanks ... im off to look at your vid.

regards

michael

I haven't looked at your data base but I did see you may have a many to many issue.
I recommend you review this free video re resolving many to many relationships.

https://www.youtube.com/watch?v=7XstSSyG8fw

[chaeljc]

just a quick note...


as you can probably tell from reading the thread im very new to posting on boards (this is my first ever IT thread) .. and pretty desparate to resolve problem, never mind this weekend, its held me back for several days in work.

in an effort to hopefully resolve asap i put the same problem up at the MS Access Help Centre Forum. They pointed out the protocal for 'cross posting' Obvious really, but it never occurred to me (still sitting here focused on trying to resolve prob) so apologies for that.
i do get prob sorted ill be sure to update at once.


TO ORANGE

i watched the video thanks and was familiar with it ok. i have had a couple of many to many encounters in the past and been able to resolve ok. this time there appear to be several many to manys and at certain points im not sure when building forms if i should be using the original tables, the junction tables or a combo of both wrapped up in queries. a totally tangled nightmare! lol

regards michael

hey orange

many thanks to the link.. will be having a look in a couple of mins.

tho no expert (obviously lol) i have successfully built databases before with many to many relationships and junction tables. this one is just torturing me. lol.

a record can have many stages and a stage can have many records.. that i know but i just can't square the circle this time.

many thanks ... im off to look at your vid.

regards

michael

[June7]

The RecordSource for ProjectsRecordStagesOfficers subform should not include a join on the ProjectID. In fact, I don't understand why you even have the fkProject in that table. The relationship to Project is through the ProjectRecords table by saving the ProjectRecords pk as fk.

Instead of binding the subform to query, use a form bound to ProjectsRecordStagesOfficers table. The form would have a combobox to select the stages from the ProjectStages table. Restrict the combobox RowSource to only the stages relevant to the current project of the main form. That's the purpose of the ProjectStages table - as source of stages associated with each project. This will require code to requery the combobox RowSource when moving to different ProjectRecord. If you want to pre-establish a set of Stage records for each ProjectRecord, that will be more code and rather complex.

Neither of the saved queries are useful.

[chaeljc]

hi june7

ta for sticking with me on this... im not sure why i have some of the joins myself. i think i follow what your saying and will try to sort.

in the sub form it is necessary to be able to see the stages pre assigned to the project record before assigning the officers to those stages.

you mention possible complex coding. im reasonablly proficient in vba...if you could, along what lines do you foresee?

many thanks

michael

The RecordSource for ProjectsRecordStagesOfficers subform should not include a join on the ProjectID. In fact, I don't understand why you even have the fkProject in that table. The relationship to Project is through the ProjectRecords table by saving the ProjectRecords pk as fk.

Instead of binding the subform to query, use a form bound to ProjectsRecordStagesOfficers table. The form would have a combobox to select the stages from the ProjectStages table. Restrict the combobox RowSource to only the stages relevant to the current project of the main form. That's the purpose of the ProjectStages table - as source of stages associated with each project. This will require code to requery the combobox RowSource when moving to different ProjectRecord. If you want to pre-establish a set of Stage records for each ProjectRecord, that will be more code and rather complex.

Neither of the saved queries are useful.

[June7]

If you want to pre-establish the ProjectRecordStage records, use append (INSERT) sql actions to write records to table. The VBA is not really complicated but the SQL statement can be confusing.

https://www.accessforums.net/forms/c...end-29682.html

In your case, you want to save a set of RecordStageOfficer records for each ProjectRecord based on the associations defined in ProjectStages. In VBA, something like:


If IsNull(DLookup("fkProjectID", "SELECT fkProjectID FROM tblProjectsRecords INNER JOIN [tbl ProjectRecordsStagesOfficers] ON tblProjectsRecords.pkProjectRecordsID = [tbl ProjectRecordsStagesOfficers].fkProjectRecords", "fkProjectID=" & Me.ProjectID)) Then

CurrentDb.Execute "INSERT INTO [tbl ProjectRecordsStagesOfficers](fkProjectRecords, fkProjectStages) SELECT pkProjectRecordsID, pkProjectStagesID
FROM tblProjectsRecords INNER JOIN [tbl ProjectStages] ON tblProjectsRecords.fkProjectID = [tbl ProjectStages].fkProjectID WHERE [tbl ProjectStages].fkProjectID = " & Me.ProjectID

End If

Real trick is figuring out what event to put the code in. Maybe a button click on the main form.

BTW, advise not to use spaces, special characters, punctuation (underscore is exception) in any names, nor reserved words (such as Date) as names. Mention this because of space in most of the table, query, form names.

[chaeljc]

hey there june7

many thanks for your last post ... at least now im starting to see it its going to envolve a bit more than relationships and queries! lol it's sunday morning as i write this and unfortunately i have a few non access related things to attend to today so i wont get a chance to work on your last post til later tonight or monday at work again. ill keep you posted asap.

hope your having a good weekend

regards

michael