Hide a row using a y/n box when duplicates are created due to a 1:Many relationship

[Bkper087]

I am working to create a database to manage my daily/weekly/yearly task list. I do a lot with event planning and the events are all similar enough that I often use what I call “backdaters.” There may be another term out there for them; however, simply put they are a list of tasks that need to be accomplished leading up to during and after an event. Each task also has an associated number of days out that it should be completed. By adding the days out and the event date in a query, each task is assigned a date to be completed. My goal is to be able to build multiple standard backdaters in this table that are identified by their categoryfk. If I am conducting 4 events in the future that are all similar, lets say a birthday party; I want to be able to put an event date on the lktEventsT for each birthday party and have access automatically generate the task list for each party in a master query.

I have a few basic tables which are described here:

BackdaterT
TaskID
Task
DaysPrior


Urgent [Y/N]
Completed [Y/N]
Categoryfk
EventDatefk

lktEventsT
EventID
Event
EventDate

lktCategoryT
CategoryID
Category

I then created a query that looks like this:

TaskID
Task
DaysPrior
Urgent [Y/N]
Completed [Y/N]
Categoryfk
EventDatefk

I have worked out the relationships and filters so that I can load multiple types of events, like the four birthday parties and their associated tasks in the backdater, but I will only see those tasks in the query related to events that are listed in the lktEventsT and have a date associated.

Here is the dilemma. I want to be able once they are all in a master query to check off a task once it is completed to hide it from the list until the event is done. Then I would simply remove it from the events table. The problem is access will produce multiple tasks, one associated for each date associated; however, it does’t let me use the check box functionally, I believe, because it sees it as a single task. I know I could make it into a table, but that would essentially defeat the purpose of what I am trying to do.

Is there a way to resolve this or is this just a limitation of Access?

[June7]

You can have a 'template' table that associates common tasks for a type (Birthday, Wedding Anniversary, etc) of event. Then you need a table of actual events (with EventID, date, customerID).

If you want to be able to 'check off' accomplishment of tasks for a particular contractual event, will need records for that. The 'template' table can be used as source for creating the task records associated with actual event. These records would be associated by EventID.

[Bkper087]

Thanks June7. I have created those relationships already in the senerio above. The real issue is that once the query is created, the tasks no longer have a unique Primary Key, because they are being repeated multiple times due to multiple events they are associated with. I am trying to see if there is a way to give each record in the query a unique primary key.

[June7]

Okay, think I understand the issue. Multiple same event type (birthday, etc) can occur on same date.

Consider:

tblTasks
TaskID
TaskName

tblEventTypes
TypeID
TypeName

tblEvents
EventID
EventDateTime
TypeID_FK
other info about this event - location, customer

tblEventTasks
EventID_FK
TaskID_FK
IsUrgent
IsCompleted

Now if you want a table that associates tasks and events to use as a template to create the records in tblEventTasks:

tblTypeTasksTemplate
TypeID_FK
TaskID_FK

Then an INSERT SELECT action can establish records for later editing. Initiate event record on a form by selecting an event type ID from a combobox bound to tblEvents TypeID_FK and reference it for selecting records. First must commit the event record to table.

DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "INSERT into tblEventTasks(EventID, TaskID) SELECT " & Me!EventID & " AS EID, TaskID_FK FROM tblTypeTasksTemplate WHERE TypeID_FK=" & Me.cbxTypeID