Need a design suggestion

[BobV]

Hopefully this is the right newsgroup to ask this (and I apologize up
front--this is probably going to get kind of long).....it crosses so many
lines it's hard to tell, but since it all starts with the tables, I thought
I'd start here. Anyway, the boss wants me to create a db for the managers
in our organization to post personnel hours devoted to various
projects.....kind of like a regular time-management db, but he would like to
make it so simple and fool-proof for them to use that it's actually harder
to design (at least it seems that way to me). Basically, it has three
tables:

Personnel
Process
Recorded Activity (which will contain information about what process was
worked by which employee)

He would like a form which will use information from the first two tables to
populate the last table. Trouble is, he would also like a form dedicated to
each process. The following are his instructions:

**************************************
When the form is opened it will show:

* the name of the process at the top
* the most recent pay period
* the names of the employees associated with the process during that pay
period
* the percent of time each worked on the process
* any overtime dedicated to the process
* there needs to be blocks below the list of names so that additional
employees can be added. The names should be selected from a drop down menu
so we don't have issues with name spelling. For planning purposes, the form
should allow for a combined total of 10 employees associated with the
process.

The supervisor would update the pay period, confirm the name of the
employee, percent worked and OT. If they need to add an employee they should
select the name, enter the percent and any OT.

Once the supervisor is happy with the info, they submit the form which sends
that data to the Recorded Activity Table.

Is it possible to run a check of the Recorded Activity Table, when the
supervisor goes to submit the data, to make sure no employee on the form has
more than 100% total process activity for that pay period?

****************************************

So, basically, for every "process" I need to create a form; which as I see
it will make it very hard to maintain (well, maybe not hard, but
time-intensive). I guess that's not a problem, as each form can populate
the same table; just seems like a dropdown to select the process would be a
lot less work--but I guess he feels that would be over their heads (and, for
the record, I don't necessarily disagree with that assessment).

Other problems I see?.....how do I make the form go to the latest pay period
upon opening it? Never really done anything like that? Kind of
like......if it's July 27th when I open a particular process form, it would
automatically populate the field with Pay Period 14 (or whatever pay period
the date falls under).

Also, not sure about "submitting" the data, or checking it for accuracy
before doing that.

At any rate, I would appreciate any guidance/suggestions anyone might
provide....thanks in advance,
Bob

 

[Jeff Boyce]

Bob

Perhaps it's only me, but my impression is that you've asked the group to
design your system. You'll probably get more response if you ask a specific
question, especially after indicating what you've already tried.

Most of the folks who reply in these newsgroups do so voluntarily, so asking
for someone to build you a system (or at least the design) may not get many
takers...

 

[BobV]

Sorry about that.....I didn't mean to make it appear like I wanted someone
to design the whole thing--I actually only meant to ask the couple of
questions at the end.....sometimes too much information is not good, I
guess.....but I thought I should explain the whole thing for a better
understanding of what's going on. At any rate, I can make what he wants
work, but here are the things I don't know:

* How do I make the form go to the latest pay period upon opening it? Kind
of like......if it's July 27th when I open a particular process form, it
would automatically populate the field with Pay Period 14 (or whatever pay
period the date falls under).

* Not sure about "submitting" the data, or checking it for accuracy before
doing that.

Sorry for the confusion....thanks,
Bob

Bob

Perhaps it's only me, but my impression is that you've asked the group to
design your system. You'll probably get more response if you ask a specific
question, especially after indicating what you've already tried.

Most of the folks who reply in these newsgroups do so voluntarily, so asking
for someone to build you a system (or at least the design) may not get many
takers...

--
Good luck

Jeff Boyce
<Access MVP>

Hopefully this is the right newsgroup to ask this (and I apologize up
front--this is probably going to get kind of long).....it crosses so many
lines it's hard to tell, but since it all starts with the tables, I thought
I'd start here. Anyway, the boss wants me to create a db for the managers
in our organization to post personnel hours devoted to various
projects.....kind of like a regular time-management db, but he would like to
make it so simple and fool-proof for them to use that it's actually harder
to design (at least it seems that way to me). Basically, it has three
tables:

Personnel
Process
Recorded Activity (which will contain information about what process was
worked by which employee)

He would like a form which will use information from the first two tables to
populate the last table. Trouble is, he would also like a form dedicated to
each process. The following are his instructions:

**************************************
When the form is opened it will show:

* the name of the process at the top
* the most recent pay period
* the names of the employees associated with the process during that pay
period
* the percent of time each worked on the process
* any overtime dedicated to the process
* there needs to be blocks below the list of names so that additional
employees can be added. The names should be selected from a drop down menu
so we don't have issues with name spelling. For planning purposes, the form
should allow for a combined total of 10 employees associated with the
process.

The supervisor would update the pay period, confirm the name of the
employee, percent worked and OT. If they need to add an employee they should
select the name, enter the percent and any OT.

Once the supervisor is happy with the info, they submit the form which sends
that data to the Recorded Activity Table.

Is it possible to run a check of the Recorded Activity Table, when the
supervisor goes to submit the data, to make sure no employee on the form has
more than 100% total process activity for that pay period?

****************************************

So, basically, for every "process" I need to create a form; which as I see
it will make it very hard to maintain (well, maybe not hard, but
time-intensive). I guess that's not a problem, as each form can populate
the same table; just seems like a dropdown to select the process would be a
lot less work--but I guess he feels that would be over their heads (and, for
the record, I don't necessarily disagree with that assessment).

Other problems I see?.....how do I make the form go to the latest pay period
upon opening it? Never really done anything like that? Kind of
like......if it's July 27th when I open a particular process form, it would
automatically populate the field with Pay Period 14 (or whatever pay period
the date falls under).

Also, not sure about "submitting" the data, or checking it for accuracy
before doing that.

At any rate, I would appreciate any guidance/suggestions anyone might
provide....thanks in advance,
Bob

 

[Larry Daugherty]

Hi Bob,

It sounds like you've been asked to do something you're not quite ready to
do. You're right about getting to the data design early, it's the thing to
do. However you can't understand the data design until you understand the
process you're trying to implement. There is some implied problem that your
boss wants solved. You are the one to solve it.

Every boss or manager always wants a tool that's idiot proof and easy to
use. Those are goals to set a direction but they're never fully achieved in
a working system. Do the best you can and Press On!

Jeff pointed out that your direct and implied questions aren't likely to be
addressed nor fully answered. They can't be. The only direct questions
you've asked are meaningless because they have no context. For them to have
meaning you have to have the data environment defined already. The answers
to your questions are generally this: You can do anything you want to do
but before you can use or retrieve data you must first have it in your
application in such a way that it serves your purpose. It kind of goes
circular, doesn't it?

If you are going to develop useful applications, then I suggest that you
read a book or two about Access. Google the access newsgroups for "books"
or simply use the Find function in your newsgroup reader. You'll get a
pretty good list of books. Lurk the newsgroups. Tablesdesign is a good
one. There are others about specific elements of Access. I think you'd
benefit from lurking in microsoft.public.access.gettingstarted, too. Be
patient with yourself. The initial learning curve in Access is fairly steep

Good luck,

 

[BobV]

Thanks Larry,

Actually I've done quite a few of them .....normally, however, it's
something where I know the answers to the questions that will get me to a
useful product (most of them are for things I manage, so I know exactly what
I'm looking for). I'm just having a tough time with this one, I guess,
because I'm half-way being told "how" it's to be done, and the degree of
idiot-proof is quite overpowering.....normally the users I design them for
can at least comprehend a drop-down combo box to make a selection......I
know, I know.....this one's for managers, so I can't expect miracles. I
guess I'll just start by creating it the way "I" think it should be done,
and then make changes if it's too tough for them to use.....unbelievable.

I'm no code-master by any stretch of the imagination, but I've been able to
get through enough of it to make it quite useful--I do actually have several
books already that I use, and the various Access newgroups have also been
invaluable. Guess that's why I was trying this time, but you're both
right.....I need to at least have the groundwork laid before trying for
obtain help. What the heck....at least it was probably good for a few
laughs around the world.

Sorry, and thanks.....
Bob

 

[rpw]

Hi BobV,

I can't give you specifics, but I do have a couple of ideas for some of the 'problems' you've stated.

Add a Yes/No field to the table and make two versions of the form - one for submitting and one for review. The Row Source for the one for review could filter on the yes/no field so that only the 'no' records (not approved) show up in the list.

Not knowing a better way, I'd have a pay period table with a start and end date fields. I'd set up the row source query for the form to today's date on greater than start date and not greater than end date. Again, I don't know quite exactly how to write the expression to get the exact results, but I'm sure that if I posted this question to the Query group here, I'd get a good answer.

It seems that allowing the user to enter only the percentage into the form is not the best idea then - how about actual hours spent, projected total hours, estimated percentage of work done, and a job done yes/no field. Then, you can calculate a percentage based on actual hours when it's done, and on projected hours when it's not done.

Again, sorry that I can't provide details on how - I'm just offering suggestions on what I think can be done.

hth