Crosstab Query Column Titles

[gharabed]

Is there a way to set the column title/header for the columns in a crosstab query?

For example, I have the table with the following data:

Table1

Series Xval Yval
====== ==== ====
A 10 10
A 15 20
A 20 30
B 10 100
B 15 120
B 20 150

and I use the following crosstab query

TRANSFORM Sum(tblMultiGraph.yVal) AS SumOfyVal SELECT tblMultiGraph.xVal FROM tblMultiGraph GROUP BY tblMultiGraph.xVal PIVOT tblMultiGraph.Series;

I get the results:

Xval A B
==== === ===
10 10 100
15 20 120
20 30 150

Is there a way to rename the column headers "A" and "B"? Say to "Series_A" and "Series_B"?

 

[Steve101]

Yes,

Change your SQL to the following:

TRANSFORM Sum(tblMultiGraph.YVal) AS SumOfyVal
SELECT tblMultiGraph.XVal
FROM tblMultiGraph
GROUP BY tblMultiGraph.XVal
PIVOT tblMultiGraph.Series In ("A-Series","B-Series"

ie. just add the ...In (...) bit at the end

You can also do this by entering the headings in the Column Headings Property of the query in design view.

Hope this helps,
Cheers,
Steve

 

[gharabed]

How does the query know how to relate "A-Series" to the value "A" and "B-Series" to the value "B"? In other words, how does it know how to relate the new column label to which value?

 

[gharabed]

Also, it seems that when I do this only data is returned in the first column (XVal) and not in the PIVOT columns. Are you sure this is correct?

 

[Steve101]

good points, and yes, I've not given you the whole picture. If you do a lookup in online help on ColumnHeadings, you'll find the following:

"You can use the ColumnHeadings property to specify the order or to limit the number of columns displayed in a crosstab query. "

The headings in the list must match the data, otherwise the columns values wont be included in the crosstab.

If you want to convert the headings, then you should be able to either use the format function, or write your own public function, and call it from the query. The function might look something like this:

Function NewHeading (ColumnHead)
Select Case ColumnHead
Case "A"
NewHeading = "Heading-A"
Case "B"
NewHeading = "Heading-B"
Case else
NewHeading = "AnyThingElse"
end Select
End Function

Then instead of referencing "Series" directly in the crosstab, you'd reference NewHeading(Series), which would do the translation. You would also possibly need to include the In clause in the query to ensure that the columns appear in the order you want.

Hope that this has clarified the situation a bit,
Cheers,
Steve

 

[Guest_imported]

Hi,
I've got a problem.In one procedure i want to select and join diferent tables ,one of them is .mdb and the others are .dbf.I'm openning the access data base and using the "in clause"([CPR_LAN LAN] IN ' c:\...' 'FoxPro 2.6;',&quotto open all the others.Unfortunantuly i am receiving an error message which is:"ERRO: -2147217900 syntax erro in FROM".
if any of you could help me i'll be really glad.
Thanks
Marcos

 

[Steve101]

Simplest way to get this working is to link the dbf tables into the tables window; then generate the query diagramatically. When this is working, you can fill in the Source property each of the associated table(s) in the query. See 'Source Property' in online help.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Shores]

I have a crosstab query that I use so that depending on a value entered different information is displayed. I would like the value that I enter displayed somewhere so that the datasheet returned makes sense, any suggestions???

ie
Date1 Date2 Date3
Name1 5 5 5
Name2 6 7 6
Name3 4 8 8

criteria to run report "value1"

Want to display this value somewhere!!

 

[Shores]

I have a crosstab query that I use so that depending on a value entered different information is displayed. I would like the value that I enter displayed somewhere so that the datasheet returned makes sense, any suggestions???

ie
Date1 Date2 Date3
Name1 5 5 5
Name2 6 7 6
Name3 4 8 8

criteria to run report "value1"

Want to display this value somewhere!!

 

[ravie23]

How can I add the values of two tables, one with multiple records, and the other one record with multiple values. For example:

Table1: Record #1 contains field such as: SSN, IS_MOTH_DEP, IS_FATH_DEP, IS_SP_DEP. The number 1 is place in each field, except SSN, which means true and 0 if false.

Table2: contains fields such as: SSN, IS_CHI DEP. The number 1 is place in each field, except SSN, which means true and 0 if false.

The two tables are connected by SSN in a One to Many Relationship (Table2 has many children).

I would like to add/sum the values of the three fields in table1 with all the values of table2 grouped by SSN.

This way I will know how many dependents an individual have per record.

Any assistance would be greatly appreciated