Re: ACCESS 2007: need help in database breakdown and table creatio

Hi Bob,

There is a lot to learn about developing applications and beyond that
there is a lot to learn about Relational Database Management Systems
and still more to learn about Access. Not for the faint of heart but
well worth the trip.

The hardest thing for people making the transition from managing data
with Excel to doing it with Access is that you must unlearn a lot of
what works so well for you in Excel in order to learn how to do it
with Access.

Properly designed and implemented there will be no need to remove
historical data. It will become useful to you in the future. Don't
ask anyone's permission; just do it that way. When your design is
complete and rolled out *no one should ever see the tables*. They
should see and interact with the Forms you design that are based on
Queries on those Tables. You will create Reports based on Queries on
Tables. People will see the data organized as you have designed the
report.

Microsoft and the Access development team are working hard to push
Access down to the level that unsophisticated users can do useful
things with it. In the process of doing that they've created and
implemented several dysfunctional traps. Those nifty things they do
will allow a novice Access user to quickly create some applications
that seem OK to the unsophisticated eye and judgement. The
applications produced are expensive or impossible to maintain or
enhance if you use those dysfunctional shortcuts. Try to hold yourself
to a higher standard. Poke around at www.mvps.org/access for lots of
useful insights and guidance.

Your boss is wrong in what s/he thinks is the right way to design your
schema (tables and relationships). What s/he said to do amounts to
coding data in table names. That is not to be done. Tables are named
for entities in play in your application. There will be one and only
one Table for any given entity type. By implication, every entity of
that type belongs in that one table.

Do not accept micromanagement and technical direction from anyone who
knows less about the technology in question that you do. A crude
analogy to what you've been told to do follows: You will build an
automobile tracking system for a car dealership. Your boss would have
tblRedCars, tblBlueCars, tblWhiteCars, etc. From what's given just
above you know that there should be a single tblCars and that every
car would be in that table. Color would be a single field in the
record for a car. You can then run a query to retrieve the records
for cars of just one color if you choose.

There should not be a tblComments unless comments are a really big
issue to be tracked separately. Just about every table should have a
notes field in it. If the notes for a single record can exceed 255
characters then the datatype should be Memo.

You can use a Make Table Query to create a table.

Post back with questions as they arise.

HTH
--
-Larry-
--

"Bob76" <Bob76@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:998DB255-07E0-4C8B-8ED0-D8640EC7055B@xxxxxxxxxxxxxxxx
Hi Larry & Kelvan,
Thanks so much for your input and suggestions.. Larry, you're right
about
the construction business being cyclical. I do have an Excel file w/
the
terminated employees, moved from the main Empl file as soon as
he/she is laid
off/quit. I've had to move it back when re-hired. Also have an
Excel file of
Employee Evaluations (done 2x a yr by the PL's) w/c my boss falls
back on
when time comes to lay off or rehire people.
Will have to start from scratch re: tables... What I've created for
the past
month are not working out..very frustrating, esp to do w/ creating
relationships. Then I created this latest one:
1: TblEmployees w/ Empl#, LastName, FirstName, DateHired,
ProjLeader,
Jobsite.
2: Tbl PayRates w/ Empl#, PrevPay, CurrentPay, Date of Raise, Level,
AppSchool Start, AppSchool End.
3: TblComments w/ Empl#, Comments.
4: TblEvaluations: Empl#, Date, Evaluation, Jobsite, Evaluated By:

My boss wants subdatasheets so that when bring up an employee, can
cascade
to diff subdatasheets for the pay, comments, evaluations. But then
he wants
separate tables for each ProjLeader w/ his own employees/jobsites.
So had 3
TBLEmployees for each PL,but the Tables Payrates, Comments and Eval
include
all employees. I created relationships and this is where I got
stuck.
Is there a way I can create a table from a query? I was thinking of
creating queries for each PL as criteria, so that when a worker
moves from 1
PL to another, it will be automatic move in the their respective
tables Hope
this makes sense.
I will try out your suggestions and see where it takes me. Hope
it's okay
to bombard you guys w/ questions again when I encounter problems
during this
process. Thanks

Totally confused, BOB76

"Larry Daugherty" wrote:

The JobSite table will be a lookup table. It is incorrect as
given.
The first two fields are correct. The second two fields don't
belong
in that table. I would add a couple of fields:
JobSiteDescription,
text and JobSiteNotes, memo.

The pay table should only have fields for PayRate and
PayEffectiveDate
and PayNotes. That will allow you to track that person's rate of
pay
for every date from initial hire to the present or to final
retirement
or other termination.

In tblEmployee there should be a field for DateSeparated and a
field
for ReasonForSeparation. There could be a lookup table:
tblReasonForSeparation that might have values like: Lay off,
Leave of
Absence, Termination for cause, etc.

Also, since the construction trades tend to be highly cyclical you
might want to allow for the possibility of several hire and
separation
dates. For example; a person might be laid off due to lack of
work.
During the hiatus that person finds employment with another
organization. When your organization might next desire his
services
he isn't available. At some later date that person might be
available
and hired to meet a need.

tblEmployee records should have information only about the
employee,
nothing else. If you might track training events and skills and
skill
levels then other, related tables must be used for that purpose.

For the next few years, don't worry about there being too much
data in
your application. You'll probably find that you won't fill the
allowable space in an Access BackEnd (2 Gigabytes) in this
application
during your career unless you're storing images. With proper
design
you'll be able to retain old data for a long time. Believe it or
not,
as your application becomes more useful to your users they'll be
asking you for historical information.

Be patient with yourself while getting over the worst of the
first
major learning curves in learning Access.

Post back with specific technical issues; one per thread.

HTH
--
-Larry-
--

"Bob76" <Bob76@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2367538B-0DEC-462A-AED6-EFB4290D499F@xxxxxxxxxxxxxxxx
Thanks for your reply.. I don't quite get the need for the
Jobsite
table
though. I also don't know how the pay table can track pay over
time.
Please
explain. thanks

"Lord Kelvan" wrote:

i would have something like

tblemployee
Employeeid,autonum
LastName,text
FirstName,text
DateHired,date/time
ApprenticeLevel,text or number depending on format of field
PreviousRate,currency
CurrentRate,currency
DateofRaise,date/time
ApprenticeSchoolStartDate,date/time
ApprenticeSchoolEndDate,date/time
Comments,memo or text depending on length

tbljobsite
jobsiteid,autonum
Jobsitename,text
employeeid,number
ProjectLeader,yes/no

if you want to track the pay increases over time not juts the
last
pay
increase you need another table

tblpay
payid,autonum
employeeid,number
PreviousRate,currency
CurrentRate,currency
DateofRaise,date/time

so you would stick everyone in the employee table including
project
leaders i added the ProjectLeader,yes/no field to job site but
this is
only useful if an employee can be both a project leader and a
normal
employee on another site. if this is not the case then the
projectleader,yes/no would be added to the employee table
instead.

i hope this makes sence

Regards
Kelvan






.