one character fields name generated by crosstab query

  • Thread starter Thread starter inungh
  • Start date Start date
I

inungh

I create a query base on a crosstab query.

The crosstab query has pivot clause like following:

PIVOT tblMytable.[MyGrade] In ("A", "B", "C", "D");

It generates single character filed name from pivot clause.

My problem is I need use (sum([a]) + sum())/ (sum([a]) + sum() +
sum([c]) + sum([d])) in my query.
It seems Access does not let me use single character field name.

If the filed name more than one character works fine.


any workaround?
Your information is great appreciated,
 
Did your crosstab produce any nulls?    Nulls do not sum very well.
--
KARL DEWEY
Build a little - Test a little



inungh said:
I create a query base on a crosstab query.
Click to expand...
The crosstab query has pivot clause like following:
Click to expand...
PIVOT tblMytable.[MyGrade] In ("A", "B", "C", "D");
Click to expand...
It generates single character filed name from pivot clause.
Click to expand...
My problem is I need use (sum([a]) + sum())/ (sum([a]) + sum() +
sum([c]) + sum([d])) in my query.
It seems Access does not let me use single character field name.
Click to expand...

If the filed name more than one character works fine.
Click to expand...
any workaround?
Your information is great appreciated,- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


yes, it returns null value. does null value affect?
it works for more than one character field name.

Thanks again
 
Use the Nz function like this ---
(sum(Nz([a],0)) + sum(Nz(,0)) / sum(Nz([a],0)) + .....
--
KARL DEWEY
Build a little - Test a little


inungh said:
Did your crosstab produce any nulls? Nulls do not sum very well.
--
KARL DEWEY
Build a little - Test a little



inungh said:
I create a query base on a crosstab query.
Click to expand...
The crosstab query has pivot clause like following:
Click to expand...
PIVOT tblMytable.[MyGrade] In ("A", "B", "C", "D");
Click to expand...
It generates single character filed name from pivot clause.
Click to expand...
My problem is I need use (sum([a]) + sum())/ (sum([a]) + sum() +
sum([c]) + sum([d])) in my query.
It seems Access does not let me use single character field name.
Click to expand...

If the filed name more than one character works fine.
Click to expand...
any workaround?
Your information is great appreciated,- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


yes, it returns null value. does null value affect?
it works for more than one character field name.

Thanks again
Click to expand...
 
Back
Top