calculations in Access

[Guest]

I am setting up a database for training. The fields are:

Company Name
First Name
Nickname
Middle Initial
Last Name
Jr./Sr.
Employee Number
Employee's Title
Supervisor Name
Years in Company
Reporting Location
State
Region
Course Name
Date Course Taken

The table and form are prepared. I can SORT reports 6 ways from Sunday;
however, what is required is CALCULATIONS. I have no way how to do that in
Access. Various reports required will be:

Training results by percentage by total number of employees (Region)
Training results by percentage by total number of employees (State)
Training results by percentage by total number of employees (Location)
Training results by percentage by total number of employees (Supervisor)
Training results by employee
Historical data by employee (hinges on Date Course Taken)

All this is being done in Excel currently. It was thought that a database
in Access would make things a lot easier (debatable!).

Any help you can provide will be greatly appreciated

 

[fredg]

I am setting up a database for training. The fields are:

Company Name
First Name
Nickname
Middle Initial
Last Name
Jr./Sr.
Employee Number
Employee's Title
Supervisor Name
Years in Company
Reporting Location
State
Region
Course Name
Date Course Taken

The table and form are prepared. I can SORT reports 6 ways from Sunday;
however, what is required is CALCULATIONS. I have no way how to do that in
Access. Various reports required will be:

Training results by percentage by total number of employees (Region)
Training results by percentage by total number of employees (State)
Training results by percentage by total number of employees (Location)
Training results by percentage by total number of employees (Supervisor)
Training results by employee
Historical data by employee (hinges on Date Course Taken)

All this is being done in Excel currently. It was thought that a database
in Access would make things a lot easier (debatable!).

And of course you were an expert bicyclist the first time you got on a
bike, and when you opened your first Excel spreadsheet you knew all
there is to know about using it! Correct?

A good book on Access would be of benefit. Also, there is more than
enough information in these newsgroups for you to get started. Search
for keywords such as "calculate", "expression", "query", etc. If you
then have a specific question, feel free to ask.

By the way, if all of those fields you mention are in the same table,
you have not correctly set up your database structure.
You should have one table for Company, another table for Employees,
another for State and/or Region, another for Courses, etc.
The "Years in company" field is sure to be incorrect at least once a
year. Try [DateHired] instead. Then you can calculate (after you learn
how) the years in company whenever you need that data, and it will
always be correct as long as that employee is still at that company.

 

[Roger Carlson]

Access is not a spreadsheet and as long as you continue to treat it like
one, you will have problems. As far as I can see, you need at least 6
transaction tables plus assorted reference tables.

Before you proceed, I suggest you do a little research on relational
database design. "Database Design for Mere Mortals" by Michael Hernandez is
a good start.

On my website (www.rogersaccesslibrary.com), I have a Tutorial section
(http://www.rogersaccesslibrary.com/TutorialsDesign.html) which use the
principles from this book. I also have a small Access database sample
called "TrainingRegistration.mdb"
(http://www.rogersaccesslibrary.com/download3.asp?SampleName=TrainingRegistr
ation.mdb) which illustrates how I would design such a database application.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tom Lake]

I am setting up a database for training. The fields are:

Company Name
First Name
Nickname
Middle Initial
Last Name
Jr./Sr.
Employee Number
Employee's Title
Supervisor Name
Years in Company
Reporting Location
State
Region
Course Name
Date Course Taken

The table and form are prepared

You really should have TWO tables if each employee can take more than one
course.
The first table has all the info in the present table up to and including
Region but NOT Course Name or Date Course Taken and uses the Employee Number
as a Primary Key (no duplicates). The second table has Employee Number (NOT
a Primary Key), Course name and Date Course Taken. The tables have a
One-to-Many relationship with Table One being the One side, Table Two being
the Many side and Employee Number being the linking field. There will be
zero, one or more records in Table Two for each record in Table One.
Also, instead of "Years in Company", use "Date of Hire" so you don't have to
change all the records each year. You can calculate Years in Company on the
fly.

I can SORT reports 6 ways from Sunday;
however, what is required is CALCULATIONS. I have no way how to do that
in
Access. Various reports required will be:

Training results by percentage by total number of employees (Region)

What do you mean by "Training Results"? I see no field in the table that
gives results. If you can specify the definition of "Training Results", is
sould be easy to get your statistics.

Training results by percentage by total number of employees (State)

Tom Lake
Tom Lake