Crosstab but if a value does not exist,it shows an error in a sub-

[ldiaz]

I have a crosstab query that shows datas like this:
W-Date Planner ACCY CONC POUCH
22-Oct-07 BAM-06 38 67 96
23-Oct-07 BAM-06 14 8 85
24-Oct-07 BAM-06 106 30 220

where ACCY,CONC, POUCH is the "Column Heading" the query is working fine,
but these datas are showed to the user using a subform, the problem is when
in crosstab query appear a new Style, like TOC and do not appear ACCY or
other it shows an error, because a field in sub-form was not found

I think it's because the sub-form was built with previous datas and then
when new datas appear it shows the error,

how fix this?, or how shows datas to the user as read only but not using a
sub-form or report because they aren't going to work

Thanks
LD

 

[KARL DEWEY]

Post your SQL.

 

[ldiaz]

Here is the SQL
TRANSFORM Sum(IDVolatilDBID_lbl.Qty) AS SumOfQty
SELECT IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
GROUP BY IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
PIVOT IDVolatilDBID_lbl.User6;

 

[KARL DEWEY]

Try this --
TRANSFORM IIf(Sum([QTY]) Is Null," ",Sum([QTY])) AS Expr1
SELECT IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
GROUP BY IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
PIVOT IDVolatilDBID_lbl.User6;

--
KARL DEWEY
Build a little - Test a little

Here is the SQL
TRANSFORM Sum(IDVolatilDBID_lbl.Qty) AS SumOfQty
SELECT IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
GROUP BY IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
PIVOT IDVolatilDBID_lbl.User6;

Post your SQL.

 

[ldiaz]

it shows an error
the specified field '[Qty]' could refer more that one table....


LD

Try this --
TRANSFORM IIf(Sum([QTY]) Is Null," ",Sum([QTY])) AS Expr1
SELECT IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
GROUP BY IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
PIVOT IDVolatilDBID_lbl.User6;

--
KARL DEWEY
Build a little - Test a little

Here is the SQL
TRANSFORM Sum(IDVolatilDBID_lbl.Qty) AS SumOfQty
SELECT IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
GROUP BY IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
PIVOT IDVolatilDBID_lbl.User6;

Post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have a crosstab query that shows datas like this:
W-Date Planner ACCY CONC POUCH
22-Oct-07 BAM-06 38 67 96
23-Oct-07 BAM-06 14 8 85
24-Oct-07 BAM-06 106 30 220

where ACCY,CONC, POUCH is the "Column Heading" the query is working fine,
but these datas are showed to the user using a subform, the problem is when
in crosstab query appear a new Style, like TOC and do not appear ACCY or
other it shows an error, because a field in sub-form was not found

I think it's because the sub-form was built with previous datas and then
when new datas appear it shows the error,

how fix this?, or how shows datas to the user as read only but not using a
sub-form or report because they aren't going to work

Thanks
LD

 

[KARL DEWEY]

I do not see how you could get that error but try this --
TRANSFORM IIf(Sum([IDVolatilDBID_lbl].[QTY]) Is Null,"
",Sum([IDVolatilDBID_lbl].[QTY])) AS Expr1
SELECT IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
GROUP BY IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
PIVOT IDVolatilDBID_lbl.User6;

--
KARL DEWEY
Build a little - Test a little

it shows an error
the specified field '[Qty]' could refer more that one table....


LD

Try this --
TRANSFORM IIf(Sum([QTY]) Is Null," ",Sum([QTY])) AS Expr1
SELECT IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
GROUP BY IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
PIVOT IDVolatilDBID_lbl.User6;

--
KARL DEWEY
Build a little - Test a little

Here is the SQL
TRANSFORM Sum(IDVolatilDBID_lbl.Qty) AS SumOfQty
SELECT IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
FROM To_Update_Excel_r1 INNER JOIN IDVolatilDBID_lbl ON
To_Update_Excel_r1.WO_ID = IDVolatilDBID_lbl.WO_ID
GROUP BY IDVolatilDBID_lbl.[W-Date], IDVolatilDBID_lbl.Planner
PIVOT IDVolatilDBID_lbl.User6;




:

Post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have a crosstab query that shows datas like this:
W-Date Planner ACCY CONC POUCH
22-Oct-07 BAM-06 38 67 96
23-Oct-07 BAM-06 14 8 85
24-Oct-07 BAM-06 106 30 220

where ACCY,CONC, POUCH is the "Column Heading" the query is working fine,
but these datas are showed to the user using a subform, the problem is when
in crosstab query appear a new Style, like TOC and do not appear ACCY or
other it shows an error, because a field in sub-form was not found

I think it's because the sub-form was built with previous datas and then
when new datas appear it shows the error,

how fix this?, or how shows datas to the user as read only but not using a
sub-form or report because they aren't going to work

Thanks
LD