Ranking

[jacobe2008]

I have a table with 2 fields (Department_Name, and the responsibilities for
each department):

Department_Name Responsibilities
Dept1 a, b, c
Dept2 d
Dept3 e, f


I have a second table, that ranks each responsibility by importance:

Responisbility Importance
a 1
b 2
c 3
d 4
e 5
f 6

The problem is, I would like to rank the departments based on the ranking of
the responsibilities in the department. That is, I want to have a final
table like this:

Department Importance
Dept1 1
Dept2 4
Dept3 5

(Dept1 gets an importance of 1 because of responsibility a, Dept2 gets an
importance of 4 because of responsibility d, and Dept3 gets an importance of
5 because of responsibility e)

I would appreciate the help. And thanks in advance!

 

[John Spencer]

SELECT DepartmentName, Responsibilities,
Importance
FROM Table1 INNER JOIN Table2
ON Table1.Responsibilities like Table2.Responsibility & "*"

or
--Add both tables to a query with no join
-- Add the fields you want to see
-- under the field Responsibilities add the criteria
LIKE [NameofTable2].[Responsibility} & "*"

This should work as long as the responsibilities field always lists the
responsibilities in order.

No time to complain about your mis-designed field holding more than one
piece of data and why that is bad and why you should fix it and how you
should fix it.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..