Solved Retrieving the record with the highest column field number value or return "No Value" if exist....

[6thDAY]

I have a simple table containing sample data like so:

Color	Purity
Red	10
Red	3
Red	9
Red	No Value
Red	12
Red	11

I'm trying to run a query where if there are multiple of the same Color, pick the record that has the highest Purity value, but ONLY if there doesn't exist a record that contains a Purity = No Value.

For example, in the above, I would like to return the record:

Color	Purity
Red	No Value

Here's my SQL:

SELECT TOP 1 Color, Purity
FROM Table1
WHERE Purity <> 'No Value' AND Color = [Table1].[Color]
ORDER BY CInt(IIF(Purity='No Value','No Value',Purity)) DESC;

But what I'm getting is:

If I have the following table with no No Value:

Color	Purity
Red	10
Red	3
Red	9
Red	12
Red	11

Then I get the correct return Query record:

Color	Purity
Red	12

The data type of Purity is a Short Text. How can I set the query to return the record if there is a 'No Value' Purity, otherwise return the record with the highest value?

 

[arnelgp]

you need to Create 3 queries:
1. Query1:
SELECT TOP 1 Table1.Color, Table1.Purity
FROM Table1
WHERE (((DCount("1","Table1","Color = '" & [Color] & "' And Purity = 'No Value'"))=0))
ORDER BY Val(Purity) DESC;

2. Query2:
SELECT TOP 1 Table1.Color, Table1.Purity
FROM Table1
WHERE Purity="No Purity";

3. your Final Query using Union Query:
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2;

 

[6thDAY]

Instead of using 3 queries, I did this:

SELECT TOP 1 Color, Purity
FROM Table1
ORDER BY CInt(IIF(Purity='No Value',1000,Purity)) DESC;

Where 1000 is guarantee to be the highest value even though it will never reach it, and I was able to return the record containing No Value. Is this OK code implementation or is it more of a backdoor hack to "make" it work?

 

[arnelgp]

correction on Query2:

Where Purity = "No Value";

 

[arnelgp]

imho, you use Val()

?Val("No Value") = 0
?Val("12") = 12

 

[Gasman]

@arnelgp
SHouldn't that be

correction on Query2:

Where Purity = "No Value";

Ah, was just going to ask you that

 

[arnelgp]

Ah, was just going to ask you that

purity without purity is unimaginable.

 

[6thDAY]

imho, you use Val()

?Val("No Value") = 0
?Val("12") = 12

Apologies, I don't follow you in relation to my "hack" solution?