Convert Access query to SQL View

Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
SenthilkumarCheck out CONVERT() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:%234%232fJmJGHA.1388@.TK2MSFTNGP11.phx.gbl...
Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
Senthilkumar|||To add to Tom's reply, you might want to consider leaving off the
formatting in the view and just return the raw data. Let the client
format the result set. Any time you format the data on the server, the
formatting functions you use have to operate on every single row, one
at a time.
--Mary
On Tue, 31 Jan 2006 18:09:30 +0530, "Senthilkumar"
<kesk32@.yahoo.co.in> wrote:

>Hi,
>I have used an access table to create a chart report to show how many calls
>were handled in a month. How do i conver the Access sql to MS sql server
>view. Here is the access sql:
>SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
>FROM [Complaints]
>GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
> (Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
>ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
>In the MS Sql, the word FORMAT is not recognised. I have tried several ways
>like this w/o any luck. Can anyone tell me the correct syntax for the above
>sql querry.
>Thanks
>Senthilkumar
>

Controlling the X-Axis

Hi guys...
I would like to do the following with my chart:
1. Have the label always appear vertically, and;
2. Limit the number of labels to 20 or less.
Any suggestions?
Thanks,
Forch#1: the chart control uses automatic label positioning based on the
available space. Vertical labels are only used if they don't fit
horizontally.
#2: Assuming you have the "numeric or time-scale" option turned on for the
x-axis, you could probably achieve this by setting the x-axis major interval
and/or min and max values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:61AA4890-A0B4-4BB2-BF5C-7006FC755650@.microsoft.com...
> Hi guys...
> I would like to do the following with my chart:
> 1. Have the label always appear vertically, and;
> 2. Limit the number of labels to 20 or less.
> Any suggestions?
> Thanks,
> Forch

Controlling the Chart Axis Interval

Greetings,
I have a chart indicating sales metrics.
The $ axis has no interval set, so it is automatic.
Sometimes, depending on what parameter the report is given, the
interval is very small and there are so many major grid lines that it
is nearly impossible to make sense of the chart.
This chart is not very big; it is only part of the report.
Is there some setting to tell the chart to be a little smarter about
the axis interval?
PLEASE NOTE: setting the interval manually would result in a HORRIBLE
result because depending on the parameter passed in, the chart
displays very different ranges of numbers.
Thank you in advance for your help.
JerryFor RS 2000, you might look into having multiple charts side by side. One
would use auto intervals, another one would use specific interval settings
for situations where auto intervals don't work well (e.g. if all data point
values are between 0 and 1). You would then hide all charts
(Visibility.Hidden property) except the one with the appropriate interval
settings for the current data.
BTW: we plan to have expression-based intervals in the next version.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410111513.120f5d32@.posting.google.com...
> Greetings,
> I have a chart indicating sales metrics.
> The $ axis has no interval set, so it is automatic.
> Sometimes, depending on what parameter the report is given, the
> interval is very small and there are so many major grid lines that it
> is nearly impossible to make sense of the chart.
> This chart is not very big; it is only part of the report.
> Is there some setting to tell the chart to be a little smarter about
> the axis interval?
> PLEASE NOTE: setting the interval manually would result in a HORRIBLE
> result because depending on the parameter passed in, the chart
> displays very different ranges of numbers.
> Thank you in advance for your help.
> Jerry

Contolling X axis labels in Chart

I have chart with data grouped by years and months. My data stops in Jan
2010 and the chart is showing this data with a year grouping of [2] instead
of [2010]. It appears to be truncating the year because it there is data for
only one month and it is the right most value in the chart. Any ideas on how
to force some extra space so that the entire year shows up? this currently
does not look very professional.
TIA
DeanI'm having a similar issue. The user is able to select a date range,
and a time frame (monthly, quarterly, yearly) and pass those
parameters into the report. When the report is generated, the monthly
grouping is fine. But quarterly and yearly, the brackets extend beyond
the range of the graph and are cut off. Sizing the chart area doesn't
do anything, because the brackets grow with the chart area. I'd either
like to turn off the brackets and just have the Labels or force more
space into the chart that doesn't extend the brackets.
Tim L