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

 

[dhookom]

misscrf,
Don't you actually have another column in your crosstab "MyDate" that is text like "200406"...?

This would be a good time to get your parameters from a text box on a form. Otherwise each graph will ask you for the dates.

Also, recall the recommendation on Column Headings? It doesn't take too long to add these to your crosstab.

Once you get that done, a chart control has link master child properties that allows you to "filter" a chart to a single month based on the month value in the report's record source.

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

 

[misscrf]

Ok, I do have the MyDate, which is integer, but here is the issue. The query works to have the person request a start date and an end date. This means that I need a chart that will be created for every month it is asked for. If I put in 200406 for the start and 200410 for the end, I need 1 chart for 200406, 200410, and every month in between.

I don't think I understand what you mean by getting my parameters from a text box on a form, adding my column headings to my crosstab, and the filter thing.

Thanks. Any help is appreciated.

Courtney

misscrf

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

 

[misscrf]

ok, here is the deal. I used the query that feeds the crosstab query, to do my chart. Things are going well. I went into the recordsource of the chart, which I started in design view. I put the chart in the MyDate group heading, and I have the parameters, and all. Here is where I am stuck. When I preview, I get the amount of charts I want, with the month date of the chart on top. Thing is, the data is showing for the whole series ( all months on all charts)

What do I need to do to make the data group on 1 chart for 1 month,ie june data on june chart?

Thanks for all the help.


misscrf

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

 

[dhookom]

I would first create a form "frmDates" with two text boxes "txtStart" and "txtEnd" for entering the start and end values. Replace the criteria
Between [Start Date] and [End Date]
with
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd
This allows for much more flexibility and features compared with parameter prompts. If you have set the parameters in your query to the prompts, you must change them to
Forms!frmDates!txtStart Long
Forms!frmDates!txtEnd Long

Your report's record source would be something like:
SELECT DISTINCT MyDate
FROM somequerytable
WHERE MyDate Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd

If there are four months in the selection then there will be four records in the main report. Add your chart control to the detail section of the report and set the link master/detail properties to the MyDate fields.

The ACG Soft web site has information on graphs that seem out of synch with data

http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm.

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

 

[misscrf]

I have done everything you advised, but I am having trouble with one part...

"Add your chart control to the detail section of the report and set the link master/detail properties to the MyDate fields."

I can't see a master/detail properties. There is one when I have the chart selected, but not the detail section. When I put MyDate or [MyDate] into the link master properties for the chart, I get an error that I need to put it for the child. I do that, and it says it doesnt recognize MyDate.

What am I not getting here?

Thanks


misscrf

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

 

[dhookom]

My bad on link master/detail which should have been Link Master/Child. These properties are used exactly like the link master/child of a main form and subform. Both your main report and the chart should have a field in their record source/row source of MyDate.

What is the record source of your report along with a few typical values?

What is the row source of your chart control?

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

 

[misscrf]

The record source of my report is step1_Crosstab, the query.

A few typical values?

You mean like what the data looks like?

well...

longdistance local international

place1 5 50 100
place2 31 18 67
place3 53 22 215

This would be for one month.

The control source for my chart is...

PARAMETERS [Forms]![Chart Dates]![Start] Long, [Forms]![Chart Dates]![End] Long; SELECT DISTINCT step1.[Name Field], step1.Call_Type, Sum(step1.CountOfxCall_ID) AS SumOfCountOfxCall_ID, Left([xDate],6) AS MyDate FROM step1 WHERE (((Left([xDate],6)) Between Forms![Chart Dates]!Start And Forms![Chart Dates]!End)) GROUP BY step1.[Name Field], step1.Call_Type, Left([xDate],6);

Does that make sense?

I bet you're saying, that's what she's doing wrong! The idiot!

lol

Thanks for your time and help. I really appreciate it.


misscrf

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

 

[dhookom]

If you want a chart for each month then you must include the month field in the report's record source. If all you have is the chart control, then your report's record source should be
[tt][blue]
SELECT Left([xDate],6) AS MyDate
FROM step1
WHERE (((Left([xDate],6)) Between Forms![Chart Dates]!Start And Forms![Chart Dates]!End))
GROUP BY Left([xDate],6); [/blue][/tt]
You can then set the link master/child properties to [MyDate].

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

 

[misscrf]

I don't understand. If my record source only has MyDate, how is it going to plot the officename and calltype and sumdata?

where are all of those fields?


misscrf

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

 

[dhookom]

I thought you wanted the calltype, sumdata, etc to appear in your chart/graph. The chart/graph has its own row source which contains your records and values to plot. Your main report doesn't plot anything.

Also, I thought your chart was to be based off a crosstab query. Did you not want columns/graph series based on longdistance local international?

I suggested you add these values (longdistance local international) into your crosstabs column headings property. Did you do this?

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

 

[misscrf]

I actually made this report in design view, inserting a chart, using the query that Feeds the crosstab ( the data before it is crosstabbed) and I just put the rowsource as it should appear, ie the calltype is set as the row, office is set as column, and callcounts as the summarized data.

Ok as well, I get what you are saying, the above is the rowsource, and the select you gave me is what I need to do for the recordsource of the report, to group the data. Thanks, let me try that.



misscrf

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

 

[misscrf]

ok I am still having some trouble. I looked at the rowsource for the chart, and MyDate wasn't involved. I have been trying to get it to be included, so that the master child links can have MyDate set.

Here is the rowsource so far:
TRANSFORM Sum(step1.CountOfxCall_ID) AS SumOfCountOfxCall_ID SELECT step1.Call_Type, Left([xDate],6) AS MyDate FROM step1 GROUP BY step1.Call_Type PIVOT step1.[Name Field];

Here is what happens. I run the report and it says it doesnt recognize MyDate as a valid field or expression. Then it gives me an OLE error.


misscrf

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

 

[dhookom]

Does step1 have the field [MyDate]?

[MyDate] must also be in the Group By.

Does step1 have the Query|Parameters data typ set?

Are you ever going to set the column headings property in the crosstab?

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

 

[misscrf]

step1 does not have the field MyDate. It has xDate. MyDate is the formula Left(xDate,6). Should I change this way of doing this?

if I dont do that, the group by will be on the day, because the field is 8 numbers long, being 20040101

Does step1 have the query/parameters data type set? no. This is because step1 is not the crosstab. The crosstab query has parameters set, and it feeds the report. This chart is not running off the crosstab query. It wasnt working to run it that way. In the crosstab, however... call_type is set as the column headings.

Does this help?

misscrf

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

 

[dhookom]

OK, let's back up.
Start with a main report that has one field in its record source query. This field is MyDate and has each unique value of yyyymm such as 200407, 200408, 200409, ...

The graph control has a crosstab query as its Row Source. This crosstab should have a value in the Column Headings property. This is not the same as setting Call_type as the Column Heading. Open Help and enter "column headings property".

The Row Source query of the chart must include the MyDate field so that the two fields can be used in the Link Master/Child properties of the chart control.

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

 

[misscrf]

Here is the problem I run into. I start a new report, make the recordsource just have a group on the MyDate.

Then I go to insert a chart into the detail. When it wants me to pick a query, and I choose the crosstab, I get a message:

"Either the table or query you selected doesn't have enough fields to provide data for a chart or the fields can't be retrieved. Select a table or query with at least one numeric, date, or text field."

Just for info, here is the SQL view of my crosstab:

PARAMETERS [Start Date] Long, [End Date] Long;
TRANSFORM Sum(step1.CountOfxCall_ID) AS SumOfCountOfxCall_ID
SELECT step1.[Name Field], Left$([xDate],6) AS MyDate, Sum(step1.CountOfxCall_ID) AS [Total Of CountOfxCall_ID]
FROM step1
WHERE (((Left$([xDate],6)) Between [Start Date] And [End Date]))
GROUP BY step1.[Name Field], Left$([xDate],6)
PIVOT step1.Call_Type;


So..... This is why I was trying to do the chart off of step1 instead of this query. It doesnt recognize the fields when I try to pull it into a report. The report that I am running was already created when I did this query, and so thankfully it works. When I try to create a new report off of it, it doesnt like it.
:-(

misscrf

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

 

[dhookom]

Are you ever going to set the Column Headings property of the crosstab? Why are you not using a reference to the Forms![Chart Dates]!Start And Forms![Chart Dates]!End in the crosstab query.

I think I have asked or suggested using these two key ideas for several postings. You have continued to go off on your own without following these suggestions. I have created hundreds of crosstabs and graphs. I don't make suggestions like this without lots of experience.

Use parameter prompts like you have will ask you for the same values over and over and over when you attempt to view the report. You may not even get this to work unless you supply the column headings property.

Please implement both of these prior to replying back.

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

 

[misscrf]

ok, here it is, I know you will be proud.

PARAMETERS [Forms]![Dates]![Start] Long, [Forms]![Dates]![End] Long;
TRANSFORM Sum(step1.CountOfxCall_ID) AS SumOfCountOfxCall_ID
SELECT step1.[Name Field], step1.MyDate, Sum(step1.CountOfxCall_ID) AS [Total Of CountOfxCall_ID]
FROM step1
WHERE (((step1.MyDate) Between [Forms]![Dates]![Start] And [Forms]![Dates]![End]))
GROUP BY step1.[Name Field], step1.MyDate
PIVOT step1.Call_Type In ("Inbound","Info/Assist","Internal","International","Local","Long Distance");


I'm sorry about all that. I guess I thought I had done that, not understanding what it all meant. Anyway, here is the crosstab. I went and tried to insert a chart into a report with the MyDate grouped recordsource. It let me go ahead, but then said I could only bring 6 fields into the chart. I don't know what to do know. It looks good so far though!

Anyone ever tell you how smart you are?
:-D


misscrf

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

 

[dhookom]

Don't worry about the chart wizard limitation. Your graph can use more than 6 columns/fields. You may need to just modify the row source query after the fact.

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