Dynamic Access Chart with SQL-Server query

I am trying to write an analysis portion to my program, and I seem to be hitting a wall. Basically, I am trying to use the data compiled by the program's everyday use into useful statistics - like item cost, turn-around time, and total spent. So, for my first part - here is the query written in Access 2003 that accesses a few tables on a SQL Server machine over the network:

SELECT dbo_tblPODetail.intPurchaseOrderInstance, dbo_tblPODetail.strSupplierCode, dbo_tblPOGaugeDetail.strGageDetailID, dbo_Gage_Master.Description, dbo_Gage_Master.Current_Location, dbo_tblPOGaugeDetail.datGageReceived, dbo_tblPOGaugeDetail.intTurnaroundDaysOut, dbo_tblPOGaugeDetail.intGageCost
FROM (dbo_Supplier_Master INNER JOIN ((dbo_tblPOGaugeDetail INNER JOIN dbo_tblPODetail ON dbo_tblPOGaugeDetail.intGagePOID = dbo_tblPODetail.intPurchaseOrderInstance) INNER JOIN dbo_Gage_Master ON dbo_tblPOGaugeDetail.strGageDetailID = dbo_Gage_Master.Gage_ID) ON dbo_Supplier_Master.Supplier_Code = dbo_Gage_Master.Supplier_Code) INNER JOIN dbo_tblSupplierInfo ON dbo_tblPODetail.strSupplierCode = dbo_tblSupplierInfo.strSupplierID
WHERE (((dbo_tblPOGaugeDetail.datGageReceived) Is Not Null) AND ((dbo_tblPOGaugeDetail.bolGageBER)=False) AND ((dbo_tblPOGaugeDetail.bolGageCalibrate)=True))
ORDER BY dbo_tblPOGaugeDetail.datGageReceived;

This will give me a listing of all the items by their associated calibrators that have returned, are not beyond economical repair, what date they returned, and how much it cost. I want to take this data, and represent it on a bar chart object on the Supplier form. I tried a TRANSFORM PIVOT rowsource but I couldn't get it to group the开发者_如何学C data together in any meaningful way. Either two things happened:

1> Data was grouped together to sum the total cost per year for the calibrator. Useful, but not what I am after for this object.

2> Data is presented with no grouping, all costs having an individual bar, and the Y axis is count. Useless in this format, but the Y axis is correct.

I am looking for a count of the number of items for the Y-axis, but I need to figure out how to group costs together in five graph bars whose groupings are either fixed (0 to 249.99, 250 to 499.99, 500 to 749.99, 750 to 999.99, 1000+) or dynamically calculated based on the data present (most likely using a standard deviation or some other statistical function I can't remember the name of right now).

Any help with this is greatly appreciated, it's frustrating as it's a question I get asked all the time by the powers-that-be (along with 'How much do we spend with them?' and 'How long does it take to get stuff back?'). Let me know if you need me to provide more information.

I'd do the pivot (crosstab) separately from the graph (UI). Get your recordset working first, then worry about how it's displayed.

Have you tried using the crosstab wizard?