Is my table to big? Lots of data fields...

I only have one table and I read so often that its better to have multiple tables. For what I have, it seems to work fine.

Will this cause problems down the road?

Thank you once again

John D

For what I have, it seems to work fine

Click to expand...

If it's just a simple tool for home purposes, fine.
However, if you (intend to) use your applic in a professional environment in a "professional" way, then I suggest you do some serious reading on topics such as "relational databases" and "normalization".

You can also run a search on the forums on these topics, loads of info / advice / links to be found.

RV

It is for my place of employment / department. There has been nothing implemented with the exception of a spreadsheet from Excel and ironically I have the most experience dealing in Access (which is not saying a heck of a lot) as well as are Tech Team is unavailable to create a system to make my life easier.

I have over 3,000 records that need to go into this (there is an old Access DB that we use, but the programmer is no longer with us) and I need to create something in the aid of my Annual Report that is produced...well, annually

Yes it needs to be professional and yes others need to use it, view it, edit it, and other odds and ends.

Ok, means that you really need to get familiar with topics such as "relational databases" and "normalization".
Forget 'bout Excel cause Access is in concept totally, and I do mean, totally different.

Have a look at sample databases you can find here to get a basic idea what your approach would have to be like.
A good start is to put your daily processes on paper, group data that seems to identify a specific occurence (think in questions, for example, how do you uniquely identify a person, answer that answer and translate the answer into a table).
Your answers should result in a couple of tables, which are or are not related to each other, having a specific "cardinality".
For example, if you would be dealing with Sales, amongst others yo'd normally have an Order table and an Order Line table.
These tables are related as each Order will come with 1 or more Order Lines.
You'd therefore would built a relationship between these two tables representing that one Order can have multiple Order Lines.

You need to define your table structure yourself.
If along the road you ran into issues, ask, but remember, you need to do the "building bit" yourself

RV

I want to thank you for putting it bluntly RV. I have been learning mostly off of Access Cookbook (text book) and oddly enough it does not discuss tables. I skipped the process entirely on creating a Relational Table(s). Looking at my Access for Dummies book - it appears there is information on how to do this.

Since I basically have the data fields I need, I will do as you say and start from scratch utilizing these fields.

Does this mean I have to re-create my forms and reports?

Thanks again.

I want to thank you for putting it bluntly RV

Click to expand...

No thanks

Does this mean I have to re-create my forms and reports?

Click to expand...

Basically, yes.
I don't think that that would take too much effort though.
What I would do is to introduce a couple of subforms.
Subforms merely represent that specific data refers to more then one occurence of other data.
In your case, you register a student's employee history.
A student can have had multiple Employee hence two tables, tblStudent and tblEmployee, having a one to many relationship.
Same applies to programs.
A student can enlist to more than one program.
So you'd have a table tblPrograms being included in a many to one relationship with tblStudent.

Also consider the use of list boxes, for instance one to reflect Gender.
Create a table tblGender coming with two values, M(ale) and F(emale).
Put a list box on your form based on a table tblGender so you can select the appropiate value.

Review the use of checkboxes.
Your current table is inconsistent, meaning you aren't using checkboxes where you normally would.
Example is your columns adultY and adultN.
In fact, these represent one column being of datatype Yes/No.

RV

Okay, I tried breaking up the data as much as I could, the result is 10 tables:

Tblstudents:
studentID
LastName
FirstName
SSN
Gender
DOB

TblStatus:
StartDate
ExitDate
Status
Program
R/D
AnnualReport

TblStudentContact:
Address
City
State
Zip
PoBox
Phone
AltPhone
Email

TblCurrentEmployment:
Title
Employer
address
city
state
zip
supervisor
telephone
related
notRelated

TblPreviousEmployment:
identical to current employment

TblUnavailEmployment:
GradUnavail
Death
Military
International
Prison
Medical
Other

TblUnavailCompletion:
TermUnavail
Death
Incarceration
MilitaryDeployment
Medical
Other

TblGraduate:
unrelatedoccupation
typeofwork
unemployed
unknown
contEducation
Institution
Verified

TblCriminal:
Misdemenaor
Felony
convictedAdultYes
ConvictedAdultNo
ExpungedYes
ExpungedNo
DomesticViolenceYes
DomesticViolenceNo
Notes

TblNotes:
notes


Okay, now they are grouped within there own category but there is nothing to link the tables together. Cant I just add the StudentID to each table and then create a relationship using that as the Primary Key for each?

A student can have had multiple Employee hence two tables, tblStudent and tblEmployee, having a one to many relationship.

Click to expand...

We only need to keep track of the Current Employer and we have many students who are employed with the same employer so wouldnt that cause problems because they all wouldnt be unique?

I don't know enough about your application to structure the tables properly but you do have several problems.
1. Breaking the columns into several tables doesn't normalize the structure. Normalization involves identifying entities and the attributes that discribe them. A student is an entity and a company is an entity. Each requires a separate table to hold all the single occurance attributes that describe them. A junction table is required to make the connection between a student and a company which describes the employement relationship.
2. The two "unavailable" tables should probably be combos where one item is chosen unless multiple items might apply. In that case a separate table is required but not in the form you have shown.
3. ALL tables should have unique primary keys. If you don't have any natural keys in your data, use an autonumber.
4. All tables in an application should be related to at least one other table. Rarely, you might have an isolated table but VERY rarely. I don't think you'll have any. Make sure that the relationships are defined and referential integrity is enforced.
5. Do some reading on normalization before you start building forms/reports. You'll save yourself a lot of work.

A couple of thoughts
Unless you are going to keep a history of addresses for students then Tblstudents: and TblStudentContact: can be combined.
If you want to keep a history then you will need to add an AddressID field to the TblStudentContact table and will need a third table to link them together with dates
TBLStudentsAddresses
studentID
AddressID
StartDate
EndDate

You would then find their current contact details by finding the most recent start date.

Similarly with TblCurrentEmployment: and TblPreviousEmployment: combine them as 1 table and add an ID field and create a reference table to link them back to the student. Again a date filter show who employed them, and when. If they have two jobs at once it will still work OK!

HTH

Peter

Thank you for the help, I have read the following links below (provided by you Pat in an earlier post) which have been extremely helpful in understanding Normalization.

(recommended links for the stupid)
http://www.fmsinc.com/tpapers/datanorm/index.html
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

I will work on the tables this week if I have time and will post what I have. From here on out, im starting from scratch on the build.

Thank you for the suggestions and tips!

John D

Pat,

Everything you said made sense with the exception of the junction table.

A junction table is required to make the connection between a student and a company which describes the employement relationship.

Click to expand...

I was under the impression that relating the two primary keys of the tables would be all it needs to relate the tables. As example:

the StudentID (Primary Key) under tblstudents would be linked to employerID (Primary Key) under tblEmployer.

The junction table kind of threw me a curve. Am I understanding this correctly? Or are you talking about a third table (the junction table) to link these together?

John D

The way you do the linking is with the junction table. Think about it this way:
A company may employ more than one student so the studentID cannot go in the company table.
A student may work for more than one company so the companyID cannot go in the student table.
The solution is a third table which holds the primary keys from the two tables plus any information specific to the relationship such as start date.

I haven't put on my professor hat for a while... time to shake the mold and mildew off of it.

Here's what you do. FIRST and FOREMOST study normalization. Here are the rules you need to remember.

1. Never mix apples and oranges in the same table.
2. You want deep, narrow tables in preference to shallow, wide tables - and then implement a key to optimize access to the deep tables.

OK, a design session is in order. You are building something for your business to track something. Conceptually, one way of looking at it is that you are making a data-flow model of whatever piece of your business you are modeling. (Whether or not you knew you were modeling, you really were!)

So identify the entities in the model. You will have several dichotomies in terms of these tables.

Dichotomy #1: Some tables have unlimited growth potential over time, representing the "revolving door" of entities coming into contact with your business and eventually leaving it. Other tables are relatively constant because you use them to define or look up something. In this dichotomy, you can tell the difference because except for the odd chance of a new category coming up, lookup/definition tables can be filled in almost immediately and will not change often over time. The key here is that for the "revolving door" tables you will need to include some thought on data retirement.

Dichotomy #2: Some tables are relatively obvious because they define very concrete entities. Like, a person or a class or a building or a business. Other tables are not so obvious. They are the overlap tables. Think of the old set theory discussion where you have overlapping circles. Each circle by itself describes a unique entity. But the part where the circles overlap is an overlap table.

Why is this important? Because of the apples and oranges rule. If you have a person and a business, you do not store the business name with the person (usually) and you do not store the person name with the business (usually). Instead, you create an overlap table that holds the key for the person (possibly a personal ID number, maybe autonumber) and a key for the business (again, a business ID number, maybe autonumber). This little overlap table then lists the persons associated with each business. In the business table, each business is listed once. In the person table, each person is listed once. In the overlap table, each person or business can be listed many times but the combination can only be listed once. This table therefore defines the RELATIONSHIP between the businesses and people.

Dichotomy #3. Some tables are pretty obviously single entities. Others can be blurred a bit. When this blurring occurs, you have incorrectly viewed the situation. Take students and classes. Say that George takes BIO 123 on MWF at 10 but Bill takes BIO 123 at 1 to 2:30 TTh. Are they taking the same class? No, but it blurs because something was left out. Those are the same class but different SESSIONS of the same class. So if you build a class table but offer more than one session of the same class, you have identified a table that requires layers. And persons don't take classes - they take sessions.

So now, here is my favorite exercise. Get a dry-erase board and some markers. Get some sticky-note pads. A case of them. (I'm suggesting supplies such that your business can use the leftovers.) Start listing tables you think you need. Draw some lines to represent relationships. When you have identified enumerable relationships, you have identified an opportunity for an overlap table. When you have identified things that subdivide, you have identified options for parent/child tables (like class/session above).

If at any point you are tempted to mix apples and oranges, you have another overlap table. If at any point you are tempted to add a field to hold another instance of the same / similar thing, you have identified a parent/child case.

Now take the sticky notes to "populate" these tables. Decide what kind of reports you want to see or what kind of forms you want to see. Try to figure out data sources to accomplish the reports and forms you want. If you cannot figure it out, you might have omitted something or blurred something. Because there is another rule you need to remember: No database gives you anything you didn't give it first. If you want to see student chirality (left or right handed), that information has to be in the DB (in a student table, most likely, since it is personal and unique to students.) If you want to know what classes a student is taking, you need to have that information in the DB some way (perhaps with a parent-child table for class/session and an overlap table for student/session - NOT student/class.)

The idea is that by doing some inspection first, you find out features you need to incorporate. You find out things you will need to add or rearrange. You will see very graphically what it takes to get the application you want. The best part of this is that once you have done this initial design and spent just a little time with it, your product will be so much better than it otherwise would have been. Nicklaus Wirth, the creator of the Pascal language, is credited with saying (something like) 90% of all program problems come from poor data design. I don't disagree with him.

Thank you DOC, PAT, RV and Bat .... Here is my test run at it. Attached is the table structure if you want to open it up (its just the tables) but I will try showing what I did here.

So far I just started with the Students information and the current and previous employment information.

tblStudents:
StudentID (Primary Key)
LastName
FirstName
PreviousLastName
DOB
SSN
Gender
POBox
Address
State
City
Zip
Phone
AltPhone
PrevPhone
PrevAltPhone
Email

tblCurrentEmployment:
CurrentEmployerID (Primary Key)
Employer
POBox
Address
City
State
Zip
Phone

tblPreviousEmployment:
PreviousEmployerID (primary key)
Employer
POBox
Address
City
State
Zip
Phone

---
Now, my 'junction' table is:

tblStudentEmployment:
StudentEmploymentID (Primary Key)
StudentID
PreviousEmployerID
CurrentEmployerID
HireDate
PreviousHireDate
PreviousExitDate
Related (data type is Yes/No)
PreviousRelated (data type is Yes/No)
Title
PreviousTitle
Supervisor
PreviousSupervisor

Now if I understand this right, what I did was make the the employer's its own entity from everything in the DB as well as the students info. I linked it by the junction table (tblStudentEmployment). Now if I need additional reference to the students employment information, I would link it with the studentemploymentID. And the actual linking of this would be done through relationships, Correct?

So is this what we call normalization?

John D

Okay, I know Im beating everyone before the critiquing of my first attempt at the table structure of just the students and employment info.

But I couldnt resist so I kept going with the project.

I have a total of 9 tables. Ultimately they come together with a table named tblOverallStatus with an additional two fields that relate to the other tables but are unique to each student.

If someone can look at what I have (I have yet to establish the relationships, but its pretty easy to identify how they relate) and tell me if I am on the right path. I have attached it as a zip. Its too much typing to put in a single reply and my head hurts from slamming it down against the keyboard.

Thank You

John D

The previous and current tables should be only a single table. What if one student is working for McDonalds currently but previously worked for Burger King and another student is currently working for Burger King. Your design would require virtually complete duplication since any company could be both a current and previous employer. That simplifies the junction table to:

tblStudentEmployment:
StudentEmploymentID (Primary Key)
StudentID
PreviousEmployerID
HireDate
TerminationDate
TerminationReason
Title
SupervisorLastName
SupervisorFirstName
SupervisorPhone

Technically, the supervisor fields should be replaced with SupervisorID and a new table should be defined to hold supervisor information. That table should look like

tblSupervisor
SupervisorID
LastName
FirstName
Phone
Extension
CompanyID

And finally, what is the purpose of the "previous" fields in the student table? If you want to keep history, create a history table and everytime someone changes something in the student table, copy the old record to the history table with the change date.

The reason I have previous numbers listed in the student table is allow us to reference there old numbers. Sometimes my staff needs to locate the student and information is old. By chance, they could be back at there old number that they had previously (for instance, they leave there parents house and move out on there own, then they move back to there parents with out notifying the school). Either way, will take it out because if it comes to that, we will have there physical file which will have all previous numbers listed.

I will take the employment tables (current / previous) and combine the two. Next I will make a seperate table for Supervisors as suggested.

I will try and get this all sorted out but will be going out of town for the holidays. I was having trouble making the relationships on the tables (queries were not working after setting up the relationships) so im sure I will have further questions.

Thanks again

Okay, so here is tblStudentEmployment

TblStudentEmployment
StudentEmploymentID (Primary Key) (Auto number)
StudentID
EmployerID
SupervisorID
HireDate
Related
Title
TerminationDate
TerminationReason

Now, my StudentID, EmployerID, and SupervisorID will have a relationship to those tables. Will my table (tblStudentEmployment) auto populate the appropriate ID # when the relationship is made?? Does this make sense?

Im not sure if the datatype should be auto number or if it should be text...