Looking for keywords to facilitate effective search

  • Thread starter Thread starter Jeffrey O'Donnell via AccessMonster.com
  • Start date Start date
J

Jeffrey O'Donnell via AccessMonster.com

I'm trying to search the forum for a fix to my particular problem but I
don't know the proper terms for what I'm trying to do. So my searches are
not producing the expected results. I'm looking for some keywords.

I have a form that contains typical employee data stuff (names, locations,
sections, and various dates). I'm trying to assign raters and reviewers to
each employee.

I've created a combo box of potential raters that is based on a query that
gets its criteria from the employee's section name displayed in a text box
on a form. (e.g. For an employee who works in the HR section, the rater
combo box displays all other employees assigned to the HR section.) For
the majority of the employees, this query works fine. My problem is with
Section Heads who are rated by someone outside their section. We have 22
possible sections.

I'd like to have the application evaluate the employee's section name. If
SectionName = HR then return all other records that have their section name
containing HR or MGT.
If SectionName = MGT then return all other records that have their section
name containing MGT or EXEC. Again, we have 22 sections.

I don't know what that procedure is called so I don't know what terms to
use when I seach the forum for help. I've tried "If statements", "Case
statements", "Query by variables", "Query with VBA" but they don't seem to
be producing a fix for my problem. So, I figured I must not be asking the
right question.

Any help would be greatly appreciated.
 
Hi Jeffrey,

I'm not sure about the keyword search - at least you gave it a go, though.
Your description of the problem in the appropriate forum would probably
draw more responses.

I'm not very familiar with combo boxes and stuff, but it sounds like you
just want to list the results of a query. In that case, you can just modify
the selection criteria of the underlying query. You're going to need to set
up a new table, though, to store which sections are valid for the various
sections.

For example, I am assuming that your current query looks like:
Select * from Employees Where SectionName = <SelectedSection>

You will need another table, SectionHeadRaters, that looks like this :
SectionHeadName SectionName
HR HR
HR MGT
MGT MGT
MGT EXEC

The query your combo box is based on then becomes:
Select Employees.* from Employees, SectionHeadRaters
where Employees.SectionName = SectionHeadRaters.SectionName
and SectionHeadRaters.SectionHeadName = <SelectedSection>

You may have a problem in that this will return lots of employees even for
those people who wouldn't normally be rated by people outside of their
section, so you may need to further qualify this query depending on an
attribute of the employee that is being rated...
 
David,

I've been working with your suggestion about making another table, but
apparently I'm not comprehending what you're trying to tell me. I don't
think I'm even close. Can you tell me again and please explain it like
you're talking to someone with no experience in MS Access - that's no lie.

Jeff
 
David - I have five tables (tblEmployee, tblSectionID, tblPostID,
tblRaterbySSN, and tblReviewerbySSN) The form is based on tblEmployee and
is entitled frmEER. For my query, I've based it on tblRaterbySSN. This
table has four fields, RaterSSN, RaterName, RaterPost, and RaterSection.
I've set the criteria for RaterSection as [Forms]![frmEER]![SectionName].
The query returns all employees who are assigned to the same section as the
current record's section.
This works fine for the first query, but subsequent queries do not work.
My attempts at requery have failed. It was at this point that I figured I
probably do not need to fix the requery if I use If...then or Case to
include additional section names.
I was attempting to search the forum for similar procedures but was not
having any luck. Figured I was not asking the right questions.
Admittedly, I know enough to get me into trouble but not enough to get me
out.
Jeff
 
Back
Top