If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add New Field to DB
I have a DB that someone who once worked here created. The DB is not set up
properly and I don't have the time to create another one and learn how to import all this data. Therefore, I have to work with what I have. This DB is used to track our company traning procedures for each employee. There are no primary keys set up and the employee table has the first and last names combined in one field - to give you an idea. Below are the table structures: Employee Table: EmployeeName EmployeeNumber DateOfHire TerminationDate Procedures Table: ProcedureNumber ProcedureName ProcedureType Training Grid Table: ProcedureNumber TrainingType EmployeeName DocumentationDate Training Type Table: Training Type We have changed our Procedure Numbers and they are now called NT Numbers. We are in the middle of changing over all the procedure numbers to the new numbers. In the mean time, I have to track this info in our database. As far as I know, a query should have been created to be used for data entry and the previous employee is using the "Training Grid" table for that purpose. How should this DB be changed so I can add a new field for "NT Number" and have each NT Number correspond in there respective record with the old procedure number? I hope I am explaining this so it is understood. Please let me know if I'm not - I really need to start entering the new numbers. ANY help would be greatly appreciated |
#2
|
|||
|
|||
Add New Field to DB
As a work around you can just add the new field in the Procedures Table.
I do not see dates when training was received. Some training is reoccuring like every year. The table needs to have a field for that. List all your training requirements and figure out the common calendar cycle to use. If nothing is less than a year then use increments of years except for one-time training that will have an interval of 0 (zero). Some training records are required to kept for an extended period by law. If your Human resources or trraining branch is not maintaining hard copy in the personnel folder then the database would be your legal records. I will post more if you want. "Karen" wrote: I have a DB that someone who once worked here created. The DB is not set up properly and I don't have the time to create another one and learn how to import all this data. Therefore, I have to work with what I have. This DB is used to track our company traning procedures for each employee. There are no primary keys set up and the employee table has the first and last names combined in one field - to give you an idea. Below are the table structures: Employee Table: EmployeeName EmployeeNumber DateOfHire TerminationDate Procedures Table: ProcedureNumber ProcedureName ProcedureType Training Grid Table: ProcedureNumber TrainingType EmployeeName DocumentationDate Training Type Table: Training Type We have changed our Procedure Numbers and they are now called NT Numbers. We are in the middle of changing over all the procedure numbers to the new numbers. In the mean time, I have to track this info in our database. As far as I know, a query should have been created to be used for data entry and the previous employee is using the "Training Grid" table for that purpose. How should this DB be changed so I can add a new field for "NT Number" and have each NT Number correspond in there respective record with the old procedure number? I hope I am explaining this so it is understood. Please let me know if I'm not - I really need to start entering the new numbers. ANY help would be greatly appreciated |
#3
|
|||
|
|||
Add New Field to DB
Thank you for your help - I added a new field to the Procedures Table - Now
where do I go from there? Won't there be a problem if the relationships are not set up properly? There is a date field "Documentation Date" in the Training Grid table. What I do is choose the training type and then I add the date for that record. Thank you again, Karen "KARL DEWEY" wrote: As a work around you can just add the new field in the Procedures Table. I do not see dates when training was received. Some training is reoccuring like every year. The table needs to have a field for that. List all your training requirements and figure out the common calendar cycle to use. If nothing is less than a year then use increments of years except for one-time training that will have an interval of 0 (zero). Some training records are required to kept for an extended period by law. If your Human resources or trraining branch is not maintaining hard copy in the personnel folder then the database would be your legal records. I will post more if you want. "Karen" wrote: I have a DB that someone who once worked here created. The DB is not set up properly and I don't have the time to create another one and learn how to import all this data. Therefore, I have to work with what I have. This DB is used to track our company traning procedures for each employee. There are no primary keys set up and the employee table has the first and last names combined in one field - to give you an idea. Below are the table structures: Employee Table: EmployeeName EmployeeNumber DateOfHire TerminationDate Procedures Table: ProcedureNumber ProcedureName ProcedureType Training Grid Table: ProcedureNumber TrainingType EmployeeName DocumentationDate Training Type Table: Training Type We have changed our Procedure Numbers and they are now called NT Numbers. We are in the middle of changing over all the procedure numbers to the new numbers. In the mean time, I have to track this info in our database. As far as I know, a query should have been created to be used for data entry and the previous employee is using the "Training Grid" table for that purpose. How should this DB be changed so I can add a new field for "NT Number" and have each NT Number correspond in there respective record with the old procedure number? I hope I am explaining this so it is understood. Please let me know if I'm not - I really need to start entering the new numbers. ANY help would be greatly appreciated |
#4
|
|||
|
|||
Add New Field to DB
Your post did not mention any relations. Do any exist?
YourTraining Grid Table has EmployeeName. If there were relations I would expect not to find name but EmployeeNumber in the table. Below is the table structure for a training database my company used for several years before moving on to a web-Orcale database. The TEMP EmployeeInfo table is how I connected weekly to HR for new hires, terminations, and people going or coming off casual/leave of absence. For a new hire I ran an append query that added all training marked as required for anyone in their department. Their manager was given a list of the training for them and asked to mark any training they did not need. That training was removed from their record. Table: CourseStatus tbl Columns Name Type Size CourseStatusId Long Integer 4 CourseStatus Text 50 Table: CourseType tbl Columns Name Type Size CourseTypeID Long Integer 4 CourseType Text 50 Table: DPINDEX Columns Name Type Size ID Long Integer 4 DP NUMBER Text 14 DP TITLE Text 255 BASIC DATE Date/Time 8 REVIEW DATE Date/Time 8 DEPT# Text 4 Table: EMPBRIEF Columns Name Type Size EMPNAME Text 20 EMP_NUMBER Text 9 BRIEFED Text 9 NEXT1 Date/Time 8 DEPT Text 6 BLDGNO Text 8 DCODE Text 7 FNAME Text 12 MNAME Text 12 Table: PPE LIST Columns Name Type Size ppe Text 50 Table: Report Level Columns Name Type Size Level Text 5 Table: Switchboard Items Columns Name Type Size SwitchboardID Long Integer 4 ItemNumber Integer 2 ItemText Text 255 Command Integer 2 Argument Text 50 Table: tbl CBT Course Description Columns Name Type Size CTL# Long Integer 4 Course Duration Text 2 Audiance Memo - Prerequisites Memo - Course Aim Text 255 Learning Objectives Memo - Topics Covered Memo - Course Incorporates Text 50 Table: tbl Certification Columns Name Type Size Number Long Integer 4 CourseID Text 50 Position Title Text 100 CourseTitle Text 50 RenewalPeriod Single 4 Table: tbl DeptInfo Columns Name Type Size Dept# Text 50 Range Text 50 DeptTitle Text 50 DeptManager Text 50 Table: tbl EduCourses Columns Name Type Size EduCourseID Long Integer 4 Course# Text 50 CourseTitle Text 150 StartDate Date/Time 8 EndDate Date/Time 8 CreditHours Long Integer 4 Grade Text 50 Cost Currency 8 CourseStatus Long Integer 4 Table: tbl Edu-Degree Columns Name Type Size EduDegID Long Integer 4 EmpDegId Long Integer 4 EduCourseID Long Integer 4 Table: tbl EduDegrees Columns Name Type Size DegreeID Long Integer 4 DegreeType Text 50 Table: tbl EduUniversities Columns Name Type Size UniversityID Long Integer 4 University Text 50 UniversityShort Text 50 Table: tbl Emp-Cert Columns Name Type Size Number Long Integer 4 SSN Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Cert Yes/No 1 Required Yes/No 1 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-Degree Columns Name Type Size EmpDegID Long Integer 4 SSN Text 50 UniversityID Long Integer 4 DegreeID Long Integer 4 Major Text 50 DateEntered Date/Time 8 GradDate Date/Time 8 DegreeComp Yes/No 1 Table: tbl Emp-Equipment Columns Name Type Size SSN Text 50 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-ETR Columns Name Type Size SSN Text 9 DateRecd Date/Time 8 ReviewDate Date/Time 8 Update Date/Time 8 CertReq? Yes/No 1 O/M Yes/No 1 Table: tbl Emp-ExtCSR Columns Name Type Size XAN# Text 200 SSN Text 200 Comp Date Date/Time 8 Table: tbl Emp-Gov Page: 20 Columns Name Type Size GAN Text 200 SSN Text 50 Class# Text 50 TLN# Text 50 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusId Long Integer 4 Evaluation Received? Yes/No 1 Certificate Received? Yes/No 1 Table: tbl EMP-IAN Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 Table: tbl EMP-IAN 1 Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 Table: tbl EmployeeInfo Columns Name Type Size SSN Text 9 LastName Text 25 FirstName Text 25 MI Text 2 Suffix Text 5 Group# Text 7 Dept# Text 50 CSCShort Text 8 LOA/CSL Yes/No 1 Term Yes/No 1 NonEmployee Yes/No 1 BLDG Text 6 BLDG DATE Date/Time 8 DEPT DATE Date/Time 8 Hire Date Date/Time 8 Table: tbl Emp-OMC Columns Name Type Size SSN Text 50 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-OMC Temp Columns Name Type Size SSN Text 9 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Equipment Columns Name Type Size Program# Text 50 OMC# Long Integer 4 Title Text 150 Dept# Text 10 PkgStatID Long Integer 4 CourseTypeID Long Integer 4 Test Equipment Flag Yes/No 1 Equipment Flag Yes/No 1 Table: tbl ExtCSR Columns Name Type Size XAN# Text 200 EntryDate Date/Time 8 CourseTitle Text 200 CourseProvider Text 200 CourseTypeID Long Integer 4 CompDate Date/Time 8 CourseStatusID Long Integer 4 Table: tbl Government Training Columns Name Type Size GAN Text 200 Course# Text 50 CourseTitle Text 50 DateReq Date/Time 8 Table: tbl Internal Columns Name Type Size IAN# Text 50 EntryDate Date/Time 8 CourseTitle Text 50 CourseProvider Text 100 CourseTypeID Long Integer 4 CourseStatusId Long Integer 4 Comments Memo - StartDate Date/Time 8 CompDate Date/Time 8 Table: tbl Internal 1 Columns Name Type Size Number Long Integer 4 CourseID Text 50 Position Title Text 100 CourseTitle Text 50 RenewalPeriod Single 4 Table: tbl OMC Columns Name Type Size Program# Text 50 OMC# Long Integer 4 Title Text 150 IssueDate Date/Time 8 RevDate Date/Time 8 Dept# Text 10 Revision# Long Integer 4 PkgStatID Long Integer 4 CourseTypeID Long Integer 4 Comments Memo - Nomenclature Text 50 PreReq Text 250 LWC Flag Yes/No 1 Desk Procedure Yes/No 1 Operating Instruction Yes/No 1 Table: tbl PkgStatus Columns Name Type Size PkgStatID Long Integer 4 PkgStatus Text 50 Table: tbl TEMP Safety Tour Columns Name Type Size SSN Text 9 LastName Text 25 FirstName Text 25 MI Text 2 Suffix Text 5 Group# Text 7 Dept# Text 50 HR Dept Text 3 DEPT DATE Date/Time 8 BLDG Text 6 HR Bldg Text 6 BLDG DATE Date/Time 8 LOA/CSL Yes/No 1 DateComp Date/Time 8 EMP_STAT Text 3 Update Integer 2 Update Bldg Integer 2 Term Yes/No 1 Table: TEMP EmployeeInfo Columns Name Type Size SSN Text 9 LastName Text 25 FirstName Text 25 MI Text 2 Suffix Text 25 Group# Text 7 Dept# Text 50 CSCShort Text 50 LOA/CSL Yes/No 1 Term Yes/No 1 NonEmployee Yes/No 1 BLDG Text 5 BLDG DATE Date/Time 8 DEPT DATE Date/Time 8 HIRE_DATE Date/Time 8 Minimum TNG Yes/No 1 Table: TEMP tbl Certification Columns Name Type Size Number Long Integer 4 CourseID Text 50 Position Title Text 100 CourseTitle Text 50 RenewalPeriod Single 4 "Karen" wrote: Thank you for your help - I added a new field to the Procedures Table - Now where do I go from there? Won't there be a problem if the relationships are not set up properly? There is a date field "Documentation Date" in the Training Grid table. What I do is choose the training type and then I add the date for that record. Thank you again, Karen "KARL DEWEY" wrote: As a work around you can just add the new field in the Procedures Table. I do not see dates when training was received. Some training is reoccuring like every year. The table needs to have a field for that. List all your training requirements and figure out the common calendar cycle to use. If nothing is less than a year then use increments of years except for one-time training that will have an interval of 0 (zero). Some training records are required to kept for an extended period by law. If your Human resources or trraining branch is not maintaining hard copy in the personnel folder then the database would be your legal records. I will post more if you want. "Karen" wrote: I have a DB that someone who once worked here created. The DB is not set up properly and I don't have the time to create another one and learn how to import all this data. Therefore, I have to work with what I have. This DB is used to track our company traning procedures for each employee. There are no primary keys set up and the employee table has the first and last names combined in one field - to give you an idea. Below are the table structures: Employee Table: EmployeeName EmployeeNumber DateOfHire TerminationDate Procedures Table: ProcedureNumber ProcedureName ProcedureType Training Grid Table: ProcedureNumber TrainingType EmployeeName DocumentationDate Training Type Table: Training Type We have changed our Procedure Numbers and they are now called NT Numbers. We are in the middle of changing over all the procedure numbers to the new numbers. In the mean time, I have to track this info in our database. As far as I know, a query should have been created to be used for data entry and the previous employee is using the "Training Grid" table for that purpose. How should this DB be changed so I can add a new field for "NT Number" and have each NT Number correspond in there respective record with the old procedure number? I hope I am explaining this so it is understood. Please let me know if I'm not - I really need to start entering the new numbers. ANY help would be greatly appreciated |
#5
|
|||
|
|||
Add New Field to DB
Actually there are relationships set up, but they are one-to-one.
Employee & Training Grid have the Employee Name as one-to-one Training Grid & Training Type have the Training Type as on-to-one Training Grid & Procedures Table have the Procedure Number as on-to-one I will have to add a field for my new procedure numbers in the Procedures Table. Then I use a form for data entry, which references a query for the record source. I do have a few concerns. Eventually, the old numbers will phase out. Right now I have a training record that has a new procedure number and not an old procedure number. I have to leave the old & new number fields on the form during the transition. In this situation, I don't want to leave the old number field blank and fill in the new number field. Someone may think that the user forgot to fill in that field. What should be done in a situation like this? Right now the vast majority have the old & new number. HELP! "KARL DEWEY" wrote: Your post did not mention any relations. Do any exist? YourTraining Grid Table has EmployeeName. If there were relations I would expect not to find name but EmployeeNumber in the table. Below is the table structure for a training database my company used for several years before moving on to a web-Orcale database. The TEMP EmployeeInfo table is how I connected weekly to HR for new hires, terminations, and people going or coming off casual/leave of absence. For a new hire I ran an append query that added all training marked as required for anyone in their department. Their manager was given a list of the training for them and asked to mark any training they did not need. That training was removed from their record. Table: CourseStatus tbl Columns Name Type Size CourseStatusId Long Integer 4 CourseStatus Text 50 Table: CourseType tbl Columns Name Type Size CourseTypeID Long Integer 4 CourseType Text 50 Table: DPINDEX Columns Name Type Size ID Long Integer 4 DP NUMBER Text 14 DP TITLE Text 255 BASIC DATE Date/Time 8 REVIEW DATE Date/Time 8 DEPT# Text 4 Table: EMPBRIEF Columns Name Type Size EMPNAME Text 20 EMP_NUMBER Text 9 BRIEFED Text 9 NEXT1 Date/Time 8 DEPT Text 6 BLDGNO Text 8 DCODE Text 7 FNAME Text 12 MNAME Text 12 Table: PPE LIST Columns Name Type Size ppe Text 50 Table: Report Level Columns Name Type Size Level Text 5 Table: Switchboard Items Columns Name Type Size SwitchboardID Long Integer 4 ItemNumber Integer 2 ItemText Text 255 Command Integer 2 Argument Text 50 Table: tbl CBT Course Description Columns Name Type Size CTL# Long Integer 4 Course Duration Text 2 Audiance Memo - Prerequisites Memo - Course Aim Text 255 Learning Objectives Memo - Topics Covered Memo - Course Incorporates Text 50 Table: tbl Certification Columns Name Type Size Number Long Integer 4 CourseID Text 50 Position Title Text 100 CourseTitle Text 50 RenewalPeriod Single 4 Table: tbl DeptInfo Columns Name Type Size Dept# Text 50 Range Text 50 DeptTitle Text 50 DeptManager Text 50 Table: tbl EduCourses Columns Name Type Size EduCourseID Long Integer 4 Course# Text 50 CourseTitle Text 150 StartDate Date/Time 8 EndDate Date/Time 8 CreditHours Long Integer 4 Grade Text 50 Cost Currency 8 CourseStatus Long Integer 4 Table: tbl Edu-Degree Columns Name Type Size EduDegID Long Integer 4 EmpDegId Long Integer 4 EduCourseID Long Integer 4 Table: tbl EduDegrees Columns Name Type Size DegreeID Long Integer 4 DegreeType Text 50 Table: tbl EduUniversities Columns Name Type Size UniversityID Long Integer 4 University Text 50 UniversityShort Text 50 Table: tbl Emp-Cert Columns Name Type Size Number Long Integer 4 SSN Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Cert Yes/No 1 Required Yes/No 1 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-Degree Columns Name Type Size EmpDegID Long Integer 4 SSN Text 50 UniversityID Long Integer 4 DegreeID Long Integer 4 Major Text 50 DateEntered Date/Time 8 GradDate Date/Time 8 DegreeComp Yes/No 1 Table: tbl Emp-Equipment Columns Name Type Size SSN Text 50 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-ETR Columns Name Type Size SSN Text 9 DateRecd Date/Time 8 ReviewDate Date/Time 8 Update Date/Time 8 CertReq? Yes/No 1 O/M Yes/No 1 Table: tbl Emp-ExtCSR Columns Name Type Size XAN# Text 200 SSN Text 200 Comp Date Date/Time 8 Table: tbl Emp-Gov Page: 20 Columns Name Type Size GAN Text 200 SSN Text 50 Class# Text 50 TLN# Text 50 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusId Long Integer 4 Evaluation Received? Yes/No 1 Certificate Received? Yes/No 1 Table: tbl EMP-IAN Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 Table: tbl EMP-IAN 1 Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 Table: tbl EmployeeInfo Columns Name Type Size SSN Text 9 LastName Text 25 FirstName Text 25 MI Text 2 Suffix Text 5 Group# Text 7 Dept# Text 50 CSCShort Text 8 LOA/CSL Yes/No 1 Term Yes/No 1 NonEmployee Yes/No 1 BLDG Text 6 BLDG DATE Date/Time 8 DEPT DATE Date/Time 8 Hire Date Date/Time 8 Table: tbl Emp-OMC Columns Name Type Size SSN Text 50 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-OMC Temp Columns Name Type Size SSN Text 9 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Equipment Columns Name Type Size |
#6
|
|||
|
|||
Add New Field to DB
How is it that you have one-to-one? Do not some employees require more than
one type of training class? Put a dash ( - ) in the old number field. Can you at sometime in the future return to using a single field even though you have two number schemes? "Karen" wrote: Actually there are relationships set up, but they are one-to-one. Employee & Training Grid have the Employee Name as one-to-one Training Grid & Training Type have the Training Type as on-to-one Training Grid & Procedures Table have the Procedure Number as on-to-one I will have to add a field for my new procedure numbers in the Procedures Table. Then I use a form for data entry, which references a query for the record source. I do have a few concerns. Eventually, the old numbers will phase out. Right now I have a training record that has a new procedure number and not an old procedure number. I have to leave the old & new number fields on the form during the transition. In this situation, I don't want to leave the old number field blank and fill in the new number field. Someone may think that the user forgot to fill in that field. What should be done in a situation like this? Right now the vast majority have the old & new number. HELP! "KARL DEWEY" wrote: Your post did not mention any relations. Do any exist? YourTraining Grid Table has EmployeeName. If there were relations I would expect not to find name but EmployeeNumber in the table. Below is the table structure for a training database my company used for several years before moving on to a web-Orcale database. The TEMP EmployeeInfo table is how I connected weekly to HR for new hires, terminations, and people going or coming off casual/leave of absence. For a new hire I ran an append query that added all training marked as required for anyone in their department. Their manager was given a list of the training for them and asked to mark any training they did not need. That training was removed from their record. Table: CourseStatus tbl Columns Name Type Size CourseStatusId Long Integer 4 CourseStatus Text 50 Table: CourseType tbl Columns Name Type Size CourseTypeID Long Integer 4 CourseType Text 50 Table: DPINDEX Columns Name Type Size ID Long Integer 4 DP NUMBER Text 14 DP TITLE Text 255 BASIC DATE Date/Time 8 REVIEW DATE Date/Time 8 DEPT# Text 4 Table: EMPBRIEF Columns Name Type Size EMPNAME Text 20 EMP_NUMBER Text 9 BRIEFED Text 9 NEXT1 Date/Time 8 DEPT Text 6 BLDGNO Text 8 DCODE Text 7 FNAME Text 12 MNAME Text 12 Table: PPE LIST Columns Name Type Size ppe Text 50 Table: Report Level Columns Name Type Size Level Text 5 Table: Switchboard Items Columns Name Type Size SwitchboardID Long Integer 4 ItemNumber Integer 2 ItemText Text 255 Command Integer 2 Argument Text 50 Table: tbl CBT Course Description Columns Name Type Size CTL# Long Integer 4 Course Duration Text 2 Audiance Memo - Prerequisites Memo - Course Aim Text 255 Learning Objectives Memo - Topics Covered Memo - Course Incorporates Text 50 Table: tbl Certification Columns Name Type Size Number Long Integer 4 CourseID Text 50 Position Title Text 100 CourseTitle Text 50 RenewalPeriod Single 4 Table: tbl DeptInfo Columns Name Type Size Dept# Text 50 Range Text 50 DeptTitle Text 50 DeptManager Text 50 Table: tbl EduCourses Columns Name Type Size EduCourseID Long Integer 4 Course# Text 50 CourseTitle Text 150 StartDate Date/Time 8 EndDate Date/Time 8 CreditHours Long Integer 4 Grade Text 50 Cost Currency 8 CourseStatus Long Integer 4 Table: tbl Edu-Degree Columns Name Type Size EduDegID Long Integer 4 EmpDegId Long Integer 4 EduCourseID Long Integer 4 Table: tbl EduDegrees Columns Name Type Size DegreeID Long Integer 4 DegreeType Text 50 Table: tbl EduUniversities Columns Name Type Size UniversityID Long Integer 4 University Text 50 UniversityShort Text 50 Table: tbl Emp-Cert Columns Name Type Size Number Long Integer 4 SSN Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Cert Yes/No 1 Required Yes/No 1 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-Degree Columns Name Type Size EmpDegID Long Integer 4 SSN Text 50 UniversityID Long Integer 4 DegreeID Long Integer 4 Major Text 50 DateEntered Date/Time 8 GradDate Date/Time 8 DegreeComp Yes/No 1 Table: tbl Emp-Equipment Columns Name Type Size SSN Text 50 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-ETR Columns Name Type Size SSN Text 9 DateRecd Date/Time 8 ReviewDate Date/Time 8 Update Date/Time 8 CertReq? Yes/No 1 O/M Yes/No 1 Table: tbl Emp-ExtCSR Columns Name Type Size XAN# Text 200 SSN Text 200 Comp Date Date/Time 8 Table: tbl Emp-Gov Page: 20 Columns Name Type Size GAN Text 200 SSN Text 50 Class# Text 50 TLN# Text 50 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusId Long Integer 4 Evaluation Received? Yes/No 1 Certificate Received? Yes/No 1 Table: tbl EMP-IAN Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 Table: tbl EMP-IAN 1 Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 Table: tbl EmployeeInfo Columns Name Type Size SSN Text 9 LastName Text 25 FirstName Text 25 MI Text 2 Suffix Text 5 Group# Text 7 Dept# Text 50 CSCShort Text 8 LOA/CSL Yes/No 1 Term Yes/No 1 NonEmployee Yes/No 1 BLDG Text 6 BLDG DATE Date/Time 8 DEPT DATE Date/Time 8 Hire Date Date/Time 8 Table: tbl Emp-OMC Columns Name Type Size SSN Text 50 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 |
#7
|
|||
|
|||
Add New Field to DB
This DB was created by someone who used to work here and now I am responsible
for the DB. When I open the relationship window, there is only a line connecting the fileds I mentioned. Isn't that a one-to-one? It's not a one-to-many (with the infinity symbol) To answer your questions: Do not some employees require more than one type of training class? Yes they do Can you at sometime in the future return to using a single field even though you have two number schemes? We may want to always keep the old number. If that changes, it won't be for many years. ANY suggestions? Thank you again for your help "KARL DEWEY" wrote: How is it that you have one-to-one? Do not some employees require more than one type of training class? Put a dash ( - ) in the old number field. Can you at sometime in the future return to using a single field even though you have two number schemes? "Karen" wrote: Actually there are relationships set up, but they are one-to-one. Employee & Training Grid have the Employee Name as one-to-one Training Grid & Training Type have the Training Type as on-to-one Training Grid & Procedures Table have the Procedure Number as on-to-one I will have to add a field for my new procedure numbers in the Procedures Table. Then I use a form for data entry, which references a query for the record source. I do have a few concerns. Eventually, the old numbers will phase out. Right now I have a training record that has a new procedure number and not an old procedure number. I have to leave the old & new number fields on the form during the transition. In this situation, I don't want to leave the old number field blank and fill in the new number field. Someone may think that the user forgot to fill in that field. What should be done in a situation like this? Right now the vast majority have the old & new number. HELP! "KARL DEWEY" wrote: Your post did not mention any relations. Do any exist? YourTraining Grid Table has EmployeeName. If there were relations I would expect not to find name but EmployeeNumber in the table. Below is the table structure for a training database my company used for several years before moving on to a web-Orcale database. The TEMP EmployeeInfo table is how I connected weekly to HR for new hires, terminations, and people going or coming off casual/leave of absence. For a new hire I ran an append query that added all training marked as required for anyone in their department. Their manager was given a list of the training for them and asked to mark any training they did not need. That training was removed from their record. Table: CourseStatus tbl Columns Name Type Size CourseStatusId Long Integer 4 CourseStatus Text 50 Table: CourseType tbl Columns Name Type Size CourseTypeID Long Integer 4 CourseType Text 50 Table: DPINDEX Columns Name Type Size ID Long Integer 4 DP NUMBER Text 14 DP TITLE Text 255 BASIC DATE Date/Time 8 REVIEW DATE Date/Time 8 DEPT# Text 4 Table: EMPBRIEF Columns Name Type Size EMPNAME Text 20 EMP_NUMBER Text 9 BRIEFED Text 9 NEXT1 Date/Time 8 DEPT Text 6 BLDGNO Text 8 DCODE Text 7 FNAME Text 12 MNAME Text 12 Table: PPE LIST Columns Name Type Size ppe Text 50 Table: Report Level Columns Name Type Size Level Text 5 Table: Switchboard Items Columns Name Type Size SwitchboardID Long Integer 4 ItemNumber Integer 2 ItemText Text 255 Command Integer 2 Argument Text 50 Table: tbl CBT Course Description Columns Name Type Size CTL# Long Integer 4 Course Duration Text 2 Audiance Memo - Prerequisites Memo - Course Aim Text 255 Learning Objectives Memo - Topics Covered Memo - Course Incorporates Text 50 Table: tbl Certification Columns Name Type Size Number Long Integer 4 CourseID Text 50 Position Title Text 100 CourseTitle Text 50 RenewalPeriod Single 4 Table: tbl DeptInfo Columns Name Type Size Dept# Text 50 Range Text 50 DeptTitle Text 50 DeptManager Text 50 Table: tbl EduCourses Columns Name Type Size EduCourseID Long Integer 4 Course# Text 50 CourseTitle Text 150 StartDate Date/Time 8 EndDate Date/Time 8 CreditHours Long Integer 4 Grade Text 50 Cost Currency 8 CourseStatus Long Integer 4 Table: tbl Edu-Degree Columns Name Type Size EduDegID Long Integer 4 EmpDegId Long Integer 4 EduCourseID Long Integer 4 Table: tbl EduDegrees Columns Name Type Size DegreeID Long Integer 4 DegreeType Text 50 Table: tbl EduUniversities Columns Name Type Size UniversityID Long Integer 4 University Text 50 UniversityShort Text 50 Table: tbl Emp-Cert Columns Name Type Size Number Long Integer 4 SSN Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Cert Yes/No 1 Required Yes/No 1 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-Degree Columns Name Type Size EmpDegID Long Integer 4 SSN Text 50 UniversityID Long Integer 4 DegreeID Long Integer 4 Major Text 50 DateEntered Date/Time 8 GradDate Date/Time 8 DegreeComp Yes/No 1 Table: tbl Emp-Equipment Columns Name Type Size SSN Text 50 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-ETR Columns Name Type Size SSN Text 9 DateRecd Date/Time 8 ReviewDate Date/Time 8 Update Date/Time 8 CertReq? Yes/No 1 O/M Yes/No 1 Table: tbl Emp-ExtCSR Columns Name Type Size XAN# Text 200 SSN Text 200 Comp Date Date/Time 8 Table: tbl Emp-Gov Page: 20 Columns Name Type Size GAN Text 200 SSN Text 50 Class# Text 50 TLN# Text 50 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusId Long Integer 4 Evaluation Received? Yes/No 1 Certificate Received? Yes/No 1 Table: tbl EMP-IAN Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 Table: tbl EMP-IAN 1 Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 Table: tbl EmployeeInfo Columns Name Type Size SSN Text 9 LastName Text 25 FirstName Text 25 MI Text 2 Suffix Text 5 Group# Text 7 Dept# Text 50 CSCShort Text 8 LOA/CSL Yes/No 1 Term Yes/No 1 NonEmployee Yes/No 1 BLDG Text 6 BLDG DATE Date/Time 8 DEPT DATE Date/Time 8 Hire Date Date/Time 8 |
#8
|
|||
|
|||
Add New Field to DB
I think your Employee & Training Grid should have the EmployeeNumber as
one-to-many. Two things to do after you make a backup copy of ther database. Add EmployeeNumber to the Training Grid and update to fill it in. Delete the relation between Employee & Training Grid. Make the EmployeeNumber the key field in the Employee table. Then create a one-to-many relations Employee & Training Grid on the EmployeeNumber. Select the update but not the delete unless you know all the ramifications. "Karen" wrote: This DB was created by someone who used to work here and now I am responsible for the DB. When I open the relationship window, there is only a line connecting the fileds I mentioned. Isn't that a one-to-one? It's not a one-to-many (with the infinity symbol) To answer your questions: Do not some employees require more than one type of training class? Yes they do Can you at sometime in the future return to using a single field even though you have two number schemes? We may want to always keep the old number. If that changes, it won't be for many years. ANY suggestions? Thank you again for your help "KARL DEWEY" wrote: How is it that you have one-to-one? Do not some employees require more than one type of training class? Put a dash ( - ) in the old number field. Can you at sometime in the future return to using a single field even though you have two number schemes? "Karen" wrote: Actually there are relationships set up, but they are one-to-one. Employee & Training Grid have the Employee Name as one-to-one Training Grid & Training Type have the Training Type as on-to-one Training Grid & Procedures Table have the Procedure Number as on-to-one I will have to add a field for my new procedure numbers in the Procedures Table. Then I use a form for data entry, which references a query for the record source. I do have a few concerns. Eventually, the old numbers will phase out. Right now I have a training record that has a new procedure number and not an old procedure number. I have to leave the old & new number fields on the form during the transition. In this situation, I don't want to leave the old number field blank and fill in the new number field. Someone may think that the user forgot to fill in that field. What should be done in a situation like this? Right now the vast majority have the old & new number. HELP! "KARL DEWEY" wrote: Your post did not mention any relations. Do any exist? YourTraining Grid Table has EmployeeName. If there were relations I would expect not to find name but EmployeeNumber in the table. Below is the table structure for a training database my company used for several years before moving on to a web-Orcale database. The TEMP EmployeeInfo table is how I connected weekly to HR for new hires, terminations, and people going or coming off casual/leave of absence. For a new hire I ran an append query that added all training marked as required for anyone in their department. Their manager was given a list of the training for them and asked to mark any training they did not need. That training was removed from their record. Table: CourseStatus tbl Columns Name Type Size CourseStatusId Long Integer 4 CourseStatus Text 50 Table: CourseType tbl Columns Name Type Size CourseTypeID Long Integer 4 CourseType Text 50 Table: DPINDEX Columns Name Type Size ID Long Integer 4 DP NUMBER Text 14 DP TITLE Text 255 BASIC DATE Date/Time 8 REVIEW DATE Date/Time 8 DEPT# Text 4 Table: EMPBRIEF Columns Name Type Size EMPNAME Text 20 EMP_NUMBER Text 9 BRIEFED Text 9 NEXT1 Date/Time 8 DEPT Text 6 BLDGNO Text 8 DCODE Text 7 FNAME Text 12 MNAME Text 12 Table: PPE LIST Columns Name Type Size ppe Text 50 Table: Report Level Columns Name Type Size Level Text 5 Table: Switchboard Items Columns Name Type Size SwitchboardID Long Integer 4 ItemNumber Integer 2 ItemText Text 255 Command Integer 2 Argument Text 50 Table: tbl CBT Course Description Columns Name Type Size CTL# Long Integer 4 Course Duration Text 2 Audiance Memo - Prerequisites Memo - Course Aim Text 255 Learning Objectives Memo - Topics Covered Memo - Course Incorporates Text 50 Table: tbl Certification Columns Name Type Size Number Long Integer 4 CourseID Text 50 Position Title Text 100 CourseTitle Text 50 RenewalPeriod Single 4 Table: tbl DeptInfo Columns Name Type Size Dept# Text 50 Range Text 50 DeptTitle Text 50 DeptManager Text 50 Table: tbl EduCourses Columns Name Type Size EduCourseID Long Integer 4 Course# Text 50 CourseTitle Text 150 StartDate Date/Time 8 EndDate Date/Time 8 CreditHours Long Integer 4 Grade Text 50 Cost Currency 8 CourseStatus Long Integer 4 Table: tbl Edu-Degree Columns Name Type Size EduDegID Long Integer 4 EmpDegId Long Integer 4 EduCourseID Long Integer 4 Table: tbl EduDegrees Columns Name Type Size DegreeID Long Integer 4 DegreeType Text 50 Table: tbl EduUniversities Columns Name Type Size UniversityID Long Integer 4 University Text 50 UniversityShort Text 50 Table: tbl Emp-Cert Columns Name Type Size Number Long Integer 4 SSN Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Cert Yes/No 1 Required Yes/No 1 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-Degree Columns Name Type Size EmpDegID Long Integer 4 SSN Text 50 UniversityID Long Integer 4 DegreeID Long Integer 4 Major Text 50 DateEntered Date/Time 8 GradDate Date/Time 8 DegreeComp Yes/No 1 Table: tbl Emp-Equipment Columns Name Type Size SSN Text 50 OMC# Long Integer 4 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusID Long Integer 4 PkgStatID Long Integer 4 Certificate Yes/No 1 EntryDate Date/Time 8 Trainer Initials Text 3 Employee Initials Text 3 Table: tbl Emp-ETR Columns Name Type Size SSN Text 9 DateRecd Date/Time 8 ReviewDate Date/Time 8 Update Date/Time 8 CertReq? Yes/No 1 O/M Yes/No 1 Table: tbl Emp-ExtCSR Columns Name Type Size XAN# Text 200 SSN Text 200 Comp Date Date/Time 8 Table: tbl Emp-Gov Page: 20 Columns Name Type Size GAN Text 200 SSN Text 50 Class# Text 50 TLN# Text 50 StartDate Date/Time 8 EndDate Date/Time 8 CourseStatusId Long Integer 4 Evaluation Received? Yes/No 1 Certificate Received? Yes/No 1 Table: tbl EMP-IAN Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 Table: tbl EMP-IAN 1 Columns Name Type Size ID Long Integer 4 SSN Text 50 IAN# Text 50 DateStart Date/Time 8 DateComp Date/Time 8 Certificate Yes/No 1 Required Yes/No 1 Trainer Initals Text 3 Employee Initials Text 3 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help to merge Image (Doug Robins?) | JohnB | Mailmerge | 21 | June 23rd, 2005 05:28 PM |
DCount compare table.textfield to form.text field question | RNUSZ@OKDPS | Using Forms | 1 | March 11th, 2005 02:05 AM |
New Record Update | Michelle | Using Forms | 5 | October 28th, 2004 07:59 AM |
field manipulation | Steve | Running & Setting Up Queries | 2 | May 28th, 2004 03:12 PM |
Supress blank lines in DOCPROPERTY field | Mary | Formatting Long Documents | 10 | May 25th, 2004 07:27 PM |