Where to start?

[FullyFamous]

We are currently using an Excel spreadsheet to collect data about employees entering daily production totals. However, the spreadsheet is not set-up to "crunch" the daily totals. Each person fills-out a sheet each day, prints it out and gives to the supervisor, who manually (ugggh!) tallies each one and enters a total on a different spreadsheet. I'd like to create a database that will allow us to collect daily data from each operator without so many manual steps.

I've taken a few "basic" Access classes, so I understand the basics of databases, but I always have trouble getting anything setup from the beginning.

Here's what I want to do: I want to construct a database called Production Report where each data entry person -"operator"- will enter how many applications he/she has entered each day, from each office that has submitted applications. The applications submitted are split into two different catagories: applications with photos, and applications w/o photos ("dups"). We currently have 14 operators entering applications each day. Each operator has a unique operator ID, 24D, 38A, etc.

So I think the basics are:

-Operator ID (24D)
-Operator name (Claudia)
-Applications entered for (station number 725)


-Photo applications entered [for that station 725] (125)
-Non-photo applications entered [for that station 725] (66)

I also have to keep track of each operator's time spent on various tasks throughout the work day:

-Application entry
-Fac Records
-Prep time
-Scanning
-Reconciling records
-RX's
-Misc
-Exercise
-Clerical
-Vacation/or Sick time

I will also need to determine:

- How many total applications (photo & dup) were entered by each operator each day/week/month/year.
-How many applications per hour were entered by each operator.
-Breakdown of time spent on those tasks each day.

How many tables should I have for all this?

I also need to make the data entry as simple as possible for each operator, as they are very computer adverse.

I hope I've explained well enough. Thanks in advance for the help.

[jzwp11]

The key to any successful relational database application is starting with a sound table structure (tables and the relationships between the tables), so it is well worth the effort spent to set them up correctly. The forms, queries, reports etc. can and should wait.


The first step to setting up a proper structure is to analyze your current process which you have started to do based on the information in your post. You asked how many tables you will need. The answer is that it depends on your application. The number of tables will vary depending on the analysis of your process.


It also will be good to get an understanding of database normalization. This site has some basic information to get started.


With your analysis of the process, you also have to identify relationships.

You said that an operator has to deal with 2types of applications, but those applications are somehow related to a station. So is an operator responsible for a station which then produces applications of which there are 2 types? Can an operator operate many stations? Can a station be operated by many operators?

Can you show an example of the data that is currently being collected?


In terms of keeping track of time, do you want your employees to record the time interval such as from 1:30pm to 2:30 pm or do you just want them to enter the time spent on an activity i.e. 1.0 hour

[FullyFamous]

This is what part of the current spreadsheet looks like. Each operator will enter the station, julian date, how many photo and duplicate applications were entered. But the totals currently are not tabulated by the spreadsheet; each operator locally on his/her PC enters these numbers and then prints a copy which is given to the supervisor. Who then enters the totals onto yet another workbook. (Ugh).

Application Entry RecordStationJulianPhotoDupTotal12332512525150124325562379

On a different part of the sheet, the operator will enter their production times for that day:
Production Time- HoursActivityTimeDL AppsFac RecPrepScanReconcileRx'smisc appsExerciseClericalVac/SikTotal0

The day's total will come out to 7.5 hours. But this is also not tabulated anywhere; it is again manually entered on another workbook by the supervisor.

My initial thought, was that I need to have separate tables for the basic information:

Table 1 (Employee Information)
Employee number
First Name
Last Name

Table 2 (Production Time)
DL Apps
Fac Rec
Prep
Scan
Reconcile
RX
Misc
Exercise
Clerical
Vac/Sik

Table 3 (entries made)
Station
Julian
Photo
Dups

Am I on the right track?

[jzwp11]

Am I on the right track?

Not to be disrespectful, but no you are not on the right track. Working with relational databases requires a different view of the data & a different way of thinking relative to a spreadsheet. I'll try to explain with some example tables.

Your employee table is OK, but I would recommend an autonumber primary key field in every table. This allows Access to give each record a unique identifying number. Also it is best not to have spaces or special characters in your table or field names. I usually use prefixes in front of my field names to tell me the data type (pk=primary key, autonumber field, txt=a text field, dte=date, log=logical or yes/no field, long=long number field, fk=foreign key field which is a long number field, sp=single precision number etc.)

tblEmployee
-pkEmployeeID primary key, autonumber field
-txtFName text field to hold the employee's first name
-txtLName text field to hold the employee's last name
-txtEmployeeNumber (if this is a social security number then it should be a text field)


Your table 2 looks to be a list of activities an employee can do during their work time. In a relational database, you would not have these activities as fields in a table but rather as records in a table. So, the table would look simply like this. Each of the activities you list would be records in the following table.

tblActivity
-pkActivityID primary key, autonumber
-txtActivity

An employee can conduct many activities which is a one-to-many relationship. But an activity can be performed by many employees, so we have another one-to-many relationship. When you have 2 one-to-many relationships between the same two tables, you need what is called a junction table which is what we will use to relate the employee with the activities and the time spent on each as shown in the following table:

tblEmployeeActivities
-pkEmpActID primary key, autonumber
-fkEmployeeID foreign key to tblEmployee
-fkActivityID foreign key to tblActivity
-dteEmpAct (date of the activity)
-spTimeSpent (minutes or hours, you'll have to choose)

I'm not sure I completely understand the following:

Application Entry RecordStationJulianPhotoDupTotal12332512525150124325562379

If an employee can conduct many applications (one-to-many) and and application can be done by many employees (one-to-many) then we need another junction table. First though, we need to set up the table to hold the applications

tblApplications
-pkAppID primary key, autonumber
-txtApplication



tblEmployeeApplications
-pkEmpAppID primary key, autonumber
-fkEmployeeID foreign key to tblEmployee
-fkAppID foreign key to tblApplications

Since an application can produce more than one type of output (2 in your case), we have a one-to-many relationship

tblEmployeeApplicationTypes
-pkEmpAppTypeID primary key, autonumber
-fkEmpAppID foreign key to tblEmployeeApplications
-txtType
-longOutputValue

In the above table, I have the txtType field to identify the type of output (photo or dups). You technically can have a table to hold the various types which will give you future flexibility if the need arises and then just reference the key value. If you wanted to go that route, it would look like this:

tblTypes (2 records for now: photo and dups)
-pkTypeID primary key, autonumber
-txtType

tblEmployeeApplicationTypes
-pkEmpAppTypeID primary key, autonumber
-fkEmpAppID foreign key to tblEmployeeApplications
-fkTypeID foreign key to tblTypes
-longOutputValue

[FullyFamous]

Sorry, the " Application Entry RecordStationJulianPhotoDupTotal12332512525150124325562379 " was pasted from the spreadsheet. It was supposed to show the 5 columns on the current sheet:

Station Julian Photos Dups Total
123 323 15 15 30
125 323 16 25 41

This shows that the operator entered 30 total applications (15 photo, 15 dups) for station 123 for julian date 323. The operator continues entering that same for each station/julian.

You explanation is starting to make sense. (Yay!) So I really need more tables to hold the many diiffrent types of inputs that are possible.

[jzwp11]

Actually, I think if we just add a field to capture the Julian date, then I think that should take care of it.

tblEmployeeApplications
-pkEmpAppID primary key, autonumber
-fkEmployeeID foreign key to tblEmployee
-fkAppID foreign key to tblApplications
-Juliandatefield

[FullyFamous]

Thanks for your help. I think (?) I have plenty to get me started.

[jzwp11]

You're welcome; good luck with your project.