I have table with these fields:
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:
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.
- id
- plantationid
- sampleno
- yop
- n_status
- p_status
- k_status
- mg_status
- Def
- Low
- Optimum
- High
- Excess
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.
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.SELECT table.plantationid, table.yop
FROM table
GROUP BY table.plantationid, table.yop
PIVOT table.n_status;
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.