Chart off of Union Query 1

[hende10]

I have two queries, one for 2004 and one for 2005, that total dollars based on month. I then have a union query that joins both of these into one, and shows three columns: Year, Month, and Amount. I am trying to build a chart with Y-Axis as Amount, X-Axis as Month, and two different series, one for each year.

My problem has been posted here before, but I still can't get it working. The X-Axis needs to be the month in 'MMMM' format, but it then sorts it alphabetically (APR, AUG...SEP), instead of by month order.

The Union query shows the correct sort order, but the only way I can get the chart to sort correctly is by using 'mm' format for the month, which will only convert to all JANs when I change the format.

Any and all help would be appreciated.

Thanks,

Hende

 

[dhookom]

What is the SQL view of your Row Source of the chart?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hende10]

I'm not sure how to find the SQL for the chart, but if you mean the SQL for the Union Query that the chart is based off of:

SELECT [DtCode],[Date By Year],[Date By Month],[Sum Of TotNetVal]
FROM [2005 Chart Totals]

UNION SELECT [DtCode],[Date By Year],[Date By Month],[Sum Of NetVal]
FROM [2004 Chart Totals];

 

[dhookom]

A chart has a Row Source property. This row source defines the data, sort order, and label values for your chart. I assume the above is the sql view of your query. Your Row Source might be the above or a saved query name or a sql statement.

I also assume the [Date By Year] value is the numeric year value and [Date By Month] is the numeric month value. Am I correct?

What is the SQL View of your chart's Row Source property?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hende10]

Oh, that row source query...:

TRANSFORM Sum([Chart Union Query].DtCode) AS SumOfDtCode
SELECT [Chart Union Query].[Date By Month]
FROM [Chart Union Query]
GROUP BY [Chart Union Query].[Date By Month]
PIVOT [Chart Union Query].[Date By Year];

Date By Year shows up as a 4 digit number (2005), and Date by Month is a 2 digit number (05). I don't know if they are actually numeric or text, although if the month is text I can see that as being a problem.

 

[dhookom]

You can try set your row source to:
[tt]
TRANSFORM Sum([Chart Union Query].DtCode) AS SumOfDtCode
SELECT Format(DateSerial([Date By Year],[Date By Month],1),"mmmm") As Mth
FROM [Chart Union Query]
GROUP BY Format(DateSerial([Date By Year],[Date By Month],1),"mmmm")
ORDER BY Val([Date By Month])
PIVOT [Chart Union Query].[Date By Year];
[/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hende10]

Thanks Duane, that seemed to work. It's listing the actual month names in the print preview, and I can change the ones in the design window manually through the datasheet.

Thanks for saving me from even more headaches.

Hende

 

[hende10]

I have one more add on question for this report. I'm trying to print the chart in landscape on 11 x 17 paper, but I can't get it to fill the paper without stretching the image, causing distortion. Is there a size limit on charts, or should I be able to fill an 11 x 17 sheet entirely?

Thanks,

Hende

 

[dhookom]

I just tested a pie chart on a 11x17 and it seemed to look fine. I don't notice any distortion.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hende10]

My 2 series bar chart is hitting a wall at 13". In design mode, I can stretch it up to 17", but it won't refresh automatically, and when I switch to preview, it snaps back to 13".

13" still seems to be the limit even if I have the legend to the right side or not. Could it be a memory/video card problem?

 

[dhookom]

I just tried setting my chart control width to 15" and it printed great. I don't know any more about this limits than you do. Every time you ask a question, I have to open an mdb, open my report is design view, change the paper size, change the chart width, and test.

Try different printers and if you have others around you at work, have them try.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]