Formatting sums in crosstab?

[Elizabeth Swoope]

I have created a crosstab with ProjNo (text) as the rows, ExpObj (text) as
the columns, and the sum of Amount. I can use the property sheet to format
the overall total for each ProjNo as Standard 2, but the total for each
ExpObj within ProjNo is in general format no matter what I do (regardless of
the field properties in the original table, formatting in the original table,
formatting in the underlying query, and formatting in the crosstab query).

In the original data table (tblLedger), I have tried Amount as double,
formatted as Standard 2. I have also tried it as Decimal with Precision 18,
Scale 2, and Dec 2 (formatted as Standard 2, Fixed 2, and Currency 2).

The crosstab query is based on a query on the data table and Amount is
correctly formatted in the query.

I am obviously doing something wrong! Do I have to somehow use a function to
format these numbers? Having money amounts with different numbers of decimal
places is unacceptable.

Thanks,

liz

 

[Duane Hookom]

It would help tremendously to see your SQL view. I expect you only need to
wrap your value expression in the Val() function.

 

[Elizabeth Swoope]

Duane,

I had never looked at the SQL view. Doing so helped me catch that I had a
space in two field names, which I know is a "no no".

I'm not sure how I managed to do that, either, but I did. This data was
exported from Borland Reflex (DOS software, copyright 1989, and still being
used today!) to an old 1-2-3 format, then to Excel, and finally imported into
Access. It's very messy.

Anyway, problem solved! Thank you for your quick response and for pointing
me in the right direction.

liz

 

[Evi]

If your crosstab had any Nulls in it, even if you made them appear like 0s
by using NZ or formatting, then you need to be aware that they won't work as
expected and may give wrong results, unless you also use the Val function to
turn them into real numbers.
Write back if this is the case.
Evi

 

[Elizabeth Swoope]

Evi,

My crosstab has lots of nulls in it (assuming that you mean blank cells
where there's no value)! I haven't done anything to "fix" that, and I
definitely don't want zeroes printing, I want the cells to be blank. Are you
telling me that the numbers in the cells that do have numbers are wrong?

If so, please explain exactly what I need to do to make this all work, with
blanks in cells where there aren't any values.

Thanks so much for bringing this to my attention!

Here's the SQL code:

TRANSFORM Sum([qryLedgerDataEntry-Proj].Amount) AS SumOfAmount
SELECT [qryLedgerDataEntry-Proj].BudgetCode AS Expr1,
[qryLedgerDataEntry-Proj].ProjectNo AS Expr2,
Sum([qryLedgerDataEntry-Proj].Amount) AS Total
FROM [qryLedgerDataEntry-Proj]
GROUP BY [qryLedgerDataEntry-Proj].BudgetCode,
[qryLedgerDataEntry-Proj].ProjectNo
PIVOT [qryLedgerDataEntry-Proj].ExpObj;

liz

 

[Evi]

Its OK having nulls so long as you don't want to do any calculations with
them. The numbers themselves aren't wrong (although they are actually text,
not numbers), unless they were obtained by calculations using null values;
then they may be. They will almost certainly be wrong if you have to
subtract, divide or multiply any of them.
It can be misleading. For instance, I had one crosstab where it seemed to
sum the values, some of which were null, but when i tried to subtract
something, it gave the answer 0 although there was a remainder and, as you
noticed, I couldn't use the drop-down list to format the field.

That's because Null isn't the same as 0. Allen Browne explains that Null
means 'I don't know, it could be a large amount or it could be 0'. Have a
look at his Access tips at www.allenbrowne.com
for an excellent clarification of this issue.

You can fake formatting by typing a custom format into the field's
properties in your form, query or report but this really is only 'Keeping Up
Appearances', it won't change any values.

Your crosstab is based on a query. If that has any null values, then remove
them there eg

Val(NZ([Amount],0) AS Amt

I'll need to think some more about how to display nulls in your report and
crosstab query while keeping your real values for calculations. Perhaps
someone else can make some suggestions.


Evi

Evi,

My crosstab has lots of nulls in it (assuming that you mean blank cells
where there's no value)! I haven't done anything to "fix" that, and I
definitely don't want zeroes printing, I want the cells to be blank. Are you
telling me that the numbers in the cells that do have numbers are wrong?

If so, please explain exactly what I need to do to make this all work, with
blanks in cells where there aren't any values.

Thanks so much for bringing this to my attention!

Here's the SQL code:

TRANSFORM Sum([qryLedgerDataEntry-Proj].Amount) AS SumOfAmount
SELECT [qryLedgerDataEntry-Proj].BudgetCode AS Expr1,
[qryLedgerDataEntry-Proj].ProjectNo AS Expr2,
Sum([qryLedgerDataEntry-Proj].Amount) AS Total
FROM [qryLedgerDataEntry-Proj]
GROUP BY [qryLedgerDataEntry-Proj].BudgetCode,
[qryLedgerDataEntry-Proj].ProjectNo
PIVOT [qryLedgerDataEntry-Proj].ExpObj;

liz

If your crosstab had any Nulls in it, even if you made them appear like 0s
by using NZ or formatting, then you need to be aware that they won't work as
expected and may give wrong results, unless you also use the Val function to
turn them into real numbers.
Write back if this is the case.
Evi

 

[Elizabeth Swoope]

Evi,

I'm a database person (SAS) from way back, and I work in a statistics
department at a state university, so I'm definitely familiar with the
difference between null and zero, but I realize that most people aren't.

This simple app is a quick-and-dirty "checkbook" program. Blanks in cells
where there's no money (no money was deposited to that particular category in
that particular account) are fine. That actually makes the report easier to
read because you don't lose the numbers amongst all the zeroes. I expect to
see a 0 when there's no balance (all the money dedicated to that particular
category account has been spent) but not when there was never any money in
that category in that account in the first place.

I won't be doing any calculations with the result of this crosstab query.
It's just to give the users a summary of where their accounts stand as a
crosscheck of the mainframe accounting system. Each record in the table has
an amount and that's definitely a number so I think I'm off the hook for
nulls!

Thanks for taking the time to explain this. I've seen several posts in
various places about displaying zeroes, so I was vaguely aware that there's
an issue there.

liz

 

[Elizabeth Swoope]

Evi,

Thanks for taking the time to explain this. I'm a statistical database
programmer (SAS) from way back, so I'm one of the relatively small percent of
people who actually are aware of the difference between zero and null
(missing).

I won't be using any of the results of this crosstab in calculations and I
want the nulls displayed as blanks because that makes it easier to
concentrate on where the money is. A zero value means that the balance for an
account/category has been spent, not hat there was no money in it to begin
with.

It sounds like I don't need to do anything at all with my crosstab, but I'm
glad to be aware of some of the issues involved with crosstabs.

liz