Graph question in Access2007

[Mark Andrews]

I cannot get a bar graph to show Months (jan, feb, mar, apr, etc...) and
sort the months correctly in Access 2007.

To show this behavior:
I made a new database with one table (3
fields MonthNumber, MonthText and TheValue) and did a report with a graph.

When using this rowsource:
SELECT Table1.[MonthText], Sum(Table1.TheValue) AS SumOfTheValue,
Table1.MonthNumber FROM Table1 GROUP BY Table1.[MonthText] ORDER BY
Table1.MonthNumber;

adding the ORDER BY cause triggers an error "MonthNumber must be part of an
aggregate function" or something like that when running the report.

Access2007 SP2.

I thought you could do this in other versions?

Any answers or workarounds appreciated,
Mark

 

[Daryl S]

Mark -

Add the Table1.MonthNumber in the GROUP BY clause and it won't error out in
the ORDER BY clause.

 

[Mark Andrews]

Then you get a graph with two bars (instead of just the one I want).

Mark

Mark -

Add the Table1.MonthNumber in the GROUP BY clause and it won't error out
in
the ORDER BY clause.

--
Daryl S

I cannot get a bar graph to show Months (jan, feb, mar, apr, etc...) and
sort the months correctly in Access 2007.

To show this behavior:
I made a new database with one table (3
fields MonthNumber, MonthText and TheValue) and did a report with a
graph.

When using this rowsource:
SELECT Table1.[MonthText], Sum(Table1.TheValue) AS SumOfTheValue,
Table1.MonthNumber FROM Table1 GROUP BY Table1.[MonthText] ORDER BY
Table1.MonthNumber;

adding the ORDER BY cause triggers an error "MonthNumber must be part of
an
aggregate function" or something like that when running the report.

Access2007 SP2.

I thought you could do this in other versions?

Any answers or workarounds appreciated,
Mark


.

 

[Daryl S]

Mark -

You should not get any more data returned from the query, so where does the
additional bar come from? You do not need the MonthNumber in the SELECT
clause. Post the SQL if that is not the issue.

 

[Mark Andrews]

You are right it does work.

Thanks for helping me out,
Not sure why I was stuck on this simple issue, guess I'm not used to
grouping by something not in the select portion of the query.

Thanks again,
Mark

 

[Mark Andrews]

That worked! Don't know what my problem was?

Thanks for your help,
Mark