Importing basics

[Michael.Reynolds1775]

Hello all,



I'm new to the forum and look forward to learning quite a bit. I've plagiarized an access database used for my work to track employee information, training and qualifications. I have created/modified a form to collect all the information I want to gain, and connected every field to a column in a table. I already have all of the information for employee information on one excel sheet, training on another excel sheet, and qualifications on another.

I want to import these three worksheets into my access table (Main personnel) so that the correlating columns (name, address, phone number, etc) are filled with the information from the excel sheets.

can anyone provide me some input?

Thanks in advance

Mike

[June7]

What are training and qualifications - training taken by employees and qualifications possessed by employees?
What value will serve as the common key between these 3 datasets? An EmployeeID? This common value should be in all 3 datasets. Names make very poor unique IDs.

[Michael.Reynolds1775]

What are training and qualifications - training taken by employees and qualifications possessed by employees?
What value will serve as the common key between these 3 datasets? An EmployeeID? This common value should be in all 3 datasets. Names make very poor unique IDs.

Each employee is required to obtain one of three levels (member, leader, quality assurance) of mastery in several classes. additionally each employee may have unique training completion such as specific licenses (forklift, 5 ton truck, van, etc) or certifications (inspector course, developer, certifier, etc) so to answer your question, yes, training completed by employees and the qualifications possessed.

Each member of the business has an employee ID number that is unique. If im understanding correctly, each excel sheet should have the employee ID in A1, then the last name in A2 etc and this information should be exactly the same on each excel sheet?

[June7]

Employee ID in each sheet. Employee name should only be on Employee Info sheet. The only data that should be common to all 3 sheets is the Employee ID. Do you understand the nature of primary and foreign keys? This is critical to understanding relational database concepts.

I assume there are multiple records for training for each employee and multiple records of qualifications for each employee.

[Michael.Reynolds1775]

Learning is occurring! I do not understand the nature of primary and foreign keys. I assume it is what links each line of information together. you mentioned that last names are not good primary keys, and I assume this is due to the frequency of duplicates (Smith, Garcia, etc)? If that's the case, it makes sense. The employee ID we use is very similar to a SSN.

Each employee has their own record book. We have a clerk that is responsible to update each individuals book as the training is completed. When a manager asks "who is currently unqualified, and why?" I want to be able to generate a report of expired certifications.

[June7]

Keys are what link records in different tables. Save the employee ID value in training record so you know which employee this record belongs to. This way employee name is not duplicated into multiple tables and eliminates risk of misspelling.

When creating a new training record, can select employee from a combobox list instead of typing out full name over and over and over.

The employee ID is a custom unique identifier. Many developers would prefer to use Access autonumber field as primary key instead of the EmployeeID. However, using the employee ID in the Excel sheets is the easiest way to import the data to Access and maintain relational integrity.

[Michael.Reynolds1775]

Thanks!. Now that my main table is filled out (with 50+ columns of qualifications and information) how do I import just my EmployeeIDs and last names into that table? Is it only by way of manual input? I have 182 empolyees.

[June7]

I don't understand. What data is in this table?

[Michael.Reynolds1775]

The table consists of the required fields of information I want to collect (EmployeeID ie EDIPI, Last Name, First Name, blood type, DOB, etc)

I am collecting the information via a form.

I have several spreadsheets with the info spread throughout. For example, I have a sheet with last names and EDIPI's. EDIPI is my primary key per your recommendation. Each sheet has the EDIPI in common.

[June7]

I still don't understand your question about how to import EmployeeID and last names. It looks like they are already in there.

[Michael.Reynolds1775]

I have an excel sheet with 100+ more employees. I'd like to avoid entering them by hand if possible

[June7]

Why would you have to enter by hand? Enter to where? Import.

[Michael.Reynolds1775]

and so we come full circle... how do I perform the import?

[June7]

The how can be by several methods. Have you tried the Import wizard?

[ssanfu]

And what does the data look like? In text files, CSV files, Excel,.....?