top value question

  • Thread starter Thread starter Ramone
  • Start date Start date
R

Ramone

Hello All,

I'm trying to see how the following could be accomplished:

I have a query that sorts dollar amounts and i would like
to create a field that assings a 1 to the top value and 0
to all other values. I was trying to use this statement

iif([amount]= top 1,1,0)

Thanks for any help
 
Ramone
This may not be the most elegant solution, but this worked in my little test

You need 2 queries
First query finds the max number. Call it qryFindMax

SELECT Max([tblMyTable]![Field1]) AS Expr
FROM tblMyTable

Second Query joins your original table with the qryFindMax, and creates a calculation that inserts a 1 for the Max value, a 0 for all other values

SELECT tblMyTable.Field1, IIf([tblMyTable]![Field1]=[qryMax]![Expr1],1,0) AS Expr
FROM tblMyTable LEFT JOIN qryMax ON tblMyTable.Field1 = qryMax.Expr1

Of course you change the table and filed names to suit your needs

Hope this help
Rosco
----- Ramone wrote: ----

Hello All

I'm trying to see how the following could be accomplished

I have a query that sorts dollar amounts and i would like
to create a field that assings a 1 to the top value and 0
to all other values. I was trying to use this statemen

iif([amount]= top 1,1,0

Thanks for any help
 
Thanks for the reply Rosco, I created a similar query. I
was trying to do it in 1 query instead of 2.

Thanks again for your time.


Ramone


-----Original Message-----
Ramone,
This may not be the most elegant solution, but this worked in my little test.

You need 2 queries.
First query finds the max number. Call it qryFindMax:

SELECT Max([tblMyTable]![Field1]) AS Expr1
FROM tblMyTable;

Second Query joins your original table with the
Click to expand...
qryFindMax, and creates a calculation that inserts a 1
for the Max value, a 0 for all other values.
SELECT tblMyTable.Field1, IIf([tblMyTable]![Field1]= [qryMax]![Expr1],1,0) AS Expr2
FROM tblMyTable LEFT JOIN qryMax ON tblMyTable.Field1 = qryMax.Expr1;

Of course you change the table and filed names to suit your needs.

Hope this helps
Rosco
----- Ramone wrote: -----

Hello All,

I'm trying to see how the following could be accomplished:

I have a query that sorts dollar amounts and i would like
to create a field that assings a 1 to the top value and 0
to all other values. I was trying to use this statement

iif([amount]= top 1,1,0)

Thanks for any help

.
Click to expand...
 
Back
Top