Equivalent to the Least function in SQL (or min in Excel)

  • Thread starter Thread starter CH
  • Start date Start date
C

CH

Is there an equivalent to the Least function inSQL (or min in Excel) within
Access?

I'm trying to find the Min value in a series of numbers calculated by an
expression.

for example Min([Field1]-10,[Field2]-10,[Field3]-10)

Also, would there be a similar function to get at the Max as well?
 
If I read between the lines, you are trying to find the minimum "across"
several fields. If true, that's exactly how you'd use ... Excel!

Access isn't a spreadsheet on steroids, so the built-in functions like Min
and Max are based on your data being well normalized, not 'sheet data.

If "relational" and "normalization" are unfamiliar terms, consider brushing
up on them before trying to make Access do something it isn't designed to
do.

And is there any reason you couldn't shove those fields of data into Excel
and use the Min() function there?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Does SQL have a LEAST function? I thought that was just an
ORACLE verb.

You can write your own verbs in Access, using VBA.

If you are using Access, paste this into a module:

Public Function Least(ParamArray mydata())
On Error Resume Next
Dim data_item
Least = Null
For Each data_item In mydata
If (data_item < Least) Or IsNull(Least) Then
Least = data_item
End If
Next
End Function

(david)
 
Jeff - Thanks for taking the time to reply to my post.

Relational, and Normalized are terms I know well. However, I can't imagine
that microsoft would be so closed minded as to think that Access should be
limited to only Normalized data sets.

Every database has a purpose, and can be deemed succesful if it meets the
needs of the user.

No, exporting the data to excel to complete the calculation would not work
for my needs. I am working to add a field to an automated dynamic report.
By the time the data is sent to excel, it is already summarized. The
calculation I am looking to complete will need to be done before the final
level of summarization.

No worries, I will figure it out. There is always a way...I just need to
figure it out. If there is no equivalent to the MIN function...perhaps I
will try some nested IIF statements.




Jeff Boyce said:
If I read between the lines, you are trying to find the minimum "across"
several fields. If true, that's exactly how you'd use ... Excel!

Access isn't a spreadsheet on steroids, so the built-in functions like Min
and Max are based on your data being well normalized, not 'sheet data.

If "relational" and "normalization" are unfamiliar terms, consider brushing
up on them before trying to make Access do something it isn't designed to
do.

And is there any reason you couldn't shove those fields of data into Excel
and use the Min() function there?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CH said:
Is there an equivalent to the Least function inSQL (or min in Excel)
within
Access?

I'm trying to find the Min value in a series of numbers calculated by an
expression.

for example Min([Field1]-10,[Field2]-10,[Field3]-10)

Also, would there be a similar function to get at the Max as well?
Click to expand...


.
Click to expand...
 
Hey David - I'm sure your right. It is Oracle I am hitting when using SQL.

Thanks for the responce below. I will play with adding a module for this.
THANKS!

david said:
Does SQL have a LEAST function? I thought that was just an
ORACLE verb.

You can write your own verbs in Access, using VBA.

If you are using Access, paste this into a module:

Public Function Least(ParamArray mydata())
On Error Resume Next
Dim data_item
Least = Null
For Each data_item In mydata
If (data_item < Least) Or IsNull(Least) Then
Least = data_item
End If
Next
End Function

(david)


CH said:
Is there an equivalent to the Least function inSQL (or min in Excel)
within
Access?

I'm trying to find the Min value in a series of numbers calculated by an
expression.

for example Min([Field1]-10,[Field2]-10,[Field3]-10)

Also, would there be a similar function to get at the Max as well?
Click to expand...


.
Click to expand...
 
I can't speak for Microsoft...

.... but if I asked you to drive nails with a chainsaw, wouldn't you think
that might not be the best use of the tool?<g>

Yes, you can use Access to handle non-normalized data sets. ... and both
you and Access will have to work overtime to overcome data for which it is
not optimized.

So, pay now (normalize your data) or pay later (keep coming up with work
arounds).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CH said:
Jeff - Thanks for taking the time to reply to my post.

Relational, and Normalized are terms I know well. However, I can't
imagine
that microsoft would be so closed minded as to think that Access should be
limited to only Normalized data sets.

Every database has a purpose, and can be deemed succesful if it meets the
needs of the user.

No, exporting the data to excel to complete the calculation would not work
for my needs. I am working to add a field to an automated dynamic report.
By the time the data is sent to excel, it is already summarized. The
calculation I am looking to complete will need to be done before the final
level of summarization.

No worries, I will figure it out. There is always a way...I just need to
figure it out. If there is no equivalent to the MIN function...perhaps I
will try some nested IIF statements.




Jeff Boyce said:
If I read between the lines, you are trying to find the minimum "across"
several fields. If true, that's exactly how you'd use ... Excel!

Access isn't a spreadsheet on steroids, so the built-in functions like
Min
and Max are based on your data being well normalized, not 'sheet data.

If "relational" and "normalization" are unfamiliar terms, consider
brushing
up on them before trying to make Access do something it isn't designed to
do.

And is there any reason you couldn't shove those fields of data into
Excel
and use the Min() function there?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CH said:
Is there an equivalent to the Least function inSQL (or min in Excel)
within
Access?

I'm trying to find the Min value in a series of numbers calculated by
an
expression.

for example Min([Field1]-10,[Field2]-10,[Field3]-10)

Also, would there be a similar function to get at the Max as well?
Click to expand...


.
Click to expand...
Click to expand...
 
We can agree to disagree here : )

All I'm saying is think outside the box. Access is a powerful tool for more
than just relational db functionality. Again, each DB has a purpose, which
should drive DB design. If the purpose of a DB is to automate a report (with
a flat file type data source, which is refreshed each time the report is
run), there is no need to normalize the data as it is refreshed each run.

Access can help manipulate the data (splice & dice many way's), and a report
process can be easily automated with the use of access macros. This in turn,
helps to reduce your excel file size, etc so the end product is user friendly.

Rain coats are made to protect you from the rain, if it's sunny outside,
there is no need to wear the coat.

Just two different ways of thinking...thanks for your time in trying to help
with my issue. I enjoyed the dialog : )

ps...I saw the Microsoft Access MVP in your signature...I see from your
reply ("can't speak for Microsoft") that this does not mean you are a
microsoft ee.


Jeff Boyce said:
I can't speak for Microsoft...

.... but if I asked you to drive nails with a chainsaw, wouldn't you think
that might not be the best use of the tool?<g>

Yes, you can use Access to handle non-normalized data sets. ... and both
you and Access will have to work overtime to overcome data for which it is
not optimized.

So, pay now (normalize your data) or pay later (keep coming up with work
arounds).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CH said:
Jeff - Thanks for taking the time to reply to my post.

Relational, and Normalized are terms I know well. However, I can't
imagine
that microsoft would be so closed minded as to think that Access should be
limited to only Normalized data sets.

Every database has a purpose, and can be deemed succesful if it meets the
needs of the user.

No, exporting the data to excel to complete the calculation would not work
for my needs. I am working to add a field to an automated dynamic report.
By the time the data is sent to excel, it is already summarized. The
calculation I am looking to complete will need to be done before the final
level of summarization.

No worries, I will figure it out. There is always a way...I just need to
figure it out. If there is no equivalent to the MIN function...perhaps I
will try some nested IIF statements.




Jeff Boyce said:
If I read between the lines, you are trying to find the minimum "across"
several fields. If true, that's exactly how you'd use ... Excel!

Access isn't a spreadsheet on steroids, so the built-in functions like
Min
and Max are based on your data being well normalized, not 'sheet data.

If "relational" and "normalization" are unfamiliar terms, consider
brushing
up on them before trying to make Access do something it isn't designed to
do.

And is there any reason you couldn't shove those fields of data into
Excel
and use the Min() function there?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Is there an equivalent to the Least function inSQL (or min in Excel)
within
Access?

I'm trying to find the Min value in a series of numbers calculated by
an
expression.

for example Min([Field1]-10,[Field2]-10,[Field3]-10)

Also, would there be a similar function to get at the Max as well?


.
Click to expand...
Click to expand...


.
Click to expand...
 
Correct, not a 'softie.

And I don't think we're disagreeing. Yes, Access can be used for that.
Yes, I can use my chainsaw to drive nails. No, neither is the optimal tool
for the task specified.

It isn't, in my mind a question of "if", but an issue of "how".

Best of luck on your project!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CH said:
We can agree to disagree here : )

All I'm saying is think outside the box. Access is a powerful tool for
more
than just relational db functionality. Again, each DB has a purpose,
which
should drive DB design. If the purpose of a DB is to automate a report
(with
a flat file type data source, which is refreshed each time the report is
run), there is no need to normalize the data as it is refreshed each run.

Access can help manipulate the data (splice & dice many way's), and a
report
process can be easily automated with the use of access macros. This in
turn,
helps to reduce your excel file size, etc so the end product is user
friendly.

Rain coats are made to protect you from the rain, if it's sunny outside,
there is no need to wear the coat.

Just two different ways of thinking...thanks for your time in trying to
help
with my issue. I enjoyed the dialog : )

ps...I saw the Microsoft Access MVP in your signature...I see from your
reply ("can't speak for Microsoft") that this does not mean you are a
microsoft ee.


Jeff Boyce said:
I can't speak for Microsoft...

.... but if I asked you to drive nails with a chainsaw, wouldn't you
think
that might not be the best use of the tool?<g>

Yes, you can use Access to handle non-normalized data sets. ... and both
you and Access will have to work overtime to overcome data for which it
is
not optimized.

So, pay now (normalize your data) or pay later (keep coming up with work
arounds).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

CH said:
Jeff - Thanks for taking the time to reply to my post.

Relational, and Normalized are terms I know well. However, I can't
imagine
that microsoft would be so closed minded as to think that Access should
be
limited to only Normalized data sets.

Every database has a purpose, and can be deemed succesful if it meets
the
needs of the user.

No, exporting the data to excel to complete the calculation would not
work
for my needs. I am working to add a field to an automated dynamic
report.
By the time the data is sent to excel, it is already summarized. The
calculation I am looking to complete will need to be done before the
final
level of summarization.

No worries, I will figure it out. There is always a way...I just need
to
figure it out. If there is no equivalent to the MIN function...perhaps
I
will try some nested IIF statements.




:

If I read between the lines, you are trying to find the minimum
"across"
several fields. If true, that's exactly how you'd use ... Excel!

Access isn't a spreadsheet on steroids, so the built-in functions like
Min
and Max are based on your data being well normalized, not 'sheet data.

If "relational" and "normalization" are unfamiliar terms, consider
brushing
up on them before trying to make Access do something it isn't designed
to
do.

And is there any reason you couldn't shove those fields of data into
Excel
and use the Min() function there?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Is there an equivalent to the Least function inSQL (or min in Excel)
within
Access?

I'm trying to find the Min value in a series of numbers calculated
by
an
expression.

for example Min([Field1]-10,[Field2]-10,[Field3]-10)

Also, would there be a similar function to get at the Max as well?


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top