How do I Create a Microsoft Access Query with column names of Lowest Rate, Average Rate, and Highest Rate?
I am creating my query from a microsoft access table I already have, however, I cannot figure out what the commands are that make my Query make an average, lowest rate, and highest rate, while i am in design view.
put the following into sql view, run the query and it automatically will switch you into design view there after:
select avg(fld) as Average, min(fld) as [Lowest Rate], max(fld) as [Highest Rate] from tbl;
December 27th, 2009 at 12:54 pm
put the following into sql view, run the query and it automatically will switch you into design view there after:
select avg(fld) as Average, min(fld) as [Lowest Rate], max(fld) as [Highest Rate] from tbl;
References :
December 27th, 2009 at 1:26 pm
if you’re in design view, if I remember well, you may right.clicking on the query chart and select "Formulas" or something similar. There will be the Sigma icon.
References :
December 27th, 2009 at 1:42 pm
You need to create a Groups/Total query (by setting the sigma icon from the toolbar in design view).
See article: http://www.about-access-databases.com/select-query.html
The trick is to keep the number of fields (you want to view) to a minimum to avoid the ‘drill-down’ effect and land up with what appears to be repeatable records. In other words, keep an eye on the ‘Group By’ option on each field.
The statistical functions required for your solution is the AVG, MIN and MAX functions for the selected numeric field required.
Hope this helped.
Ben
References :
Access and Excel expert including VBA
http://www.about-access-databases.com
http://www.access-database.com/
http://www.excel-spreadsheet.com/
http://www.trainingpartnership.biz/