I have a database that has 3 tables: Associates, Tasks and Schedules. The
associates table has a list of the associates and the individual tasks, if
the particular task has a check mark in the box that means the associate can
peform that task. What I would like to do, if possible, is have a module
that looks to see if the associate is working that day (from the schedules
table) and check to see if the associate can peform that task and if so
automatically assign that task to that associate. However to ensure that all
associates get their turn at the various tasks, it needs to look at the
previous task asignments and not schedule them again to a particular
associate until it has cycled through all the other associates. There all
about 30 associates and 15 tasks. Can this be done in access? and if so how?
Thanks for any help or suggestions.

Hi Rascal,

I am assuming you are using a bunch of checkboxes... there
is a better way

*Associates*
AssocID, autonumber
Firstname, text
Lastname, text
etc

*Tasks*
TaskID, autonumber
Task, text

*AssociateTasks*
AssocTaskID, autonumber
AssocID, long integer
TaskID, long integer

*Schedules*
SchedID, autonumber
SchedDate, date
AssocID, long integer, DefaultValue--> =null
TaskID, long integer

create the records in Schedules that need to be performed
(that is why you should set the DefaultValue of AssocID to
null so there is not a problem with records not being filled
out with AssocID when referential integrity is enforced)

make a query showing the Tasks that each Accocite has
already performed (or is assigned to):

Name --> qryAssociateTasksPerformed

SELECT DISTINCT AssocID, TaskID FROM Schedules

First, figure out which associated have not been assigned
tasks that need to be done I assume you are using the QBE
grid to make your queries

1. make a new query
2. add qryAssociateTasksPerformed and Schedules for your
fieldlists

field --> AssocID
table --> qryAssociateTasksPerformed
criteria --> Is Null

field --> TaskID
table --> Schedules

field --> AssocID
table --> Schedules
criteria --> Is Null

this will give you a list of Associates that have not yet
performed that task

Hopefully, this gives you some food for thought and gets you
thinking about a path you can take to solve your problem.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day


rascal wrote:
> I have a database that has 3 tables: Associates, Tasks and Schedules. The
> associates table has a list of the associates and the individual tasks, if
> the particular task has a check mark in the box that means the associate can
> peform that task. What I would like to do, if possible, is have a module
> that looks to see if the associate is working that day (from the schedules
> table) and check to see if the associate can peform that task and if so
> automatically assign that task to that associate. However to ensure that all
> associates get their turn at the various tasks, it needs to look at the
> previous task asignments and not schedule them again to a particular
> associate until it has cycled through all the other associates. There all
> about 30 associates and 15 tasks. Can this be done in access? and if so how?
> Thanks for any help or suggestions.

Thanks Crystal:
I will try it that way and see how it goes and whether I can take it to the
next step.

"strive4peace" wrote:

> Hi Rascal,
>
> I am assuming you are using a bunch of checkboxes... there
> is a better way
>
> *Associates*
> AssocID, autonumber
> Firstname, text
> Lastname, text
> etc
>
> *Tasks*
> TaskID, autonumber
> Task, text
>
> *AssociateTasks*
> AssocTaskID, autonumber
> AssocID, long integer
> TaskID, long integer
>
> *Schedules*
> SchedID, autonumber
> SchedDate, date
> AssocID, long integer, DefaultValue--> =null
> TaskID, long integer
>
> create the records in Schedules that need to be performed
> (that is why you should set the DefaultValue of AssocID to
> null so there is not a problem with records not being filled
> out with AssocID when referential integrity is enforced)
>
> make a query showing the Tasks that each Accocite has
> already performed (or is assigned to):
>
> Name --> qryAssociateTasksPerformed
>
> SELECT DISTINCT AssocID, TaskID FROM Schedules
>
> First, figure out which associated have not been assigned
> tasks that need to be done I assume you are using the QBE
> grid to make your queries
>
> 1. make a new query
> 2. add qryAssociateTasksPerformed and Schedules for your
> fieldlists
>
> field --> AssocID
> table --> qryAssociateTasksPerformed
> criteria --> Is Null
>
> field --> TaskID
> table --> Schedules
>
> field --> AssocID
> table --> Schedules
> criteria --> Is Null
>
> this will give you a list of Associates that have not yet
> performed that task
>
> Hopefully, this gives you some food for thought and gets you
> thinking about a path you can take to solve your problem.
>
> Warm Regards,
> Crystal
> MVP Microsoft Access
>
> remote programming and training
> strive4peace2006 at yahoo.com
> *
> Have an awesome day
>
>
> rascal wrote:
> > I have a database that has 3 tables: Associates, Tasks and Schedules. The
> > associates table has a list of the associates and the individual tasks, if
> > the particular task has a check mark in the box that means the associate can
> > peform that task. What I would like to do, if possible, is have a module
> > that looks to see if the associate is working that day (from the schedules
> > table) and check to see if the associate can peform that task and if so
> > automatically assign that task to that associate. However to ensure that all
> > associates get their turn at the various tasks, it needs to look at the
> > previous task asignments and not schedule them again to a particular
> > associate until it has cycled through all the other associates. There all
> > about 30 associates and 15 tasks. Can this be done in access? and if so how?
> > Thanks for any help or suggestions.
>

you're welcome, Rascal

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day


rascal wrote:
> Thanks Crystal:
> I will try it that way and see how it goes and whether I can take it to the
> next step.
>
> "strive4peace" wrote:
>
>
>>Hi Rascal,
>>
>>I am assuming you are using a bunch of checkboxes... there
>>is a better way
>>
>>*Associates*
>>AssocID, autonumber
>>Firstname, text
>>Lastname, text
>>etc
>>
>>*Tasks*
>>TaskID, autonumber
>>Task, text
>>
>>*AssociateTasks*
>>AssocTaskID, autonumber
>>AssocID, long integer
>>TaskID, long integer
>>
>>*Schedules*
>>SchedID, autonumber
>>SchedDate, date
>>AssocID, long integer, DefaultValue--> =null
>>TaskID, long integer
>>
>>create the records in Schedules that need to be performed
>>(that is why you should set the DefaultValue of AssocID to
>>null so there is not a problem with records not being filled
>>out with AssocID when referential integrity is enforced)
>>
>>make a query showing the Tasks that each Accocite has
>>already performed (or is assigned to):
>>
>>Name --> qryAssociateTasksPerformed
>>
>>SELECT DISTINCT AssocID, TaskID FROM Schedules
>>
>>First, figure out which associated have not been assigned
>>tasks that need to be done I assume you are using the QBE
>>grid to make your queries
>>
>>1. make a new query
>>2. add qryAssociateTasksPerformed and Schedules for your
>>fieldlists
>>
>>field --> AssocID
>>table --> qryAssociateTasksPerformed
>>criteria --> Is Null
>>
>>field --> TaskID
>>table --> Schedules
>>
>>field --> AssocID
>>table --> Schedules
>>criteria --> Is Null
>>
>>this will give you a list of Associates that have not yet
>>performed that task
>>
>>Hopefully, this gives you some food for thought and gets you
>>thinking about a path you can take to solve your problem.
>>
>>Warm Regards,
>>Crystal
>>MVP Microsoft Access
>>
>> remote programming and training
>> strive4peace2006 at yahoo.com
>> *
>> Have an awesome day
>>
>>
>>rascal wrote:
>>
>>>I have a database that has 3 tables: Associates, Tasks and Schedules. The
>>>associates table has a list of the associates and the individual tasks, if
>>>the particular task has a check mark in the box that means the associate can
>>>peform that task. What I would like to do, if possible, is have a module
>>>that looks to see if the associate is working that day (from the schedules
>>>table) and check to see if the associate can peform that task and if so
>>>automatically assign that task to that associate. However to ensure that all
>>>associates get their turn at the various tasks, it needs to look at the
>>>previous task asignments and not schedule them again to a particular
>>>associate until it has cycled through all the other associates. There all
>>>about 30 associates and 15 tasks. Can this be done in access? and if so how?
>>>Thanks for any help or suggestions.
>>