Min and Max

[nathanmav]

Hi everyone,

I have a field name RefNo, RevNo check the picture below:

what I want is display "LT" if Latest and "SS" if obsolete.

This is the formula that Im trying to get the result I want

Latest: IIf(Max([RevNo]=0),"LT",IIf(Max([RevNo]>1),"LT","SS"))

Sorry for the explanation. Thank you in advance

 

[theDBguy]

Hi. "Latest" usually involves a sequence, like time or an order. However, I can't determine, from your image, which column would identify this sequence to be able to tell which one is the latest.

 

[nathanmav]

Hi. "Latest" usually involves a sequence, like time or an order. However, I can't determine, from your image, which column would identify this sequence to be able to tell which one is the latest.

This are the latest based on the picture.
refno revno
ax1 3 LT
ax2 0 LT
ax3 0 LT
ax4 1 LT

actually i have an formula in excel and i want to apply in ms access.
here is the formula:
COUNTIFS(A1:A10,A1)-1 it will count refno and then -1

 

[theDBguy]

This are the latest based on the picture.
refno revno
ax1 3 LT
ax2 0 LT
ax3 0 LT
ax4 1 LT

actually i have an formula in excel and i want to apply in ms access.
here is the formula:
COUNTIFS(A1:A10,A1)-1 it will count refno and then -1

Hi. Okay, if you're saying that whichever record has the largest value in RevNo is considered to be the "latest," then we can try something like this:

SELECT T1.*, Nz(SQ.MaxOfRevNo,"SS") As Latest
FROM TableName T1
LEFT JOIN (SELECT T2.RefNo, Max(T2.RevNo)
    FROM TableName T2
    GROUP BY T2.RefNo) SQ
ON T1.RefNo=SQ.RefNo
   AND T1.RevNo=SQ.RevNo
ORDER BY T1.RefNo, T1.RefNo

(untested)
Hope it helps...

 

[Cronk]

Latest: IIf(Max([RevNo]=0),"LT",IIf(Max([RevNo]>1),"LT","SS"))

I wonder if that is what you really want. Resulting values are
RevNo <0 SS
RevNo = 0 LT
RevNo >0 and RevNo <=1 SS
RevNo >1 LT


If RevNo is always an integer then a simpler formula is
Latest: IIf(Max([RevNo])>1,"LT","SS")

 

[nathanmav]

Hi. Okay, if you're saying that whichever record has the largest value in RevNo is considered to be the "latest," then we can try something like this:

SELECT T1.*, Nz(SQ.MaxOfRevNo,"SS") As Latest
FROM TableName T1
LEFT JOIN (SELECT T2.RefNo, Max(T2.RevNo)
    FROM TableName T2
    GROUP BY T2.RefNo) SQ
ON T1.RefNo=SQ.RefNo
   AND T1.RevNo=SQ.RevNo
ORDER BY T1.RefNo, T1.RefNo

(untested)
Hope it helps...

theDBguy,

I tried your query but i get an error of "enter parameter value of SQ.MaxofRevNo and SQ.RefNo

Thanks

 

[nathanmav]

Latest: IIf(Max([RevNo]=0),"LT",IIf(Max([RevNo]>1),"LT","SS"))

I wonder if that is what you really want. Resulting values are
RevNo <0 SS
RevNo = 0 LT
RevNo >0 and RevNo <=1 SS
RevNo >1 LT


If RevNo is always an integer then a simpler formula is
Latest: IIf(Max([RevNo])>1,"LT","SS")

Cronk,
Highest value is always the latest if 0 is the last value it should be the latest.

I tried your formula and the result is different.

Thanks

 

[arnelgp]

create a query (say query1):

select RefNo, Max(RevNo) As RefNo from yourTable Group by RefNo;

now create yet another query (query2), joining query1 and yourTable:

SELECT Query1.RefNo, Query1.RevNo, yourTable.latest
FROM Query1 INNER JOIN yourTable ON (Query1.RefNo = Table1.RefNo) AND (Query1.RevNo = yourTable.RevNo);

the last query is the one you will need.

 

[nathanmav]

create a query (say query1):

select RefNo, Max(RevNo) As RefNo from yourTable Group by RefNo;

now create yet another query (query2), joining query1 and yourTable:

SELECT Query1.RefNo, Query1.RevNo, yourTable.latest
FROM Query1 INNER JOIN yourTable ON (Query1.RefNo = Table1.RefNo) AND (Query1.RevNo = yourTable.RevNo);

the last query is the one you will need.

hi arnelgp,

I followed your instruction and make two queries the result is what i am looking for except that is it possible to display "LT" in the latest field

Thanks

 

[arnelgp]

you mean regardless of the field value in the [Latest] field? then you only need 1 query:

select RefNo, Max(RevNo) As RefNo , "LT" As Latest from yourTable Group by RefNo;

 

[arnelgp]

still if this is not what you want, as I read your first post, add a calculated field in the query:

select RefNo, Max(RevNo) As RefNo , Count(1) As Expr1, Iif([Expr1]>1,"LT", "SS") As Latest from yourTable Group by RefNo;

 

[nathanmav]

arnelgp,

Its working and this is what i need.. Thank you very much

 

[arnelgp]

you're welcome friend.
may we know which query works for you?

 

[nathanmav]

arnelgp,

This query:

select RefNo, Max(RevNo) As RefNo , Count(1) As Expr1, Iif([Expr1]>1,"LT", "SS") As Latest from yourTable Group by RefNo;