MS Access 2003 - Creating a Dashboard - Return Values to a form?

Question

So this is something that I have never tried, but I think I want to create a Metrics Dashboard in my MS Access database. So I think the first question that I need to ask is

how can I return a value to a form? If I ave aggregate queries that are top 10 total sales, how can I specify not only the query that I want the top result to come from, but also the "top/first record" (listing in descending order? should i use a single text box?

i figure that making a subform with a datasheet view will display the entire result of a given query, however I am also wanting to just list the top values of several queries...

thanks, i am sure this will just lead to more questions to follow!

Answer 1

You could use a subform or listbox. The ControlSource property of the listbox and the RecordSource property of the subform can be set at runtime. It is possible to set the number of top values to be returned in Jet SQL. Be aware that Top returns all matching values from the index, so if there are n equal values, Top 1, say, will return n records.

SELECT TOP 10 Field1, Field2 FROM tblTable ORDER BY Field1

Or

SELECT TOP 10% Field1, Field2 FROM tblTable ORDER BY Field1