irunergoiam
Registered User.
- Local time
- Yesterday, 19:02
- Joined
- May 30, 2009
- Messages
- 76
I'm finding that I'm in over my head on this as I don't believe it's possible to do with a query what I describe below. I'm sure that someone far wiser than me out there on the forum will know of a module or some other trick that can help me with the below dilemma. I have attached a sample database where I have one query (qryPositionInfo) that I would like to make look like the other query (qryDesiredResult) with a new column (PositionLevel) in it:
I'm trying to build a query that looks at the field FTE and compares it to the FTE fields of the other records in the query. I would like the record with the largest value in the FTE field (where the Retain/Vacate field is not equal to "Vacate") to have it display the number 1 in the PositionLevel field for that record. I then need it to loop through the remaining records and perform the same analysis, assigning a number 2 and so on until all the records (where the Retain/Vacate field is not equal to "Vacate") have this kind of numerical ranking.
If the FTE field from one record is equal to the FTE field in another (or more) record(s), I would like it to then compare the Position fields between those records and where the Position field has the smallest value (based on the first 12 characters of the field), I would like it to assign the next sequential number.
Where the Retain/Vacate field is equal to "Vacate", it is okay to simply display "N/A".
Much thanks to anyone who might offer their expertise and assistance.
I'm trying to build a query that looks at the field FTE and compares it to the FTE fields of the other records in the query. I would like the record with the largest value in the FTE field (where the Retain/Vacate field is not equal to "Vacate") to have it display the number 1 in the PositionLevel field for that record. I then need it to loop through the remaining records and perform the same analysis, assigning a number 2 and so on until all the records (where the Retain/Vacate field is not equal to "Vacate") have this kind of numerical ranking.
If the FTE field from one record is equal to the FTE field in another (or more) record(s), I would like it to then compare the Position fields between those records and where the Position field has the smallest value (based on the first 12 characters of the field), I would like it to assign the next sequential number.
Where the Retain/Vacate field is equal to "Vacate", it is okay to simply display "N/A".
Much thanks to anyone who might offer their expertise and assistance.
