Crosstab Query: Row Heading taken from table's field name

yus2aces

Registered User.
Local time
Tomorrow, 05:33
Joined
Jul 25, 2012
Messages
14
I have table with these fields:

  • id
  • plantationid
  • sampleno
  • yop
  • n_status
  • p_status
  • k_status
  • mg_status
All of the nutrient status fields above (n_status, p_status, etc.), consists of these values:

  • Def
  • Low
  • Optimum
  • High
  • Excess
I want to create a crosstab query with this structure (column) and sample data:
plantationid yop nutrient Def Low Optimum High Excess
abc1 2001 N 30 1 5
abc1 2001 P 5 15
def1 2001 N 4 1 5 2

Explanation:

  • The nutrient fields gathered from the field's name (for example: n_status means N, p_status means P, etc.).
  • The value fields (Def, Low, etc.) are taken from the status count value.
  • the plantationid and yop are the grouping row heading for xtab.
I can create a crostab for one nutrient by using this sql:
TRANSFORM Count(table.n_status) AS CountOfn_status
SELECT table.plantationid, table.yop
FROM table
GROUP BY table.plantationid, table.yop
PIVOT table.n_status;
The above SQL statement will create a table without nutrient column.

How to create the crosstab query with different row heading by using part of field's name (N, P, etc.). Or maybe someone has different way to achieve the same result.
I hope my explanation is clear enough, and someone would like to help me for this. Thank you so much.
 
As an alternative (depend which one is the best), maybe someone could share the way to combine all the crosstab query (that I already create for one nutrient) into a single table.
 
I don't understand what you want.

In your sample output abc1 2001 N 30 1 5

Can you advise if
abc1 is the PlantationID, 2001 is the YOP, and how the N, 30,1,5 are derived.
 
Thanks for replying. :)
I have succesfully solved the problem, by using different way. I am creating a several Crosstab Query with this :
TRANSFORM Count(table.n_status) AS CountOfn_status
SELECT table.plantationid, table.yop, "N" as nutrientname
FROM table
GROUP BY table.plantationid, table.yop
PIVOT table.n_status;​
Note: Another xtab will consist of same query statement with slight change in the bold section above (eg. "P" as nutrientname and "K" as nutrientname)
Next, I am using union query to combine all crosstab queries above. Thanks again. :D
 

Users who are viewing this thread

Back
Top Bottom