rotating table records in a form

  • Thread starter Thread starter Sabosis
  • Start date Start date
S

Sabosis

Hello-

I'm stumped and can't find any previous posts that address what i
wanted to do. I want to have a simple table that holds employee names;
just ID, Fname, and Lname. Another ID will hold simple info on
projects; ProjectID, Date, Desc, completed (Y/N). My goal was to have
a form that when opened would display the person in line for the next
project. The project description would be filled and the record saved.
The next time a project comes up and the form is opened, the next
employee in line would be displayed, the description box filled out,
form saved, etc.
Essentially, I want the form to manage a rotation of the table of
employees so that first employee gets project A, the second rep gets
project B, etc, etc.
Is this possible? Please let me know if anyone has some ideas.

Thanks-

Scott
 
Define "next-in-line" ...

How you might do this will depend on what definition you use.

?Are you using the same definition of "next-in-line" for both the persons
and the projects?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Definitely need more info to be sure, but the way I did this in the past was
to create a query that did my ranking (determined who was next).  Then I can
just take the first one in the list, using a TOP VALUES query...
Click to expand...

In response to Jeff's post, and I hope I am able to clarify correctly,
I just want the employees to be rotatedone at a time when a new
project comes available. The project work itselt will vary in scope
and difficulty, but I am trying to get a systematic appraoch to
deterrmine who is in line for the project at hand, and the project
after that, etc. I will be using the projects table to store info
about the project itself, but the main goal of the database is to
handle a rotational selection of the employees. I have several
supervisors that will be receiving projects that must be delegated to
our team members. In concept, we thought of using a spreadsheet to
have a list of employees, then when the employee in row 1 was given a
project, he/she would be placed at the bottom of the list by cutting
the row and pasting it to the bottom. We would do this until that
employee eventually worked their way back to the top of the list. But
managing the list amongst several supervisor lends itself to errors,
so I thought a database could do this, as well as store the project
info in a seperate table. I hope this helps to understand what I am
trying to do, please let me know if you have any more questions.

Thanks-

Scott
 
If I'm understanding your description, by "next-in-line" you mean the
employee who is actually, physically, the next one on a list.

Be aware that Access does NOT put records-in-a-table in any sort of order
you or I might recognize or understand. To get that, you need to
incorporate Pieter's notion of ranking/ordering. This may even take adding
a new field to your table.

Let's see if the way I paraphrase what you're trying to do makes sense to
you...

You have a list of employees, organized/sorted/ranked by some means.
You have a list of projects, organized/sorted...

You want to assign an employee to a project (that doesn't already have
someone assigned).
You want to ensure that all employees have an equal load of projects (i.e.,
no more than one more than anyone else).

If this is a fair representation, then one approach you could use would be
to retrieve the number of projects each employee has (already) been
assigned, and sort these by Count-of-assigned-projects. Whoever shows up
'short' on the list is next assigned.

You'd also need to select all projects without assigned employees. If
there's some sort order (?the date the project hit the books?), you could
take the 'earliest' project (see Totals queries, using Minimum) to be
assigned.

One small wrinkle ... can I assume that projects get completed? If so,
you'll want to exclude "Completed Projects" from the list of available
projects, and you may even wish to exclude them from the count of the number
of projects each employee is assigned to.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If I'm understanding your description, by "next-in-line" you mean the
employee who is actually, physically, the next one on a list.

Be aware that Access does NOT put records-in-a-table in any sort of order
you or I might recognize or understand.  To get that, you need to
incorporate Pieter's notion of ranking/ordering.  This may even take adding
a new field to your table.

Let's see if the way I paraphrase what you're trying to do makes sense to
you...

You have a list of employees, organized/sorted/ranked by some means.
You have a list of projects, organized/sorted...

You want to assign an employee to a project (that doesn't already have
someone assigned).
You want to ensure that all employees have an equal load of projects (i.e..,
no more than one more than anyone else).

If this is a fair representation, then one approach you could use would be
to retrieve the number of projects each employee has (already) been
assigned, and sort these by Count-of-assigned-projects.  Whoever shows up
'short' on the list is next assigned.

You'd also need to select all projects without assigned employees.  If
there's some sort order (?the date the project hit the books?), you could
take the 'earliest' project (see Totals queries, using Minimum) to be
assigned.

One small wrinkle ... can I assume that projects get completed?  If so,
you'll want to exclude "Completed Projects" from the list of available
projects, and you may even wish to exclude them from the count of the number
of projects each employee is assigned to.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

Ok Jeff, I think that will get me going. If I have a quesry that
displays info by count of assigned projects, with a second sort by
alpha name to keep things consistent, then I should get where I need
to be. It makes sense now, just needed someone to point out the right
direction. Many thanks for your input.

Scott
 
Back
Top