Crosstab Values

[tuktuk]

i have a crosstab query with the Values setting on Field "Agent" where the total line is set to "Count".

is there a way to format the output to be a Negative Nubmer rather than the current result which is a positive number?

T

 

[boblarson]

Use the cross tab query in another query and then multiply the value by -1.

 

[tuktuk]

ahhh gotcha....

one more question regardign Crosstabs if you don't mind.

on the same qry i also have a "MonthlyDate" field with a criteria "#12/01/10#". if count of values do not exist for #1/1/11# the crosstab starts wiht #2/1/11#......

is there a way to have #1/1/11# with values as zeros....this helps line exports up in MS Excel.

NOTE: the "MonthlyDate" field only contains text as the first of each month, namely "12/1/10","1/1/11","2/1/11","3/1/11" etc.......

Thanks

 

[boblarson]

You would need to add in an NZ function for nulls to turn them to 0. If they do not even exist for that month then you will need to create a table to have all possible dates you need and then link that up using an outer join for the initial query and then use that query in the crosstab you could put the Nz function in that query so when it got to the crosstab it would have the zeros.