DMIN In a query

[nazzoli]

This is what I am looking for: I have a query that is built like this
Month1 Month2 Month3 Month4 Month5 Month6 MinMonth
1 4 5 8 10 12 DMIN

In a new column I want to find the minimum of the months. How would I build
this in a query?

 

[aaron.kempf]

if you were using Access Data Projects then you could just use a simple
subquery.

-Aaron

 

[John Vinson]

This is what I am looking for: I have a query that is built like this
Month1 Month2 Month3 Month4 Month5 Month6 MinMonth
1 4 5 8 10 12 DMIN

In a new column I want to find the minimum of the months. How would I build
this in a query?

Is this a Crosstab query? Hopefully you don't have *table* fields
named Month1, Month2 etc.!

DMin() works across *records*, not across fields. You'll need to write
a custom function to loop through the field values and return the
minimum. Or, with a properly normalized table, you should be able to
put an expression in a query to calculate it - it'll be difficult in
your current query!

John W. Vinson[MVP]

 

[David F Cox]

questions:

what do you want MinMonth to show - Month1 or 1 ?
What is the query that gives the answer as you have shown it?
Do Month1 etc correspond to fields in a table?
Or are they the result of a query?

 

[nazzoli]

I built this query in design mode. Month1, Month2, etc are all expressions
that I built into the query based off of another record. I just need to
build another expression that will take the minimum number within those
months.

 

[nazzoli]

I want MinMonth to show whatever value is the smallest between the 6 values
in the month columns. The 6 months have a IIf expression built into them to
give me a value of the number of days associated with it.