Graph question

[Mark Andrews]

Access2007

I don't use graphs too often, but I was putting a graph on a report that
shows months Jan thru Dec and values for those months.

My query has:
- monthnumber (1 to 12)
- monthname (example: "jan", "feb")
- value (should be used to make the bar graph)

If I use a select query for the graph and I select "monthname" and value I
get a graph that is close and shows all 12 bars but it puts the months in
alphabetical order by name ("Apr", "Aug" etc...)

Do I need to switch to a crosstab query to get the graph to order correctly?

My underlying query refers to some form fields Forms!frmReports!StartDate
etc... and if I try to use a crosstab off my underlying query it doesn't
like the references to form fields. I can recreate the querydef from code
if needed.

Thanks for help in advance,
Mark

 

[S.Clark]

Crosstab queries allow you to specify the desired column order... Jan, Feb,
Mar.

Alternatively, you should be able to sort by the MonthNumber somewhere to
make the order correct.

 

[Mark Andrews]

Would you know how I can sort by MonthNumber (the graph forces every column
to have a aggregate function)? Also if I go the crosstab route do you know
if references to forms are allowed (some trick)?

I know what crosstab queries and select queries can do, so your answer was a
little too general to help.

Keep trying,
Mark

 

[Duane Hookom]

Rather than describing your query by the columns, just post your Row Source
SQL view. You should be able to simply ORDER BY MonthNumber.

 

[Mark Andrews]

Duane,

Ok to eliminate all other variables 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

 

[Duane Hookom]

Have you tried changing the Row Source to:
SELECT Table1.[MonthText], Sum(Table1.TheValue) AS SumOfTheValue,
Table1.MonthNumber
FROM Table1
GROUP BY Table1.[MonthText],Table1.MonthNumber
ORDER BY Table1.MonthNumber;

--
Duane Hookom
Microsoft Access MVP

Duane,

Ok to eliminate all other variables 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

Rather than describing your query by the columns, just post your Row
Source
SQL view. You should be able to simply ORDER BY MonthNumber.

.