Populating some fields in a table from another table 1

[tiger3p]

Hello ..

I have a table called tblEmployee with the data...

I created a seperate table labeled tblEEINFO.. the fields on this table contains link to tblEmployee on the following fields:

EEID = employee id
STATUSID = full-time or part-time
OFFICEID = location of employee


This table also have the following fields which is the same as in tblEmployee:

LastName
FirstName
Phone#
Email

The fields below are new fields specifically for tblEEINFO
Undergrad School
Degree 1
Undergrad School 2
Degree 2
Undergrad School 3
Degree 3
Grad School 1
Grad Degree 1
Grad School 2
Grad Degree 2
Previous Employer

Now .. I want tblEEINFO to be populated with the data already in tblEmployee (lastname, firstname, phone#, email).

Is there a way to do this??

Thanks so much and sorry if I confuse anyone.

 

[genomon]

This is easily done with SQL & the DoCmd object. Look up Access help on DoCmd; here is a good starting point for learning basic SQL:

http://www.justphukit.com/sql/sql-tutorials-1.php

All I ask is a chance to prove that money can't make me happy.

 

[lespaul]

having fields with numbers at the end like [Undergrad School 1] and [Undergrad School 2] breaks the rules of database normalization...you should try to change that. Additionally having spaces in the field names requires that in every query you surround that will need brackets as shown above...you may want to reconsider your field names.



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[tiger3p]

Hi Leslie,

I recreated the tables to the following:

Undergrad_School (could be more than 1)
Degree_Attained (could be more than 1)
Grad_School (could be more than 1)
Grad_degree (could be more than 1)
Previous_Employeer (could be more than 1)

I created a table named tblEEInfo and the field EEID is link to tblEmployee EEID (one to many)

however, when I click on tblEEInfo it has blank information. Any thoughts on how to get this populated? Form or query or report?

Thank you so much!

 

[MajP]

Those are good fixes but you still have a bad design

I created a seperate table labeled tblEEINFO.. the fields on this table contains link to tblEmployee on the following fields:

EEID = employee id
STATUSID = full-time or part-time
OFFICEID = location of employee

If I understand correctly an Employee has a unique employee ID. But you use a composite key composed of EEID, StatusID, and OFFICEID to link to tblEmployee. Why would you need a composite key. Why not link by employee ID.

Also now that you normalized your tables why even have a EEINFO table?

This table also have the following fields which is the same as in tblEmployee:

LastName
FirstName
Phone#
Email

Why would you repeat information in two places? This is a poor database design.

You should have tblEmployee with information that is unique to an employee to include things like
STATUSID = full-time or part-time
OFFICEID = location of employee

An employee should have a unique ID that is used to link to other tables:
Undergrad_School (could be more than 1)
Degree_Attained (could be more than 1)
Grad_School (could be more than 1)
Grad_degree (could be more than 1)
Previous_Employeer (could be more than 1)

You could build a main employee form and have a tab control with 5 tabs. Each tab could be a continous form for School, degree, grad degree. But I actually do not think those are the correct tables. I would have two tables

tblEducation
strSchoolName
strSchoolType (grad, undergrad, etc)
strDegreeObtained (BS, BA, MS, PHD)
other fields

tblEmployer
strEmployerName
other fields

Now you can use queries to link your tables and show all the data as you want.

 

[tiger3p]

"tblEducation
strSchoolName
strSchoolType (grad, undergrad, etc)
strDegreeObtained (BS, BA, MS, PHD)
other fields"

Will I be able to accomplish the following with this table structure:

one employee with multiple schools (undergrad and grad) and multiple degrees?

Joe Smith
University of Maine
BS Engineering
University of Toledo
BS Accounting
GWU
Masters in Engineering

Currently my table structure is the following:

EEINFOID (primary key)
EEID (connected to tblEmployee)
College1 (coming from tblCollege)
College2 (coming from tblCollege)
Degree1 (coming from tblColDegree)
Degree2 (coming from tblColDegree)
GradSchool1 (coming from tblGradSchool)
GradSChool2 (coming from tblGradSchool)
GradDegree1 (coming from tblGradDegree)
GradDegree2 (coming from tblGradDegree)
PrevEmplyr1
PrevEmplyr2

Thanks again!

 

[lespaul]

yes you can handle that structure if you set it up like this:

EEINFOID (primary key)
EEID (connected to tblEmployee)
CollegeType (College or GradSchool)
CollegeName
Degree

EEINFOID EEID CollegeType CollegeName Degree
12502 15 College Univ. of Maine BS - Eng
12503 15 College Univ. of Toledo BS - Acct
12504 15 Grad GWU MS - Eng



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[tiger3p]

Thank you all!

I'll play with the table and the update forms...

 

[lespaul]

you may also want to read The Fundamentals of Relational Database Design

 

[MajP]

No you can not, or at least in a proper logical way. What Leslie says is correct (she repeated exactly what I said), but what you show is incorrect.

EEINFOID (primary key)
EEID (connected to tblEmployee)
College1 (coming from tblCollege)
College2 (coming from tblCollege)
Degree1 (coming from tblColDegree)
Degree2 (coming from tblColDegree)
GradSchool1 (coming from tblGradSchool)
GradSChool2 (coming from tblGradSchool)
GradDegree1 (coming from tblGradDegree)
GradDegree2 (coming from tblGradDegree)
PrevEmplyr1
PrevEmplyr2

It looks to me like you are thinking like you are designing a spreadsheet like Excel, but not a relational database. Maybe if you picture a form that looks something like this:

Name: Joe Smith Office: Corporate
Phone: 123-456 email: Joe.smith@aol.com

Education:
College Univ. of Maine BS - Eng
College Univ. of Toledo BS - Acct
Grad GWU MS - Eng

Previous Employer
Walmart 1/1/2007 to 1/1/2008
McDonalads 1/2/2008 to 1/25/2008

Main form pulls info from the tblEmployee
Subform one pulls eduaction info from tblEducation linked by employeeID
Subform two pulls employer info from tblEmployeer linked by employeeID

This design allows you to have as many degrees as you want, and as many employers.

 

[tiger3p]

Hello,

I created a form pulling data from tblEmployee and the subform pulling from tblEEInfo. But when I try to enter more than 1 college/degree for 1 employee I get the following error:

You cannot add or change a record because a related record is required in table 'tblEmployee'

 

[MajP]

You have create a relationship between the two tables and enforced referential integrity. You have a many to many relationship where one employee can have many education records in the employee info table.

This means that if both tables are linked by employeeID you can not make a record in the EEinfo table unless it has a employeeID key in it (foreign key). This is called creating an orphan. You would create eduacation info not related to a parent employee, thus an orphan.

You are trying to do this
15 Grad GWU MS - Eng
You need to do this
12504 15 Grad GWU MS - Eng

If the subform is linked to the mainform this should happen automatically. The subform should automatically put (12504) in the EEinfo record, so not sure why this is happening. Look in the subform control properties. It should say:
link master: employeeID
link child: employeeID

 

[tiger3p]

Cool .. it works now.. Thanks!

Another question, when I try to open tblEmployee, I get the following pop up dialog:

Insert Subdatasheet

Tables tab, Queries Tab, Both

What is this? I use to be able to click on the [+] sign next to the employee and I could see another datasheet under the employeeID.

 

[MajP]

I have never seen it do this, but open your table in design view. Right click and select "properties". What do the following fields say?
subdatasheet name
link child fields
link master fields

subdatasheet name is a pulldown. You can pick "none" that should clear the problem, but you probably want to pick EEinfo. I think the links fields will automatically populate but they should be EEID.

 

[tiger3p]

subdatasheet name == Auto
link child fields == blank
link master fields == blank

I tried using another table design:

tblCertific
CertID = auto
EEID = Number (link to tblEmployee)
Certification = text
tblDegree
DegreeID = auto
EEID = Number (link to tblEmployee)
Degree = text

tblSchool
SchoolID = auto
EEID = Number (link to tblEmployee)
SchoolName = text (pulldown from tblUniversity)

tblEmployer
EmployerID = auto
EEID = Number (link to tblEmployee)
Employer = text

How do i pull all these table together in a form that the user can populate? and I'm assuming that the data will populate these tables..

How do I create a report to show:

EEID 123
FirstName: Joe
LastName: Smith

Certification: MCSE
College: Univ. of Toledo
College Type: Undergrad
Degree: BS Engineering
College: University of Miami
College Type: Graduate
Degree: MBA

 

[lespaul]

when I try to open tblEmployee, I use to be able to click on the [+] sign next to the employee and I could see another datasheet under the employeeID.

Sounds like your table has 'Lookup Fields'; check out The evils of Access Lookup Fields

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[tiger3p]

hmm I do have lookup field in my tblEEINFO

under the field Degree

Display Control - Combo Box
Row Source - table/query
Row Source - SELECT tblDegrees.DegreeID, tblDegrees.Degree FROM tblDegrees;

 

[MajP]

How do i pull all these table together in a form that the user can populate? and I'm assuming that the data will populate these tables.

I demonstrated this in the reply of 24 January 16:22 using subforms. First of all degree information and school need to go in the same table. They are related information, the real entity is education. Both tables just describe the persons education. You seem to be reluctant, but both Leslie and I have suggested that the correct table structure is:

tblEducation
strEducationType (grad, undergrad, etc)
strSchoolName
strDegreeObtained (BS, BA, MS, PHD)
other fields

Look at my post again, but if you do not understand subforms and how to use them look at the Northwind database that comes with access as an example. My design for a form is a main form based on the employee table and then a subform for certification, education, and employer. If you want to get fancy put a tab form on the main form. Each tab has a continous subform: education, certification, employer

The report is the same approach. Main form with subreports. On the subreports you need to review the can grow property to get nice formatting.

It seems that you are in a tabular form mindset. This is clunky in a one to many relationship wher you have a variable number of certifications, educatins, and employers. You could have one person with 10 certifications, 4 degrees, and 5 employers while the next person could have 1 cert, 0 degrees, and 1 employer. Does not work well in a tab design. Again my report or form would look like this

Name: Joe Smith     Office: Corporate
Phone: 123-456      email: Joe.smith@aol.com

Certifications: [b](Subform/subreport 1)[/b]
  Cisco              1/1/2006
  Microsoft Network  1/2/2008

Education:      [b] (subform/subreport 2)[/b]
 College        Univ. of Maine   BS - Eng
 College        Univ. of Toledo  BS - Acct
 Grad           GWU              MS - Eng

Previous Employer:  [b](subform/subreport 3)[/b]
  Walmart 1/1/2007 to 1/1/2008
  McDonalads 1/2/2008 to 1/25/2008



Name: New Guy       Office: Mail Room
Phone: 123-457      email: new.guy@mycompany.com

Certifications:

Education:
 High School        Coolie High School   Diploma

Previous Employer:
  Burger King       1/2/2006 to 1/25/2008

 

[tiger3p]

tblEducation
strEducationType (grad, undergrad, etc)
strSchoolName
strDegreeObtained (BS, BA, MS, PHD)
other fields

Do I need to set up strEducationID and set that as the primary Key?

 

[tiger3p]

Is there a way to display more than 1 College in the form I've attached? I'm not sure i know how to go about it..

Thank you!