Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Find most popular field, second most popular field etc
- Thread starter isymo
- Start date
MichaelRed
Programmer
You COULD try a crosstab query, although it would take away the "pleasure" of the seperate queries.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
You could also use an Aggregate query, where you "Group By" the field in question, and also "Count" the same field. You might want to then ORDER BY that field in descending order, in order to present the data in "waterfall" format; eg.
SELECT SomeField, COUNT(SomeField)
FROM SomeTable
GROUP BY SomeField
ORDER BY SomeField
To avoid using a crosstab query, you could go into the OLE Chart object (double click on it), and set the "Data", "Series in Columns" menu options.
Either way, certainly no reason to need to use multiple queries. This method also avoids requiring a crosstab.
Cheers,
Steve
SELECT SomeField, COUNT(SomeField)
FROM SomeTable
GROUP BY SomeField
ORDER BY SomeField
To avoid using a crosstab query, you could go into the OLE Chart object (double click on it), and set the "Data", "Series in Columns" menu options.
Either way, certainly no reason to need to use multiple queries. This method also avoids requiring a crosstab.
Cheers,
Steve
MichaelRed
Programmer
... but... but ... why "avoid" the crosstab?
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
...because ...because
(a) I presented my solution as an alternative; not necessarily better or worse.
(b) "Avoid"ing the crosstab does provide the benefit of returning a table with a predictable structure, including predictable field headings. Personally, I use crosstabs only where I NEED to; if the chart facility inherently provides the option to transform the data for me, then I dont need to use them (different story though with reporting of course).
(c) I'd reckon that the non crosstab version provides better performance (though you could counter argue that the charting transformation has to do more "work" then ... but with a lot less data)
(d) Sometimes crosstabs are necessary, sometimes they're not; this provides an example of where they're not. Why use something, if its not required.
(e) Having said all of this, the crosstab approach does provide the benefit of allowing better higher level control of the chart's X-Axis by virtue of its ability to specify a set of static column names in the PIVOT .. IN clause.
Armed with a number of options, the experience (gaining) user can make a more informed decision,
Cheers,
Steve
(a) I presented my solution as an alternative; not necessarily better or worse.
(b) "Avoid"ing the crosstab does provide the benefit of returning a table with a predictable structure, including predictable field headings. Personally, I use crosstabs only where I NEED to; if the chart facility inherently provides the option to transform the data for me, then I dont need to use them (different story though with reporting of course).
(c) I'd reckon that the non crosstab version provides better performance (though you could counter argue that the charting transformation has to do more "work" then ... but with a lot less data)
(d) Sometimes crosstabs are necessary, sometimes they're not; this provides an example of where they're not. Why use something, if its not required.
(e) Having said all of this, the crosstab approach does provide the benefit of allowing better higher level control of the chart's X-Axis by virtue of its ability to specify a set of static column names in the PIVOT .. IN clause.
Armed with a number of options, the experience (gaining) user can make a more informed decision,
Cheers,
Steve
- Status
Similar threads
- Locked
- Question
- Locked
- Question
