this could get ugly

[bensmeets]

Guys, I am having trouble building something in access and i think i should bring it bakc to table level and try to fix it there. It is vrey complicated i think so i might explain things wrong.

I have a table 'sessiontype', the session types have multiple 'task'. Then i have 'goals' and eacht task has multiple goals.

Here comes the tricky part.

Now i have 'sessions' who are an instance of 'sessiontypes' which occur on a certain time and date. These sessions have the same tasks and goals as the type it has originated from.

The point is i need to 'check' (yes/no field) per session if the goal for a certain task is met yes or no. If i do it like i do now, i check the goals as completed for the whole sessiontype.

I have made a whole mess out of all the relationships so would like to start over.

Anybody have any ideas to get me back on track, since i am completely lost for the time being...

 

[directormac]

Sounds like you're going to need lots of junction tables...

tblSessionTypes
autoSessionTypeKey (PK)
txtSessionTypeName

tblSessions
autoSessionKey (PK)
dtmSessionDateTime
fkSessionTypeID
- (1:M from tblSessionTypes.autoSessionTypeKey)

tblTasks
autoTaskKey (PK)
txtTaskName

tblTasksForSessionTypes
' this junction table is where you store default tasks
' associated with session TYPES...
fkSessionTypeID (complex PK 1/2)
- (1:M from tblSessionTypes.autoSessionTypeKey)
fkTaskID (complex PK 2/2)
- (1:M from tblTasks.autoTaskKey)

tblGoals
autoGoalKey (PK)
txtGoalName

tblGoalsForTasks
' this junction table is where you store which tasks need
' completion for which goals...
fkTaskID (complex PK 1/2)
- (1:M from tblTasks.autoTaskKey)
fkGoalID (complex PK 2/2)
- (1:M from tblGoals.autoGoalKey)


Then, when an actual specific session record is created, use code to determine which tasks must be completed for that session type and which goals must be completed for each of those tasks. Use that information to create a new record in the final junction table:


tblSessionToDo
' this junction table is where you store which goals need
' completion for which sessions, and whether or not they
' have been completed...
fkSessionID (complex PK 1/2)
- (1:M from tblSessions.autoSessionKey)
fkGoalID (complex PK 2/2)
- (1:M from tblGoals.autoGoalKey)
boolComplete


This is just a doodle to get you started. You'll need to spend some time with good old paper & penceil figuring out the tables to ensure normalization, and there will be a fair amount of coding involved to get the forms and reports to do what you want. Pat H. or one of the other list gods can probably show you a way to make the structure simpler...

--Sketchy Mac

 

[bensmeets]

tnx for the great reply directormac. I am going to get busy and try to understand/implement this kind of solution. Will let you know when and if i got it working

 

[directormac]

If you hit a snag, just shout out. Also, keep checking this post, as one of the many wiser folks may give you better guidance.

--Not-Actually-Senior Mac