How? Dynamically grouping records for chart display!

[Culby]

I have users entering parameters which are picked up by a query to select records and display in a report with a chart. The chart is only part of the report and is small. I need to limit the data points to a maximum of about 15 but it is possible to get up to 1500 records.

THE PROBLEM:
Therefore, I need to group the records but the grouping must be dynamic because it must reflect the nature of all the data. So...... obviously I can't just group the first 100 records then the second 100 records and soforth since the total amount of records may be less than 100 so my chart would get only 1 data point.

THE QUESTION:
How do I write my query (which uses only 2 fields to creat the chart)so that it will array the data and segment it into 15 groups then average or in someway group those records so that in the end it displays only 15 data points, thus, representing all the records when I make my chart, no matter how many records were actually selected? :

 

[GingerR]

what is your chart type? line? column?
you can alter your x-axis to only show every 10th or 20th or whatever item. i think it does this on it's own too doesnt it?
where are the points showing that you dont want to see them? on the axis? or in a data table? please supply a sample of the data.

 

[Culby]

The fields I use are a size field and a dollar field. The size field runs from 1000 to 100,000. The dollar field runs from $5.00 to $250.00

The chart is a line type.

I don't know how to submit a volume of data in this forum. If I can send you sample data directly please let me know.

I don't want to randomly view every 10th or 20th data point, I want to array the data small to large (size) then divide the data into 15 equal ranges and average all the records within each range (sizes and dollars), thus, I will have only 15 data points.

Please respond with what you need to help me and I'll provide it.

Thanks,
Culby

 

[GingerR]

you can compact and zip and send to rowe147@hotmail.com
if there are multiple charts, please let me know which you are needing help on.
seems like a fun project for the weekend )

for other forum users: i will post solution here.

 

[GingerR]

mike--i have your db but deleted your email so now dont have your address any more. write back.
in the mean time i have a question: what do you mean for this chart to look like?
what is on the Y-Axis? SIZE?
what is on the X-Axis? DOLLARS?
and value = count of size/dollars? i'm just checking.

how are you wanting the 'grouping' to happen? if you could maybe you could hack together an excel chart as an example for me. say the first 20 rows are 'grouped'; what do you want the x-axis to say? the value at the 20th row ($35.08)? i just can't envision what you're output will be. i can do the programming to get it there if you tell me more.

thanks--g

 

[GingerR]

hi again--you said earlier that your chart is a line chart, but the one you sent me was columns? again maybe it's best if you mock something up in excel. thanks!! g