sorting formula

[dastr]

Hi all,

I have the following table:

Name Value Priority
Anna 2 A
Anna 1 B
Anna 3 C
Anna 9 D
Anna 4 E
Anna 22 F
Anna 114 G
Anna 0 X

I am thinking of some formula that would give me the value according to the priority column with the highest priority being A, lowest – x. Logic If I have Anna value 2 and Anna value 114; I want only Anna value 2 coz it is higher in the priority column. If I have Anna value 0 and Anna value 114, I would like to have Anna 114, coz it is higher acording to priority? The Priorities can be only the ones listed above. Can any help me out here?

Thanks in advance.

 

[plog]

Sorting means the arrangement of your data. It sounds like you want to create a query that excludes some of your records, is that correct?

In either case, based on the sample data you have already provided can you tell me what the finished result should look like?

 

[dastr]

Hi there,

Thx for yr reply, well for example if I have:

Name Value Priority

Anna 3 C
Anna 9 D
Anna 4 E
Anna 22 F

I would like to get as a final result Anna 3 coz it is a higher priority - C (priorities start from A - to X alphabetically)


if I have :

Name Value Priority

Anna 22 F
Anna 114 G
Anna 0 X


I would like to have Anna 22 .

thanks

 

[pr2-eugin]

Create a Query to sort the data according to that Priority field and use DLookUp on the Query?

 

[plog]

First the admonishment: 'Name' and 'Value' are bad field names because they are reserved words in Access (http://support.microsoft.com/kb/286335). You should rename those fields--I suggest prefixing them with something relevant to the table (i.e. CustomerName, EmployeeName, etc).

I don't know your table's name, so I am using 'YourTableNameHere'--whenever you see that in the code below replace it with your table's actual name. First create a sub-query to determine each person's highest priority. Use this SQL:

  SELECT [Name], Min(Priority) AS HighestPriority
FROM YourTableNameHere
GROUP BY [Name];

Save the above query as 'sub_HighestPriority'. Then you need to use that sub-query in another query along with your initial table to find the exact record that matches that highest priority:

SELECT *
FROM YourTableNameHere INNER JOIN sub_HighestPriority ON [YourTableNameHere].[Name]=[sub_HighestPriority].[Name] AND [YourTableNameHere].[Priority] = [sub_HighestPriority].[HighestPriority];