Formatting sums in crosstab?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Elizabeth Swoope - 26 Apr 2008 02:11 GMT

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

Reply to this Message

Duane Hookom - 26 Apr 2008 04:47 GMT

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

Signature

Duane Hookom
Microsoft Access MVP

> 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
[quoted text clipped - 17 lines]
>
> liz

Reply to this Message

Elizabeth Swoope - 26 Apr 2008 05:16 GMT

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

> It would help tremendously to see your SQL view. I expect you only need to
> wrap your value expression in the Val() function.
[quoted text clipped - 20 lines]
> >
> > liz

Reply to this Message

Evi - 26 Apr 2008 12:27 GMT

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

> 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
[quoted text clipped - 17 lines]
>
> liz

Reply to this Message

Elizabeth Swoope - 26 Apr 2008 12:48 GMT

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

Reply to this Message

Evi - 26 Apr 2008 13:45 GMT

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,
>
[quoted text clipped - 27 lines]
> > Write back if this is the case.
> > Evi

Reply to this Message

Elizabeth Swoope - 26 Apr 2008 14:15 GMT

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

> 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,
[quoted text clipped - 25 lines]
>
> Evi

Reply to this Message

Elizabeth Swoope - 26 Apr 2008 14:19 GMT

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

> 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,
[quoted text clipped - 25 lines]
>
> Evi

Reply to this Message