same part, diferent revision (selecting records)

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

I have the folling data on my table

Part, Rev
ABC, 000
CDD, 000
CDD, 00A
DDD, 000
DDD, 00A
DDD, 00B
FFF, 000

I need to select the parts with the highest Rev level

Part, Rev, Update
ABC, 000, Y
CDD, 000, N
CDD, 00A, Y
DDD, 000, N
DDD, 00A, N
DDD, 00B, Y
FFF, 000, Y

I would really apreciate if someone can help me with this problem.

Regards,

Bre-x
 
Try this ---
SELECT [bre-x].Part, Max([bre-x].Rev) AS MaxOfRev,
IIf(Min([Rev])=Max([Rev]),"N","Y") AS [Update]
FROM [bre-x]
GROUP BY [bre-x].Part;
 
Karl,
Thank you for answering my post. This is may query (according to your
suggestion)

SELECT
sku.Part,
Max(sku.Rev) AS MR,
IIf(Min([Rev])=Max([Rev]),"N","Y") AS [Update]
FROM sku
GROUP BY sku.Part;

The result:

Part MR Update
ABC 000 N
CDD 00A Y
DDD 00B Y
FFF 000 N

Does not quite work

:)

Regards,

Bre-x



KARL DEWEY said:
Try this ---
SELECT [bre-x].Part, Max([bre-x].Rev) AS MaxOfRev,
IIf(Min([Rev])=Max([Rev]),"N","Y") AS [Update]
FROM [bre-x]
GROUP BY [bre-x].Part;

--
KARL DEWEY
Build a little - Test a little


Bre-x said:
I have the folling data on my table

Part, Rev
ABC, 000
CDD, 000
CDD, 00A
DDD, 000
DDD, 00A
DDD, 00B
FFF, 000

I need to select the parts with the highest Rev level

Part, Rev, Update
ABC, 000, Y
CDD, 000, N
CDD, 00A, Y
DDD, 000, N
DDD, 00A, N
DDD, 00B, Y
FFF, 000, Y

I would really apreciate if someone can help me with this problem.

Regards,

Bre-x
Click to expand...
Click to expand...
 
Try these two queries --
bre-x_Rev ----
SELECT [bre-x].Part, Max([bre-x].Rev) AS MaxOfRev
FROM [bre-x]
GROUP BY [bre-x].Part;

SELECT [bre-x].Part, [bre-x].Rev, IIf([Rev]=[MaxOfRev],"Y","N") AS Expr2
FROM [bre-x] INNER JOIN [bre-x_Rev] ON [bre-x].Part = [bre-x_Rev].Part
GROUP BY [bre-x].Part, [bre-x].Rev, IIf([Rev]=[MaxOfRev],"Y","N")
ORDER BY [bre-x].Part, [bre-x].Rev;

--
KARL DEWEY
Build a little - Test a little


Bre-x said:
Karl,
Thank you for answering my post. This is may query (according to your
suggestion)

SELECT
sku.Part,
Max(sku.Rev) AS MR,
IIf(Min([Rev])=Max([Rev]),"N","Y") AS [Update]
FROM sku
GROUP BY sku.Part;

The result:

Part MR Update
ABC 000 N
CDD 00A Y
DDD 00B Y
FFF 000 N

Does not quite work

:)

Regards,

Bre-x



KARL DEWEY said:
Try this ---
SELECT [bre-x].Part, Max([bre-x].Rev) AS MaxOfRev,
IIf(Min([Rev])=Max([Rev]),"N","Y") AS [Update]
FROM [bre-x]
GROUP BY [bre-x].Part;

--
KARL DEWEY
Build a little - Test a little


Bre-x said:
I have the folling data on my table

Part, Rev
ABC, 000
CDD, 000
CDD, 00A
DDD, 000
DDD, 00A
DDD, 00B
FFF, 000

I need to select the parts with the highest Rev level

Part, Rev, Update
ABC, 000, Y
CDD, 000, N
CDD, 00A, Y
DDD, 000, N
DDD, 00A, N
DDD, 00B, Y
FFF, 000, Y

I would really apreciate if someone can help me with this problem.

Regards,

Bre-x
Click to expand...
Click to expand...
Click to expand...
 
Back
Top