How do I design tables that collect information to generate vario.

[Jo Anna]

I have worked with access for a few years and even designed a few databases
but none as large as this one and would love to benefit from others expertise
before I begin the design. This data base will be used for tracking
assignments for several employees - many assignments (in thousands) and
several employees. I will email the assignments to each employee (could be
25-50 assignments to each employee). The employee then returns a draft of
each assignment which I will then forward to an editor. There could be
several levels of correspondence between editors & the employee on each
assignment. When the assignment is approved by the editor I will then send
the assignment to our client for approval. The assignment could then involve
several levels of revision (client>me>editor>employee) before final
acceptance by the client. Once approved by the client a payment code is
assigned and this payment code is then applied to the employee’s record and
the accumulation of such assignment payments becomes the basis for the
employee’s pay. This data base must track the assignments received from the
client, showing progress on each assignment; must keep track of each
employee’s completed assignments and the payments received; and must be able
to generate reports for various units within our organization such as
payroll, billing and accounting; and possibly keep personnel type records for
these employees some of who are also under another payroll code on an hourly
basis. Since these assignments have a completion time factor I need to be
able to track employee’s leave time so that assignments are not left in
someone’s email and we miss deadlines.
I welcome any suggestions on building tables and establishing relationships
for this project. I also would like opinions on whether it would be easier to
use the assignment code given by the client; establish a new simpler one for
internal use and link it to the client’s code; or add an internal company
code in front of the client’s code. It is conceivable that the employee
originally assigned the project does not complete it and it would be
reassigned to another employee; otherwise, I would without hesitation use the
client’s code.

 

[Jeff Boyce]

Jo Anna

I am not clear, from your description, what the relationships are for your
situation. But I'd say you've posted in the correct newsgroup
(tablesdbdesign) for ideas on table structure and relationship.

I guess I'm a "picture" person. Could you repost a description along the
lines of:

We have many Employees
We have many Assignments
Employess work on one or more Assignments
We want to track the different things Employees do on their Assignments
We have Editors do things on Assignments (does that mean you could count

an Editor as just another type of Employee?)

We want to track the routing of the different things done on Assignments

(suggests knowing where each assignment is, at which stage, with which
Employee - ?Colonel Mustard, in the Conservatory, with the candlestick?!)

We have Clients do things on Assignments (whoops! Clients aren't Employees, right?)
We get paid by Clients for Assignments
We want to distribute payments to Employees
There may need to be interfaces to other systems in the business.

Whew! This is rather ambitious! Feel free to make any/all corrections to
my first stab at it.

I'm wondering, since you mentioned deadlines, if you'll have enough time to
work all the details out? Have you looked into existing applications that
might be able to do some/most/all of what you need. I'm not sure, but the
Customer Management or Enterprise Resource management fields offer
commercial applications (pricey to be sure!).

Good luck!

Jeff Boyce
<Access MVP>

 

[Jo Anna]

Jeff,
Thanks for jumping in the deep water to try to help me. I hope I have
included enough information in my responses so that we both can get to land.
Jo Anna

Jo Anna

I am not clear, from your description, what the relationships are for your
situation. But I'd say you've posted in the correct newsgroup
(tablesdbdesign) for ideas on table structure and relationship.

I guess I'm a "picture" person. Could you repost a description along the
lines of:

We need to know where the assignment is the the process. How close to
completion, make sure it doesn't get lost as it is routed from
client-employee-editor-client.

an Editor as just another type of Employee?)

yes editors for the most part are FTE hourly paid employees and these
"assignment employees" are part time employees paid on the basis of completed
& client approved assignments

(suggests knowing where each assignment is, at which stage, with which
Employee - ?Colonel Mustard, in the Conservatory, with the candlestick?!)

yes where each assignment is, at which stage, with which

Employee
Employees, right?)

Right, the clients are not our employees. They will either approve what we
send or send it back with request for additional work on the assignmentemployees get paid for assignments accepted by the client (editors are
hourly paid)

" Maybe interface is too strong a link. All I need to do is generate reports for payroll and billing. I will keep personnel type records for the part time employees for this project especially, contact information, leave time, etc.
Whew! This is rather ambitious! Feel free to make any/all corrections to
my first stab at it.

I'm wondering, since you mentioned deadlines, if you'll have enough time to
work all the details out? Have you looked into existing applications that
might be able to do some/most/all of what you need. I'm not sure, but the
Customer Management or Enterprise Resource management fields offer
commercial applications (pricey to be sure!).

As you can imagine with all the routing required for each assignemnt the
profit margin is very thin for this project and funds for additional software
was not included in the original planning.

 

[Jeff Boyce]

Jo Anna

I'm even more convinced now... if there's no budget for acquiring
off-the-shelf software that might meet your needs, how much of your time is
your company willing to commit to developing what sounds like a fairly
complex application.

If someone asked me to "size" what you've described, I would put it in the
"bigger than a breadbox", and perhaps "bigger than a boxcar" category.

I didn't see anything in your description that triggered alarms, but I can
imagine that what you've described (and left out) could be a year-long
project.

Best of luck!

Jeff Boyce
<Access MVP>

Jeff,
Thanks for jumping in the deep water to try to help me. I hope I have
included enough information in my responses so that we both can get to land.
Jo Anna

Assignments
We need to know where the assignment is the the process. How close to
completion, make sure it doesn't get lost as it is routed from
client-employee-editor-client.

yes editors for the most part are FTE hourly paid employees and these
"assignment employees" are part time employees paid on the basis of completed
& client approved assignments candlestick?!)
yes where each assignment is, at which stage, with which
Right, the clients are not our employees. They will either approve what we
send or send it back with request for additional work on the assignment
employees get paid for assignments accepted by the client (editors are
hourly paid)

reports for payroll and billing. I will keep personnel type records for the
part time employees for this project especially, contact information, leave
time, etc.