Sort via form

[cho]

Hi all,
I have a query that have about 5 numeric field.
Called A,B,C,D,E.

I want to create a form that can be used as the sorter
of the query.For example,
I want to sort the query descently base to the field A.
But the sort criteria inserted from my form.

I have a combo box contains all the fields names in my form,
2 radio option,and two command button.Would somebody
advise me how to do that.?

 

[Al Campagna]

cho,
I'm not sure I understand, but let me offer this.
On a form with five numeric fields, and those controls are enabled, you can simply
right click on any one of the fields, and select Sort Ascending or Sort Descending. That
will override the sorting of your form query, while the form remains open.
You could then right click another field, and sort by that, and so on and so on.
When the form is first opened, the sorting will be according to the form's query, from
there on, you can sort by any field you choose.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[cho]

You're right Al,
But it is not like what you imagine,
the query is not for a form but a datasource
of a chart.So I want the chart enable to display
the data base to 5 different fields.
or
if it's possible,when the user select a field name from
the combo box,it will be added to the query design grid
and sort descently or ascently.But descently is a priority.

 

[Al Campagna]

cho,
If I understand corectly... you have one chart that you would like to use to use 5
different recordsets (one at a time), and also sort those recordsets Asc or Desc.
I'm also assuming this chart is on a form...
That would have to be done through the charts RowSource property, which you can switch
on the fly.

For ex. let's just do a chart against recordset1...
Create a query that works to display Chart1 properly (Asc), and create a string
variable that equates to that SQL statement.
Dim Chart1Asc as String
Chart1Asc = "the SQL statement for Chart1Asc"

Create another query that works to display Chart1 properly (Desc).
Dim Chart1Desc as String
Chart1Desc = "the SQL statement for Chart1Desc"

Now it's just a matter of swapping the SQL statements into your chart's RowSource via
some Button click, or Option Group selection, etc... etc..
You could use a IF statement against an Option Group, with option values (for example)
of 1 or 2
If ChartOption = 1 Then
MyChart.RowSource = Chart1Asc
ElseIf ChartOption = 2 Then
MyChart.RowSource = Chart1Desc
ElseIf etc.. etc... for all poermutations of the Rowsource.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[cho]

Sorry Al,
I'm still confusing with this.Please check the SQL below :

SELECT TOP 10 Format([Date],"mmm") AS [Month], Format([Date],"yyyy") AS
[Year], [DC Fan Information Centre].[Model], Sum([DC Fan Information
Centre].[Striking]) AS SumOfStriking
FROM [DC Fan Information Centre]
GROUP BY Format([Date],"mmm"), Format([Date],"yyyy"), [DC Fan Information
Centre].[Model]
HAVING (((Format([Date],"mmm"))=[Forms]![fltr Reject Rank By Reject
Item]![cboMonth]) AND ((Format([Date],"yyyy"))=[Forms]![fltr Reject Rank By
Reject Item]![cboYear]) AND ((Sum([DC Fan Information
Centre].Striking))<>0))
ORDER BY Sum([DC Fan Information Centre].[Striking]) DESC;

It works fine now but I want one more event.
Did you see the field named 'striking'.I have more 20 fields in my table.
And I want when the user choose a fields from my combo box,
it will replace the fields striking from this query.How to do that?

 

[John W. Vinson]

Did you see the field named 'striking'.I have more 20 fields in my table.
And I want when the user choose a fields from my combo box,
it will replace the fields striking from this query.How to do that?

Short answer: write VBA code to rebuild your entire SQL string for the Update
query in code in the afterupdate event of the combo box.

Full answer: Normalize your data. Having to select a fieldname as if it were
data clearly indicates that your table is not properly normalized. You should
NOT be storing data in fieldnames!

John W. Vinson [MVP]

 

[cho]

John,
I do this cause need to retrieve data and dispaly it in a chart.
Would you like to help me with the code or would like to offer
a better idea.

 

[Al Campagna]

cho,
John is correct. Your data is not normalized. Normalizing your data "is" not only a
better idea... it's the best idea!
If you don't normalize, you'll continue to have great difficulty in evaluating your
data in subsequent queries, and reports.

Because of that fact, you'll (in effect) have to create 20 SQL statements... one for
each of 20 possible "fields" used in each chart.... rather than just changing the value in
your criteria against one normalized field in your form's combo box.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[John W. Vinson]

John,
I do this cause need to retrieve data and dispaly it in a chart.
Would you like to help me with the code or would like to offer
a better idea.

I'd be glad to help restructure your table so that it is correctly normalized.
What is the current structure of your table, and what are you trying to
accomplish? Remember that I cannot see either your table nor your desired
chart.

John W. Vinson [MVP]