How to grab the Minimum value from various fields on MS Access? 2

[suiliclic]

Dear Forum,
This is an MS Access Query Syntax question.

I need help with writing the syntax to accompolish the following:

[Field 1], [Field 2] & [Field 3] are all displaying on the same row.

Field 1 Value = 1
Field 2 Value = 2
Field 3 Value = 3

I want to write a syntax will grab value from [Field 1] since it has the least amount. (The "Total-Min" Function on Access not working for me).

So I tried something like...
expr1: Min([Field 1 Value],[Field 2 Value],[Field 3 Value]) but it doesn't work. Any idea?

I can accompolish this easily on Excel by writing something like "min(C1:E1)" but is there anyway to do the same thing on MS Access?

Thank you very much. Your inputs/suggestion would certainly brigthen up my dull working day.




Zabrina from the SF BayARea
Just another humble analyst & VBA newbie~

 

[lespaul]

let's say there's another field that's the PK of the table, ID:

SELECT ID, [Field 1] As FieldValueName From TableName
UNION
SELECT ID, ]Field 2] From TableName
UNION
SELECT ID, ]Field 3] From TableName

save this query (qryNormal), it will normalize your data.

Run this:

SELECT ID, Min(FeildValueName) From qryNormal GROUP BY ID



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[lespaul]

Additionally, by storing this data in this format within a database, you are "committing spreadsheet", you should search the Access fora for normalization and rethink your design if possible.

leslie

 

[suiliclic]

Thanks Leslie,

Would you please look at my query syntax and see what's wrong with it? I tried to follow your suggestion by creating these two queries on SQL view and I got got an error.

(qryNormal)
SELECT ID, [DISCH_DIFF1] From [NameSSNraw]
UNION SELECT ID, [DISCH_DIFF2] From [NameSSNraw]
UNION SELECT ID, [DISCH_DIFF3] From [NameSSNraw];

then I ran....this query...
SELECT ID, Min( [DISCH_DIFF1], [DISCH_DIFF2], [DISCH_DIFF3] )
FROM qryNormal
GROUP BY ID;

It gave me an error saying "Wrong Number of Argument used in function with query expression: Min( [DISCH_DIFF1], [DISCH_DIFF2], [DISCH_DIFF3] ).

Why can't I see the min value among these 3 fields?
Thanks again!


Zabrina from the SF BayARea
Just another humble analyst & VBA newbie~

 

[lespaul]

your first query needs a column qualifer in the first select clause:

SELECT ID, [DISCH_DIFF1] As [DISCH_DIFF] From [NameSSNraw]
UNION SELECT ID, [DISCH_DIFF2] From [NameSSNraw]
UNION SELECT ID, [DISCH_DIFF3] From [NameSSNraw];

then your second query needs to select that field name:

SELECT ID, Min([DISCH_DIFF])
FROM qryNormal
GROUP BY ID;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

Another way is to create the following function in a standard code module:

'A generic function to get the min value of an arbirtrary numbers of same type values:
Public Function myMin(ParamArray Args())
Dim i As Long, rv
rv = Args(LBound(Args))
For i = 1 + LBound(Args) To UBound(Args)
  If IsNull(rv) Or rv > Args(i) Then rv = Args(i)
Next
myMin = rv
End Function

And then, in the query grid:
expr1: myMin([Field 1 Value],[Field 2 Value],[Field 3 Value])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244