CrossTab - Adding a Difference Column

[jpalmer150]

I'm just beginning to introduce myself to SQL coding.

I have a simple Crosstab query that produces just 2 columns when executed. [FY'02]& [FY'03] I'd like to add a third column that will provide the difference between the two which would represent the yearly growth.

I didn't think this should be too difficult, but I haven't figured it out yet.

Any suggestions?

 

[dodge20]

select column1, column2, (column1 - column2) as Growth
from YourTable

Dodge20

 

[jpalmer150]

Thanks...I gave that a try. Problem is that the query doesn't recognize FY'02 (column1) or FY'03 (column2). I'm assuming that's because they're virtual columns created by the crosstab and not fields within the query.

There must be a way to refer to the values with the crosstab column field.?

 

[dodge20]

Where do the fields come from? Can you be a little more specific on that? To refer to a different table or query you do this

select t1.field1, t2.field2, (t1.field1 -t2.field2)
from table1 t1, table2 t2
where ....

Dodge20

 

[JonFer]

The crosstab query will just generate the column values for you. Save it and write a new query that selects from the crosstab query. Then you have all of the columns from the crosstab to do whatever calculations you need.

 

[jpalmer150]

Sure...

The table looks like this

Territory Year Sales
US101 FY'02 100.00
US101 FY'03 200.00
US102 FY'02 125.00
US102 FY'03 300.00

Of course the CrossTab creates the following datasheet:

Territory FY'02 FY'03 GROWTH (To Be added)
US101 100.00 200.00
US102 125.00 300.00

I'm trying add the column named Growth which is the difference between the two. I'd like to do this in the same crosstab without creating a subquery.

 

[jpalmer150]

JonFer,

Thanks...That worked just fine and got the job done.

For performance considerations, there must be a way to combine this into one query. I'm curious how.

Thanks for the help.

JP

 

[MichaelRed]

look at the columnheadings property of the XTab

MichaelRed
m.red@att.net

Searching for employment in all the wrong places