Special Fomat for Query

[RobNauticus]

Hello, I need to know if a special query can be created to properly format my data for a XY Scatter chart.

I am afraid to say that word "chart" as no one will take on anything related to them in any forum across the Internet.

Anyways, I wnat to take my data that looks like this:

DivisionID DrywallMBF Cost
LSC 10095 $7,000.00
LSC 10095 $7,300.00
LSC 10095 $6,500.00
USH 14995 $5,675.00
USH 14995 $5,900.00



And make it look similar to this:

Cost "LSC DrywallMBF" "USH DrywallMBF"
$7,000.00 10095
$7,300.00 10095
$6,500.00 10095
$5,675.00 14995
$5,900.00 14995



Also the query must be dynamic becuase there can be many divisionsIDs. The scatter graph must have separate columns for each series, so they must be separate.

Any help on how this can be accomplished would be appreciated,

Rob

 

[LonnieJohnson]

Try...

SELECT COST, IIF([DivisionID] = 'LSC',[DrywallMBF],Null) As [LSC DrywallMBF], IIF([DivisionID] = 'USH',[DrywallMBF],Null) As [USH DrywallMBF] FROM MyTableName

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[PHV]

HAve you tried a Crosstab query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobNauticus]

Just discovered it yesterday, it is exactly what I need.

Thanks all!

Rob