Tracking alloted time and jobs

[Jan Il]

Hi all - Access 2002 - WinME

Our company will be begin the 'Start-up' operations of the latest extension
of our Light Rail system in March next year. We will need to track all
related costs and man-hours assigned to the various start-up projects. The
overall information to be tracked is the following;

JobDate
Manhours
EmployeeName
EmployeeID
HourlyRate
JobLocation
JobType
TotalCosts

I now have a table in the new database set up for the JobType and
JobLocation, and one that has the following:

JobDate
Manhours
HourlyRate

Each employee can work at more than one JobLocation, and perform more than
one JobType. The hourly rate will be the same for all, as the will be
contracted. There can also be more than one employee at the same
JobLocation performing the same JobType on the same date. However, the
information must be recorded for each employee, as their work schedules and
shift times may vary if overtime is required at some point.

I already have existing tables to cover the EmployeeID and EmployeeName
which can be imported, but, I am not sure which of the other information
above should be in one or more tables. The JobLocations recorded will not be
a part of our active system information until the extension is officially
turned over to our company for operation, and the 'official' location
identification may change entirely at that time. Thus, the locations will
be a recording reference only and I can not use our existing Location table,
nor JobType, as the various work performed during acceptance evaluations and
inspections may vary in actual work requirements that we do not normally
recorded individually.

This will be a separate database for use for this specific purpose, as when
the extension is completed and in operation, the information will no longer
be required, except for a backup record. That is why I don't wish to include
this in the current active database.

This is my first go at something such as this, so, before I get too deep
into putting the various queries and forms together, I would truly
appreciate any corrections or suggestions regarding the table designs that
should be made at this time. Sorry this is rather long, but, wanted to
provide as much information as I could to start.

Best regards,
Jan

 

[Henry]

First, the DB design you describe will be a challenge in
DB design, but you can do it. The usual approach to DB
design is to first determine what you want out of the DB
in the way of information and reports. After you define
what you want then you define what data elements you need
to store in the DB. Now you arrange the data elements
into tables and then normalize the tables. Without
normalization at least to level 3 your DB will be
difficult to manage and may not even work. Now you have
the data stored in tables and relationships defined.
Next is to build the interface for data input and data
output. There is a set of books I highly recommend that
will take you through what I just described and more.
Author is Ken Getz, "Access 2000 Developer's Handbook"
published by SYBEX. Available at Amazon.com at a
discount price. Books (2 volume set) are a reference,
tutorial and include a CD with all the examples and more.

Wish you success,
Cheers,
Henry

 

[XXXXXX]

Ever thought of using MS Project instead? It was designed to do exactly
what you're talking about!

SWF

 

[Jan Il]

Hi!

Ever thought of using MS Project instead? It was designed to do exactly
what you're talking about!

Not really..as this is not a program we have at work, at least not that I am
aware of in our department. I can import some of the tables already
established in our current Access database into the new one that can be
used, such as EmployeeID and EmployeeName. Additionally, right now I don't
have time to invest in learning a new program for this project.

Thank you very much for your time to assist, and the information on this
program. I will keep it in my reference library. Knowledge is a good thing
to keep on hand. <g>

Jan

 

[Jan Il]

Hi Henry!

First, the DB design you describe will be a challenge in
DB design, but you can do it. The usual approach to DB
design is to first determine what you want out of the DB
in the way of information and reports. After you define
what you want then you define what data elements you need
to store in the DB. Now you arrange the data elements
into tables and then normalize the tables. Without
normalization at least to level 3 your DB will be
difficult to manage and may not even work. Now you have
the data stored in tables and relationships defined.
Next is to build the interface for data input and data
output. There is a set of books I highly recommend that
will take you through what I just described and more.
Author is Ken Getz, "Access 2000 Developer's Handbook"
published by SYBEX. Available at Amazon.com at a
discount price. Books (2 volume set) are a reference,
tutorial and include a CD with all the examples and more.

I did not realize it is that difficult to create a very basic database to do
this? Of course, to a novice, all things are rather difficult. Command
buttons still insist on fighting with me. <g> However, it did seem to be
straightforward and does not involve a lot of information. Sort of like
tracking daily labor costs, or so I thought.

I do have the Ken Getz "Access 2002 Developer's Handbook" published by
SYBEX, the set of 2 volumes and 2 CD's, and I have tried to implement my
understanding about the table setups. However, sometimes, someone from
another point of view, and/or more experienced can spot something I may not
have thought about, or overlooked. I have not yet set the PK's or
relationships, as it is still preliminary and I saw no point in going
through all that until I am sure what all is needed and what goes in each
table. I have found running preliminary concepts by the good folks here when
I'm unsure to be very helpful. There have been a few times when a good 2x4
has been needed to clear the confusion on my end. (vbg)

Thank you so much for your time and very helpful information. I truly
appreciate it and will add it to my resource library.

Best regards,
Jan

 

[Jeff Boyce]

Jan

The limited table/field description you offered looked like you may be
duplicating data across tables. If true, this is not a good thing. I agree
with Henry's suggestion that you need to do design and normalization before
you build. I would suggest turning off the computer and using paper/pencil.

If you haven't already, take a look at an MS KB article (#100139) on
normalization:

http://support.microsoft.com

For someone just starting out in Access, the temptation to "commit
spreadsheet" on Access can lead to table designs that cause nothing but
headaches when you start trying to query the data. If a spreadsheet is what
you need, use it! Why do you think Access, a relational database, is the
tool you need?

Good luck on your project!

Jeff Boyce
<Access MVP>

 

[Jan Il]

Hi Jeff,

Jan

The limited table/field description you offered looked like you may be
duplicating data across tables. If true, this is not a good thing. I agree
with Henry's suggestion that you need to do design and normalization before
you build. I would suggest turning off the computer and using paper/pencil.

If you haven't already, take a look at an MS KB article (#100139) on
normalization:

http://support.microsoft.com

For someone just starting out in Access, the temptation to "commit
spreadsheet" on Access can lead to table designs that cause nothing but
headaches when you start trying to query the data. If a spreadsheet is what
you need, use it! Why do you think Access, a relational database, is the
tool you need?

Actually, I started out in Excel, and found that while the spreadsheets were
ok, it did not provide the capabilities we needed and switched to Access.
Like many of you, I have had to be self-taught, and like most who come here,
I'm stumbling and struggling.

This is just my first time in trying to record this type of information in
this particular manner. I know what I want to do, and how the information
needs to be recorded and reported for accounting. I was just not sure if I
was getting all the right fields in the right tables. I have found at times,
especially when venturing into new waters, when I thought I needed several
tables, I only needed one. And, vise-versa. Or, I had a field in one table
that should have gone in another, as I had overlooked a point in how the
information should be addressed. So, when there are times when I'm not
sure, I ask. And..who better to ask, than the experts? ;-)

It doesn't hurt as much to check before you commit, as it does at the end of
the line when you find yourself facing a block wall. BTDT <g>

Thank you for your suggestions and additional information. I really do
appreciate it. I'll go over the KB article again to be sure I haven't
tripped up along the way.

Best regards,
Jan

 

[Jeff Boyce]

Jan

We're all just learners here, I agree!

It's a little nit-picky, but I'll suggest that while you might have some
firm ideas about "how the information needs to be ... reported for
accounting", you may want to disabuse yourself of any preconceived notions
about how it "needs to be recorded". You may know which data elements need
to be preserved, but even this can get you into trouble -- for example, if
part of your accounting is monthly totals, you need to store totals, right
(danger! danger!! trick question!!!)?

My earlier suggestion about stepping back to design and normalization is to
work through what MUST be stored and what doesn't have to be, plus a
structure for storing that will make your queries much simpler. For
example, imagine trying to get the average monthly revenue from a each store
when your table structure includes a different table for each store and a
column for each month -- this design is NOT a good idea.

Good luck with your project, and post back whenever

Happy holidays

Jeff Boyce
<Access MVP>

 

[Jan Il]

Hi Jeff!

Jan

We're all just learners here, I agree!

It's a little nit-picky, but I'll suggest that while you might have some
firm ideas about "how the information needs to be ... reported for
accounting", you may want to disabuse yourself of any preconceived notions
about how it "needs to be recorded". You may know which data elements need
to be preserved, but even this can get you into trouble -- for example, if
part of your accounting is monthly totals, you need to store totals, right
(danger! danger!! trick question!!!)?

My earlier suggestion about stepping back to design and normalization is to
work through what MUST be stored and what doesn't have to be, plus a
structure for storing that will make your queries much simpler. For
example, imagine trying to get the average monthly revenue from a each store
when your table structure includes a different table for each store and a
column for each month -- this design is NOT a good idea.

Oh yes, one of the first things embedded in my head here on the Access
newsgroups when I first started with Access was NOT to store calculations,
but call them when needed using a query. And, that a cat can have six toes.
<g>

Unnecessary tables is what I am trying to avoid. Yet, it can sometimes be a
bit difficult to know where the line is according to the rules. These are
the areas that may need clarification, at least for me.

Good luck with your project, and post back whenever

Thank you so much for all your time and great advice. I'm sometimes a bit
wobbly off the tee, but, I can putt pretty good. Now and then I even hit the
cup!

Happy holidays

Wishing you and yours the same,
Jan