How many tables do I need

[chriswrcg]

I am very new to Access. I mainly work with excel and am very familiar with vba coding. I have a spreadsheet that I built and maintain that tracks the employees in a warehouse and provides them with an overall performance score at the end of the day. I want to try and build the same thing in access but am just starting out learning and don't know how to translate what I have in excel into access.

The information I gather can be broken down into three parts. But I don't know if I should create three tables or one large one. The three blocks of information are

Associate
name (first and last)
department
shift
clock in
clock out

Activities
task codes
task start time
task stop time
movements made during task

Exceptions
what task the exception happened in
exception duration


exception code

There is more but you get the idea. Each day an associate will be entered into the database and create a record of that associates day. If I have three tables how do I make sure that the task I entered for the associate is applied to that associates record and not someone else? Same with the exceptions table. Will I have to include the associates name in each table? I really don't want to have to enter a field of information more than once so should I just have one very large table that holds all the fields I will need?
Any advice?

[CJ_London]

Looks like your existing tables need to be broken down further. google 'normalisation' to understand how tables and relationships are created. Also google 'junction tables', 'primary key/foreign key which will explain how to associate one record with another.

Be aware that databases work at 90 degrees to excel - simplistically excel stores data 'short and wide', databases store 'tall and narrow'. Excel combines data storage and presentation in a single view. Databases store data in tables and front ends (such as access) use queries forms and reports for presentation and interaction - the user never gets to interact directly with the tables. So much of your excel thinking on 'data layout' will probably not apply

[ssanfu]

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.


Work through the tutorials (won't take that long) at Rogers Access Library . This will help you a lot.


Once you have gone through the tutorials, here is some reading:

Normalization Terms and Concepts
Normalization


What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization?
What Is Normalization, Part II: Break it up.
What Is Normalization: Part III: Putting It Back Together
What is Normalization: Part IV: More Relationships
What Is Normalization: Part V: Many-to-Many Relationships


Entity-Relationship Diagramming
==============================
Entity-Relationship Diagramming: Part I
Entity-Relationship Diagramming: Part II
Entity-Relationship Diagramming:Part III
Entity-Relationship Diagramming: Part IV


The Normal Forms
=========================
The Normal Forms: Introduction
The Normal Forms: First Normal Form (1NF)
The Normal Forms: Second Normal Form (2NF)
The Normal Forms: Third Normal Form (3NF)
The Normal Forms: In a Nutshell





AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

Autonumbers--What they are NOT and What They Are
Microsoft Access Tables: Primary Key Tips and Techniques


Some suggestions:
-------------------
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.

Naming Conventions
What not to use in names




I still go back and read these links. (its a Me.Refresh memory thing )

Good luck with your project. Post back when you have questions..........