Another linking/Join Question (1 Viewer)
- Thread starter Rick
- Start date
2 more items Sambo
Hi Sambo,
1) I tried to import as fresh Employee file into the tblEMP and a Department head file into the tblDepthead tables. Problem is, When I attempt to import 1675 Job position records into the table i get a "Validation" error and it wants to only import 746 records. Is there a way that i can get all records.
2) This is a situation that was brought up today, There are departments that may have more than 1 of the same job position listed due to different people in those positions. Not sure if you will understand this but, What needs to be done(If possible) is, If a department has more than one of the same job position, instead of clicking the next position icon and having it click through (Example) 4 or 5 RN III's, is there a way to only move to the next RN III IF the "Position Qualifier" field gets populated with data. In other words, In most cases, the PHI elements will apply to all "duplicate job positions" within a department. If one of the dupluicate positions listed (Example: RN III's) gets a different set of PHI qualifiers, then some how allow the next position icon to show another RN III. This is hard to explain so i will try this in a nutshell: No matter how many of the same job positions full under any particular department, They only want to be able to scroll through more than one of the same positions IF there are different position qualifiers that apply. I would imagine that the trigger for this would be something like: if position Qualifier is "Not Null" then ..........(somehow allow for another (duplicate) job position. Sorry if this is not clear, it's hard to explain in type. Thanks
Hi Sambo,
1) I tried to import as fresh Employee file into the tblEMP and a Department head file into the tblDepthead tables. Problem is, When I attempt to import 1675 Job position records into the table i get a "Validation" error and it wants to only import 746 records. Is there a way that i can get all records.
2) This is a situation that was brought up today, There are departments that may have more than 1 of the same job position listed due to different people in those positions. Not sure if you will understand this but, What needs to be done(If possible) is, If a department has more than one of the same job position, instead of clicking the next position icon and having it click through (Example) 4 or 5 RN III's, is there a way to only move to the next RN III IF the "Position Qualifier" field gets populated with data. In other words, In most cases, the PHI elements will apply to all "duplicate job positions" within a department. If one of the dupluicate positions listed (Example: RN III's) gets a different set of PHI qualifiers, then some how allow the next position icon to show another RN III. This is hard to explain so i will try this in a nutshell: No matter how many of the same job positions full under any particular department, They only want to be able to scroll through more than one of the same positions IF there are different position qualifiers that apply. I would imagine that the trigger for this would be something like: if position Qualifier is "Not Null" then ..........(somehow allow for another (duplicate) job position. Sorry if this is not clear, it's hard to explain in type. Thanks
sambo
Registered User.
- Local time
- Today, 14:47
- Joined
- Aug 29, 2002
- Messages
- 289
Rich,
1) The reason you can't import all of the tblEmp records is because some are orphaned. That is, they don't have a corresponding department head.
If you remember, we set your table structure up so that there is ONE (1) DeptHead to MANY Employees. Translated, this means that EVERY employee must be associated with a SPECIFIC department head.
So, the key will be to make sure the the Foreign Key Value in your tblEmp matches a Primary Key Value in your tblDept.
Example...
If John Smith is in tblEmp and has a DeptID # of 1.1001
Then it stands to reason that DeptID # 1.001 must first exist in tblDept. Otherwise, John will become an orphan and the rules of referential integrity (validation rules) will be violated.
2) Remeber when you said, "It may not be pretty but its what they want and it SEEMS to work fine." Well, I'm sorry to tell you that that philosophy has bitten you in the proverbial butt. What you really need as a tblJobQualify (underneath tblEmp). This would allow you to have many job qualifiers to ONE (1) tblEmp entry.
As it stands now you've got a static number of categories that can be associated with each job. This is baaaaadddd!!!
So, you can either keep walking in quick sand, or, you can restructure.
1) The reason you can't import all of the tblEmp records is because some are orphaned. That is, they don't have a corresponding department head.
If you remember, we set your table structure up so that there is ONE (1) DeptHead to MANY Employees. Translated, this means that EVERY employee must be associated with a SPECIFIC department head.
So, the key will be to make sure the the Foreign Key Value in your tblEmp matches a Primary Key Value in your tblDept.
Example...
If John Smith is in tblEmp and has a DeptID # of 1.1001
Then it stands to reason that DeptID # 1.001 must first exist in tblDept. Otherwise, John will become an orphan and the rules of referential integrity (validation rules) will be violated.
2) Remeber when you said, "It may not be pretty but its what they want and it SEEMS to work fine." Well, I'm sorry to tell you that that philosophy has bitten you in the proverbial butt. What you really need as a tblJobQualify (underneath tblEmp). This would allow you to have many job qualifiers to ONE (1) tblEmp entry.
As it stands now you've got a static number of categories that can be associated with each job. This is baaaaadddd!!!
So, you can either keep walking in quick sand, or, you can restructure.
I know my friend, My lack of knowledge and time constraint were the factors but, You are absolutley correct. I was able to find out specificaly what is needed, My question to you is, after explaining to you what is needed, Is this D/B so far gone that we cannot tweak and fine tune? If you tell me what to do Sambo i will do it. I don't want to take advantage of your hospitality but i am stuck and really in need of assistance.
As i had said, They did not need to see more than 1 of the same position due to the database being geared towards the JOB POSITION and not employees. I had to go into the table this morning and delete any multible job positions that fell under the same DEPARTMENT. That was the one obstical.
Here is the part i have no clue about, If for some reason a department should have the need for duplicate JOB POSITIONS (Example LAB TECH - Clerk
LAB TECH - Manager) and these two lab techs should have different qualifiers assigned, What he wants to see is an "Add POSITION" button that will allow the Department head to add another "Lab Tech" position with their own position qualifier. Sambo, Can this be accomplished without a major rewrite of the D/B? Thanks buddy
As i had said, They did not need to see more than 1 of the same position due to the database being geared towards the JOB POSITION and not employees. I had to go into the table this morning and delete any multible job positions that fell under the same DEPARTMENT. That was the one obstical.
Here is the part i have no clue about, If for some reason a department should have the need for duplicate JOB POSITIONS (Example LAB TECH - Clerk
LAB TECH - Manager) and these two lab techs should have different qualifiers assigned, What he wants to see is an "Add POSITION" button that will allow the Department head to add another "Lab Tech" position with their own position qualifier. Sambo, Can this be accomplished without a major rewrite of the D/B? Thanks buddy
sambo
Registered User.
- Local time
- Today, 14:47
- Joined
- Aug 29, 2002
- Messages
- 289
Here's the thing Rick..
We truly are not far from having this thing looking (and working) fairly slick. The only catch is going to be data entry. If we add a third table you are going to have to go through and manually enter the qualification for each of your 400+ job positions.
If this is cool, then here is a good strategy.
1) 3 Tables
2) Slightly New Form Structure that uses a Subform (continuous layout) that allows us to add (or subtract) any qualification we choose for any given job.
This really won't be difficult at all. The only problem will be getting all of your data uploaded correctly.
Post your latest REV and we'll get going..
We truly are not far from having this thing looking (and working) fairly slick. The only catch is going to be data entry. If we add a third table you are going to have to go through and manually enter the qualification for each of your 400+ job positions.
If this is cool, then here is a good strategy.
1) 3 Tables
2) Slightly New Form Structure that uses a Subform (continuous layout) that allows us to add (or subtract) any qualification we choose for any given job.
This really won't be difficult at all. The only problem will be getting all of your data uploaded correctly.
Post your latest REV and we'll get going..
Thats the good part Sambo, There have been no Qualifiers assigned!! That is what the department heads will do for each job position when this gets rolled out and is live. (The ulcer is kicking in buddy) Should I grab the two files that will need to be imported and put them on disk in case we make progress before monday?
sambo
Registered User.
- Local time
- Today, 14:47
- Joined
- Aug 29, 2002
- Messages
- 289
Rick..
Take a look at the new form layout. Placing a continuous subform where your endless comment fields used to lie will give you much more efficiency.
In order to properly link the newly added Job Qualifications it was necessary to add this bit of code in the Before Insert Event of the Sub Form
Me.EmpID = Forms!MainForm!EmpID
This properly links the data on the Sub Form with the Data on the MainForm. There is a way to make Access do this for you (Master/Child Fields) but I prefer to control this myself.
Also, it was necessary to requery the new subform in the MainForm's On Current Event.
Take a look at the new table structure. You will notice that you are now able to add as many Qualifications and Comments as you want for any given tblEmp Record (job).
Normalizing, my friend, that is the power of Database Structuring. Feel free to add/subtract fields to the tblJobQual table. I just gave you a start. Also, you may want to take a close look at the continuous view subform. This allows you to display many records at the same time. It makes the form look just like the old one, only now you've got much more efficiency.
Last thing, take a look at the changes made to the employee form. It was necessary to add a combo box to the DeptNum field that limited the entries to the pre-defined list in tblDept. You don't want users entering some far-out DeptNum that doesn't even exist. This would create an orphan and be no good to anyone.
Good Luck..
Take a look at the new form layout. Placing a continuous subform where your endless comment fields used to lie will give you much more efficiency.
In order to properly link the newly added Job Qualifications it was necessary to add this bit of code in the Before Insert Event of the Sub Form
Me.EmpID = Forms!MainForm!EmpID
This properly links the data on the Sub Form with the Data on the MainForm. There is a way to make Access do this for you (Master/Child Fields) but I prefer to control this myself.
Also, it was necessary to requery the new subform in the MainForm's On Current Event.
Take a look at the new table structure. You will notice that you are now able to add as many Qualifications and Comments as you want for any given tblEmp Record (job).
Normalizing, my friend, that is the power of Database Structuring. Feel free to add/subtract fields to the tblJobQual table. I just gave you a start. Also, you may want to take a close look at the continuous view subform. This allows you to display many records at the same time. It makes the form look just like the old one, only now you've got much more efficiency.
Last thing, take a look at the changes made to the employee form. It was necessary to add a combo box to the DeptNum field that limited the entries to the pre-defined list in tblDept. You don't want users entering some far-out DeptNum that doesn't even exist. This would create an orphan and be no good to anyone.
Good Luck..
Sambo, Two questions please.
1) The list of positions that were in the D/B I sent you are "Predefined" The drop down subform is great but will this work for the predefined list? Even if we could use a dropdown box that would allow us to select from.
2) The position qualifier at the top of the form is the major thorn in my side. Since i had to go in and delete the duplicate job positions fron the table. I will somehow need to be able to add another record to the table With the same position title ONLY if the position qualifier is "Not null". Please let me know if i need to try and explain this part further. Thanks
1) The list of positions that were in the D/B I sent you are "Predefined" The drop down subform is great but will this work for the predefined list? Even if we could use a dropdown box that would allow us to select from.
2) The position qualifier at the top of the form is the major thorn in my side. Since i had to go in and delete the duplicate job positions fron the table. I will somehow need to be able to add another record to the table With the same position title ONLY if the position qualifier is "Not null". Please let me know if i need to try and explain this part further. Thanks
sambo
Registered User.
- Local time
- Today, 14:47
- Joined
- Aug 29, 2002
- Messages
- 289
1) I guess I don't know what you mean by predefined. Currently the combo box selects from the list of available department numbers. This makes it a predefined list.
2) Are you wanting to add new positions in this form?
Example..
Dept# 01.6000 has a position, Staffing Coordinator with job qualifier, 228.
Are you saying you would like to add a position..
Dept# 01.6000, positions Staffing Coordinator, with job qualifier 300?
So everything is the same except the job qualifier.
2) Are you wanting to add new positions in this form?
Example..
Dept# 01.6000 has a position, Staffing Coordinator with job qualifier, 228.
Are you saying you would like to add a position..
Dept# 01.6000, positions Staffing Coordinator, with job qualifier 300?
So everything is the same except the job qualifier.
Sambo, What I am refering to are the multible YES/NO Boxes in the original D/B. The department head will have to select one or more of thse selections to assign to the user.
Also, The job qualifier has changed, If you remember me saying that since this D/B is based on "Position" and not employee that they wanted to do away with the number and make it a Text field. Say the record being created is Department 01.6000, The entity is PWH, the department name is NURSING. The department head is: Barbara Jones(??) the first job position in that department may be a: RN II. Barbara will look at the duties of the RN II position and decide which of the predefined "PHI QUALIFIERS" (The YES/NO Boxes) apply to that position (Not the employee in that positiion) If the PHI Qualifiers that Barbara selects apply to the 4 RN II positions in her department, then fine, entries have to be made once for the RN II position. IF, Barbara's Day shift RN II position has different " PHI Qualifiers" Then Her Evening (or night) RN II position, This is where I need the ability to be able to add another RN II record to the table and specify the Position Qualifier in the blank text box under the "Position" field. I found that based on the design flaws of the D/B, that I was ble to create a another form based on the tables and i was able to add a new record. How Can I have the main form showing with the department data populated and have a second smaller form appear(If anther position qualifier needs to be added) that will have the Department info from the main form already be displayed in the fields of the second form where all that Barbara would need to enter is the additional RN II. I think it needs to be done via a query of some sort from the main form that is opened but i am not sure. Again, I have it working so far but the users are not going to want to have their data already up on the main form and have the need to add another position and then have to scroll through the record set until they come to their department number again. Can you help me with this?
Also, The job qualifier has changed, If you remember me saying that since this D/B is based on "Position" and not employee that they wanted to do away with the number and make it a Text field. Say the record being created is Department 01.6000, The entity is PWH, the department name is NURSING. The department head is: Barbara Jones(??) the first job position in that department may be a: RN II. Barbara will look at the duties of the RN II position and decide which of the predefined "PHI QUALIFIERS" (The YES/NO Boxes) apply to that position (Not the employee in that positiion) If the PHI Qualifiers that Barbara selects apply to the 4 RN II positions in her department, then fine, entries have to be made once for the RN II position. IF, Barbara's Day shift RN II position has different " PHI Qualifiers" Then Her Evening (or night) RN II position, This is where I need the ability to be able to add another RN II record to the table and specify the Position Qualifier in the blank text box under the "Position" field. I found that based on the design flaws of the D/B, that I was ble to create a another form based on the tables and i was able to add a new record. How Can I have the main form showing with the department data populated and have a second smaller form appear(If anther position qualifier needs to be added) that will have the Department info from the main form already be displayed in the fields of the second form where all that Barbara would need to enter is the additional RN II. I think it needs to be done via a query of some sort from the main form that is opened but i am not sure. Again, I have it working so far but the users are not going to want to have their data already up on the main form and have the need to add another position and then have to scroll through the record set until they come to their department number again. Can you help me with this?
sambo
Registered User.
- Local time
- Today, 14:47
- Joined
- Aug 29, 2002
- Messages
- 289
Not quite sure what you mean.
Take a look at the new button "Add Similar" on the MainForm.
This loads up the "tblEmp" Form with Similar INformation to that which is already on the form and allows the user to add a new tblEmp Record (job posting).
Next you will probably want to automatically add similar Job Qualifiers for a newly added job posting if it matches the Current MainForm record.
Man, this is hard to do w/ just textual exchanges like this. I'll bet if we were dealing w/ real human interaction we could have cranked this sucker out in about 15 minutes.
Oh well..
Let me know if this is what you are after and we can work on adding similar Job Qualifications for new entries.
Take a look at the new button "Add Similar" on the MainForm.
This loads up the "tblEmp" Form with Similar INformation to that which is already on the form and allows the user to add a new tblEmp Record (job posting).
Next you will probably want to automatically add similar Job Qualifiers for a newly added job posting if it matches the Current MainForm record.
Man, this is hard to do w/ just textual exchanges like this. I'll bet if we were dealing w/ real human interaction we could have cranked this sucker out in about 15 minutes.
Oh well..
Let me know if this is what you are after and we can work on adding similar Job Qualifications for new entries.
