crosstab query to chart? 1

[misscrf]

I am not sure how to accomplish this. Let me take this from the top. I have data linked in from SQL... I have a query, and then a crosstab query. I want to chart this data.

I don't know if I should be working in Excel or Access to accomplish this.

The data in the crosstab looks like this, which is how I want to graphically represent it:

localcalls internationalcalls longdistance

place1 5 12 6
place2 23 423 87
place3 12 54 24


now here is the catch, in the crosstab, I have parameters: MyDate is between [start date] and [end date]

That means that there would be a chart created for each month in that selection. If the user selects 200406 and 200410 ( june, 2004 and october, 2004) they would have a chart for june data, july, and so on.

Any ideas? When I go to create a chart, using the crosstab, it wont see any fields to use.
Thanks.

misscrf

Management is doing things right, leadership is doing the right things

 

[misscrf]

OK, I went to do that, and modify the rowsource. I tried to make the officename the row and each call type a column. It said that to make a crosstab you have to have 1 column and 1 or more rows.

I don't understand how I'm supposed to do this.
:-(

misscrf

Management is doing things right, leadership is doing the right things

 

[misscrf]

can you help me with how the rowsource works?

I did the wizard and then went to fix it like I said....

This is what it had:

TRANSFORM Sum(step1_Crosstab.[Total Of CountOfxCall_ID]) AS [SumOfTotal Of CountOfxCall_ID] SELECT step1_Crosstab.Inbound, step1_Crosstab.[Info/Assist], step1_Crosstab.Internal, step1_Crosstab.International, step1_Crosstab.Local, step1_Crosstab.[Long Distance] FROM step1_Crosstab GROUP BY step1_Crosstab.[Name Field] PIVOT step1_Crosstab.[Name Field];


and I can't get it to let me have more than 1 column.

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

Just set the Row Source to your crosstab query. The wizard tries too hard.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

I am working on the chart now. I have started from scratch, so that I can try to go step by step as you have instructed. I am having a difficult time with displaying the data. I don't know if you are experienced at displaying difficult data as well. The series for any month will have 6 call types along the bottom. The bars will only show for offices that have had at least 1 call. If so, there are up to 8 offices. Here is the tricky part. I get anywhere from 1 call in one office/calltype to 28,000 calls in one office/calltype. It just seems really yucky on the page, and the user would like the values displayed, so you know what the bar is exactly at. No Y axis numbers.

Any suggestions?

Thanks a bunch. I'd give you a bunch of stars if I could.


misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

Have you opened the chart control in design view by double-clicking it? You can then set all kinds of properties of the chart, axis, series, title, legend, format,...

One of the tricks that I use is to copy and paste the graph control onto a form. I then set the enabled and locked properties of the control to Yes and No. When viewing the graph, double-click it to open MS Graph. Play with the formatting until you get it right. Then go to the design view of the form to copy and paste the graph control back to the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

neat idea. I got my user happy for now, but I will definitely use that trick in the future. Another star for you hahaha.

Thank you for all of your help.

I don't know if I need to go to another post again, but is there anything I can do about how long it takes for the report or chart to run? It takes like 2-3 minutes.

I tried moving the db directly to the server, but that didnt seem to help.

Thanks again

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

If you have Page of Pages and or allow growing of sections, you might remove these for faster printing.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

Thanks, I will consider that.

what about just for previewing?

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

The suggestions I made were for previewing or printing.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

oh, ok thanks!


misscrf

Management is doing things right, leadership is doing the right things