Sorting and Ranking

[F1stman]

Hello,

I am working on a database which created financial reports of projects for a
state department of health. The projects must be prioritized for budgetary
purposes. There are two steps to this process.

1)The current system calls for scoring the projects by 6 criteria, totaling
this score, then sorting by the score.

2)Thereafter, the projects must be sorted by previous year priority to break
any ties.

Two Questions:
How do I create the double sorting?
Once I get to this point, how do I priortize the projects (1-?) and SAVE
that priority into the appropriate table?

I know this is confusing but ANY help is appreciated. Many thanks, and
please let me know if you need clarification.

Adam Kemp

 

[KARL DEWEY]

You need to post your table structure stating the table and field names with
their datatype so queries can be suggested.

 

[F1stman]

Karl,

Here is the aforementioned table and structure.

Table: Project Data
Fields:
Project ID (Text – Primary Key)
Criteria 1 (Number, Long Integer)
Criteria 2 (Number, Long Integer)
Criteria 3 (Number, Long Integer)
Criteria 4 (Number, Long Integer)
Criteria 5 (Number, Long Integer)
Criteria 6 (Number, Long Integer)
Previous_Year_Priority (Number, Long Integer) <-- Currently accessed via a
query bring the new priority from the previous year and copied in the form
via a script. May not work with any suggested methods.
New_Priority (Number, Long Integer) <-- This is the value I need to set.

 

[KARL DEWEY]

Several things --
Your data structure is like a spreadsheet and not a relational database. It
should look like this --
Table: Project Data
Project ID (Text – Primary Key)
Criteria (Number, Long Integer)
Rating (Number, Long Integer)
ProjectYear (Number, Long Integer)

You should not store a calculated value - New_Priority - as input values
change so does the validity of the calculation.

This query will revise the data from what you have now to my suggestion --
SELECT [Project ID], 1 AS [Criteria], [Criteria 1] AS Rating, #1/1/2008# AS
ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 2 AS [Criteria], [Criteria 2] AS Rating,
#1/1/2008# AS ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 3 AS [Criteria], [Criteria 3] AS Rating,
#1/1/2008# AS ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 4 AS [Criteria], [Criteria 4] AS Rating,
#1/1/2008# AS ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 5 AS [Criteria], [Criteria 5] AS Rating,
#1/1/2008# AS ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 6 AS [Criteria], [Criteria 6] AS Rating, 2008 AS
ProjectYear
FROM [Project Data];
Save the query as ProjCriteria.

SELECT [Project ID], Sum([Rating]) AS SumOfRating
FROM ProjCriteria
WHERE [ProjectYear] = 2008
GROUP BY [Project ID];
Save the query as ProjCriteriaSum.

Then use the query below to rank.

SELECT Q.[Project ID], Q.Points, (SELECT COUNT(*) FROM ProjCriteriaSum Q1
WHERE Q1.[Project ID] = Q.[Project ID]
AND Q1.[SumOfRating] < Q.[SumOfRating])+1 AS Rank
FROM ProjCriteriaSum AS Q
ORDER BY Q.[Project ID], Q.[SumOfRating];

 

[Nicholas Meyer]

cool

 

[F1stman]

Karl,

Thanks for all that great help. The reason the New_Priority is a field to be
stored is that there are times it must be manually overwritten. Please advise
on this issue if you get a chance.

Adam

Several things --
Your data structure is like a spreadsheet and not a relational database. It
should look like this --
Table: Project Data
Project ID (Text – Primary Key)
Criteria (Number, Long Integer)
Rating (Number, Long Integer)
ProjectYear (Number, Long Integer)

You should not store a calculated value - New_Priority - as input values
change so does the validity of the calculation.

This query will revise the data from what you have now to my suggestion --
SELECT [Project ID], 1 AS [Criteria], [Criteria 1] AS Rating, #1/1/2008# AS
ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 2 AS [Criteria], [Criteria 2] AS Rating,
#1/1/2008# AS ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 3 AS [Criteria], [Criteria 3] AS Rating,
#1/1/2008# AS ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 4 AS [Criteria], [Criteria 4] AS Rating,
#1/1/2008# AS ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 5 AS [Criteria], [Criteria 5] AS Rating,
#1/1/2008# AS ProjectYear
FROM [Project Data]
UNION SELECT [Project ID], 6 AS [Criteria], [Criteria 6] AS Rating, 2008 AS
ProjectYear
FROM [Project Data];
Save the query as ProjCriteria.

SELECT [Project ID], Sum([Rating]) AS SumOfRating
FROM ProjCriteria
WHERE [ProjectYear] = 2008
GROUP BY [Project ID];
Save the query as ProjCriteriaSum.

Then use the query below to rank.

SELECT Q.[Project ID], Q.Points, (SELECT COUNT(*) FROM ProjCriteriaSum Q1
WHERE Q1.[Project ID] = Q.[Project ID]
AND Q1.[SumOfRating] < Q.[SumOfRating])+1 AS Rank
FROM ProjCriteriaSum AS Q
ORDER BY Q.[Project ID], Q.[SumOfRating];

--
KARL DEWEY
Build a little - Test a little

Karl,

Here is the aforementioned table and structure.

Table: Project Data
Fields:
Project ID (Text – Primary Key)
Criteria 1 (Number, Long Integer)
Criteria 2 (Number, Long Integer)
Criteria 3 (Number, Long Integer)
Criteria 4 (Number, Long Integer)
Criteria 5 (Number, Long Integer)
Criteria 6 (Number, Long Integer)
Previous_Year_Priority (Number, Long Integer) <-- Currently accessed via a
query bring the new priority from the previous year and copied in the form
via a script. May not work with any suggested methods.
New_Priority (Number, Long Integer) <-- This is the value I need to set.