If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Equivalent to the Least function in SQL (or min in Excel)
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? |
#2
|
|||
|
|||
Equivalent to the Least function in SQL (or min in Excel)
How about Min and Max just like you use in Excel, these also work in Access...
-- Maurice Ausum "CH" wrote: 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? |
#3
|
|||
|
|||
Equivalent to the Least function in SQL (or min in Excel)
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" wrote in message ... 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? |
#4
|
|||
|
|||
Equivalent to the Least function in SQL (or min in Excel)
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" wrote in message ... 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? |
#5
|
|||
|
|||
Equivalent to the Least function in SQL (or min in Excel)
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" wrote: 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" wrote in message ... 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? . |
#6
|
|||
|
|||
Equivalent to the Least function in SQL (or min in Excel)
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" wrote: 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" wrote in message ... 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? . |
#7
|
|||
|
|||
Equivalent to the Least function in SQL (or min in Excel)
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" wrote in message ... 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" wrote: 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" wrote in message ... 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? . |
#8
|
|||
|
|||
Equivalent to the Least function in SQL (or min in Excel)
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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? . . |
#9
|
|||
|
|||
Equivalent to the Least function in SQL (or min in Excel)
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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? . . |
Thread Tools | |
Display Modes | |
|
|