Lookup keys appear in query instead of desired field

[rjgriffin46]

My Students table has 5 skill fields (skill1, skill2, etc) that lookup to a Skills table for the name of the skill. The Skills table has an auto number key field and a skill name field (eg., Sauteing). When the Students table is open in datasheet mode, or in a simple query of just the Students table, the skill name displays in the 5 Students Table skill fields. But when another query is added to this simple query the skill table (foreign) key is displayed in the 5 Students table skill fields (skill1, skill2, etc).



I would appreciate learning possible causes of this problem and how to display the skill name instead of the skill auto number key in any query, simple or complex.

Thanks

[June7]

Have to include the Skills table in query so related data is available.

I NEVER build lookups in tables because I want to see the actual value in field, not the lookup alias.

Multiple similar name fields (Skill1, Skill2, etc) indicates non-normalized data structure. This will likely cause you headaches down the road.

[orange]

rjgriffin46,

You should review and become familiar with Normalization

[rjgriffin46]

June, Orange:

Thanks for your responses. As happens occasionally, after I rebooted the next day everything worked as I'd originally expected. I think I had many applications opened and was opening and closing different versions of the database resulting in memory issues.

I would be interested in your thoughts on normalization. I could have created a separate Student Skills table linked to the Students table. However, I and my client didn't want an open ended ability to enter an unlimited number of skills for a student. And I didn't want to create another table, so we decided to limit to the 5 skills and I decided to add the fields to the Students table. The other option I considered, since the Students table has a lot of fields, was to create a single record Student Skills table with the 5 skill fields. I'm assuming then I could have limited that table to 1 record per student with a secondary unique index on the student ID. But again, I think I opted for simplicity in not wanting another table.

So, your suggestions on a better approach and what specific headaches I might experience.

Thanks!

[June7]

It is a balancing act between normalization and ease of data entry/output.

Consider a query where you want to find students with Sautéing (cooking school ?) skill. This skill can be in any of 5 fields. Will have to apply search criteria to all five fields. What if you want to find student with Sautéing AND DeepFrying skills?

Review this discussion https://www.accessforums.net/forms/c...ble-54448.html

[rjgriffin46]

I see your point and will evaluate because I will need to a variation on what you suggested. But it seems to me that whether I have 5 fields in 1 record or 5 records with 1 field, I still have to cycle through each to see if there is a match. I guess what you are pointing out, though, is that if I had a separate 1 field skills table, it would be easier to pick out matching students - just a sort of the table would do that...?

Here's is what I am planning to do with the skills data:

There is a counterpoint to the 5 Student skills which is an open job position that also has up to 5 required skills that the employer will define. I used the same method as I did for the Students table, creating 5 fields in the Job Position table record. So I am working on creating a report that will list an open job position from the Job Position table and then all the students looking for a job. It will then compute a "match" score for each student against the open position based on the matching and rating of their 5 skills to the positions 5 skills. We want to be able to list the job seeking students in descending order of their match score for each open position as a aide in directing their job search.

I didn't mention before that for each of the student's and each of the position's skills there is a corresponding rating field (SkillRating1-5). So each of the student's 5 skills will have to be compared to each of the 5 position skills to identify matching skills and then compute a score using the student's (proficiency) and the position's (need) rating for the matching skill.

If you care to, any ideas, guidance or reference on ways to go about this, both given how I've structured the fields and also the way you would have done it, will be most appreciated, as always. I'm thinking that I can create a series of queries for this, perhaps involving creation/updating of (temporary) tables...?

Thanks!

[orange]

rgriffin46,

I agree with June's comment, and would go further.
Adding another table that is part of your "defined business" is not additional effort. Your short cut to save a table and save some time it seems to me, will cause you work arounds as June mentioned.

Do NOT confuse WHAT and HOW.
If you have a tblStudent and a tblSkill, you run the traditional many to many one student can have 1 or Many Skills and, from the Skill perspective, a Skill can be possessed by one or many students. In fact you could easily have a tblSkill that included a Skill(s) that no current student possessed.(a Skill can be possessed by 0,1, or many students.)

Your WHAT now includes --we only allow up to 5 skills per student.
Your HOW was to identify 5 fields in the student table.

Now suppose we resolved the Many to Many by means of a junctionTable -jncStudentHasSkill -
with fields StudentID and SkillID.

Student --->jncStudentHasSkill<---Skill

If you want to restrict a student to 5 skills max, you can do that on a form (before update event check that students skill count) where you could select and assign skills to a given student. You could limit the Skill to be selected/assigned to the current list of Skills in tblSkill. The Primary key or composite unique index of StudentID, SkillID will prevent duplicate assignments. Same WHAT --different HOW.

If you do want to add new Skill(s) to tblSkill, you could have an Admin routine that let you or a defined person modify/append to tblSkill.

Back to June's example, since you have 5 fields any of which could contain the Skill you are looking for and you have to search all 5 fields. If you use the junction table, you do the search against 1 field in a query.

If you change the policy and will now allow 7 Skills per student max, then you have to change your table structure (add 2 more fields).
There is no change with the junction table set up, but you would have to change a line of code to limit your Student Skill count to 7 from the original 5.

If you had a gi-normous file and normalization was causing speed/performance issues, then deNormalize. But, when you are starting with database and Access, Normalize your tables -it will save you from some strange work arounds.

Design your tables based on your business rules. Get the tables designed, tested (desk checked (paper and pencil). Use established database concepts ---Normalization, consistent naming convention, ...

Good luck.

OOops: I just checked back and see that you have added some info. June is correct again Rating in a Skill possessed by the Student would be an attribute in the junction table.
If you have a number of Ratings to discriminate the degree of the skill, then you could have a tblRatings with RatingId and RatingName. Then your jncStudentHasSkill would include a RatingID (FK reference to TblRating corresponding to the Rating assigned to that Skill for that Student)

[June7]

The rating would be another field in tblStudentSkills junction table.

[rjgriffin46]

Thanks so much for these suggestions which I have been evaluating and which I think are sufficient for me to close this post.

I do have another question related to this subject of matching student skills to job position skill needs. I'll state the question and you can advise if this should be a new post.

In trying to rate/score how well a given set of students' skills match a specific (or set of) open job position(s), there is no inherent field on which to join the students table (or query) with the Job Position table. In this case might a Cartesian query using the 2 two tables un-joined work because it would result in a row for each student with the job position information in each row? I should then in this query, or in a subsequent query using the Cartesian as input, be able to create nested IFF expressions to check each of the 5 student skills for matches to the 5 job position skills. And then I am hoping to input this into a report sorted by open job position and for each job position lists all the students selected for the query in descending order of their match score.

The other approach I've been looking at is creating a data macro that could read the student and job position tables (I assume a data macro can't use a query as input) and then loop through the 5 fields to check for matches and calculate scores.

You may have noticed I don't mention VBA only because I've not done any VBA coding as yet, nor any macros for that matter in the 15 months since I started this Student Administration database while learning Access. It seems to me that macros are easier to develop and, as one of my books suggest, might be a good stepping stone to learning VBA.

Again, your thoughts on this and whether this should be a new thread will be appreciated.

[June7]

So you are still talking about 5 skill fields with Cartesian query? Try it and see what happens.

What event data macro would you put this code into? How would you report the results of the code? Looping in macros is not simple https://support.microsoft.com/en-us/kb/90815

My training went straight to VBA coding but I already had an understanding of programming concepts. I have since 'picked up on' macro coding but in general, don't use it.

When you have question on specific issue related to efforts, start a new thread.

[orange]

rjgriffen46,

I noticed in your latest post you have student skills and job required skills.

I have a diagram that is similar in concept -it deals with Employees who have Training and JobPositions that have Training requirements. You can substitute student for Employee and Skill for Training to see some of the relationships and tables involved.

It may be of interest to you.

[rjgriffin46]

The event macro would probably be a button on a form to run the report.

Thanks again for your inputs. I'll close this thread.