ACCESS 2007: need help in database breakdown and table creation

  • Thread starter Thread starter Bob76
  • Start date Start date
B

Bob76

Am new at Access. Been studying books on Access 2007 and tables I've created
are not efficient. Have feeling am going about it the wrong way.
Currently have database in Excel 07 spreadsheet w/ these headings:
Empl#, LastName, FirstName, Date Hired, Project Leader (PL), Jobsite,
Apprentice Level, Previous Rate ($), Current Rate ($), Date of Raise,
Apprentice School Start Date, End Date, Comments.
This is a list of construction workers in diff jobsites. Each PL handles 2
to 3 jobsites. Some workers move from 1 site to another (therefore, at times
from 1 PL to another). In Excel, have 1 worksheet as Master List (includes
all workers) and broken down into separate worksheets for each PL. It was
getting tedious in Excel moving some workers from one PL's list to another &
also updating any changes of an employee file in Master List as well as the
PL list for that employee. Reports are printed for each PL to study at their
meeting each Monday.
Need advice on how to breakdown this spreadsheet into smaller tables and
need to create a 1 to 1 relationship so that any change in an employee file
is automatically changed in other files where he/she is located. Empl# is
unique.
Thank you.... Bob76
 
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
 
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
 
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
 
the job site table is designed to store what employees are in what job
site so you can do a quick seach so see this information

the pay table allows you too see over time

ie

tblpay
payid employeeid PreviousRate CurrentRate DateofRaise
1 1 50000 55000 1/1/2008
2 1 55000 58000 1/5/2008

does this make sence

o right i seem to have forgotten a table you need a work table which
will have the employeeid and the jobsiteid so you can see the list of
employees on a site.

tblemployee
Employeeid LastName FirstName DateHired ApprenticeLevel
ApprenticeSchoolStartDate ApprenticeSchoolEndDate Comments
1 bob jones 1/1/2007
1 1/5/2007
6/9/2007 he is fat
2 frank smith
1/8/2007

tbljobsite
jobsiteid Jobsitename
1 that big job
2 that other job

tblwork
employeeid jobsiteid projectleader
1 1
2 1 yes
2 1 yes


i hope that helps

regards
Kelvan
 
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
 
Comments in line.


Lord Kelvan said:
the job site table is designed to store what employees are in what job
site so you can do a quick seach so see this information

the pay table allows you too see over time

ie

tblpay
payid employeeid PreviousRate CurrentRate DateofRaise
1 1 50000 55000 1/1/2008
2 1 55000 58000 1/5/2008

does this make sence
Click to expand...

No. The below is better and is Relational. PreviousRate and
CurrentRate cause someone to enter redundant data.

tblPay
PayID EmployeeID Rate EffectiveDate
1 1 55000 1/1/2008
2 1 58000 1/5/2008

Queries to extract required information are easy to create. The
latest date is always the last rate. You can create a Report based on
a query that will list every rate and effective date in date order
for each employee.
o right i seem to have forgotten a table you need a work table which
will have the employeeid and the jobsiteid so you can see the list of
employees on a site.

tblemployee
Employeeid LastName FirstName DateHired ApprenticeLevel
ApprenticeSchoolStartDate ApprenticeSchoolEndDate Comments
1 bob jones 1/1/2007
1 1/5/2007
6/9/2007 he is fat
2 frank smith
1/8/2007
Click to expand...

I would only include the apprentice School info in the tblEmployee
record if every single employee must have that school and if there
will never be any other training noted in your application. If there
will be other training for some or all employees then I'd have a
tblTraining. tblTraining would be in a many-to-many relationship with
tblEmployee. tblTraining would list every course of training that an
employee might take. The junction table, tblEmployeeTraining would
list every instance of an employee and a course of training.
tbljobsite
jobsiteid Jobsitename
1 that big job
2 that other job

tblwork
employeeid jobsiteid projectleader
1 1
2 1 yes
2 1 yes
Click to expand...

I have trouble with the ProjectLeader issue. Is it a permanent Rank
that once attained stays with that person? If so and thee are other
Ranks to consider; President, Vice President, other officers, Clerk of
the Works, etc. then there should be a tblRank that lists every
possible rank. It would be a lookup table Is it simply situational:
this person is the Project leader on this particular job but on other
jobs s/he is not???
i hope that helps

regards
Kelvan
Click to expand...

HTH
 
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
 
Hi Larry,

Thanks a lot for your advice. It has cleared up a lot of misconceptions in
my mind. I concentrated too much on designing my Access database based on
what I have in Excel that I've totally lost focus on what Access can do w/
regards to providing meaningful infos in a structured format.

I've also glanced thru the mvps site and it has loads of infos which I'll
take the time to dissect these few weeks while I design my Access database
again from scratch. Will get back to you for advice when I get stuck.

Thanks again, Bob
 
Back
Top