SUMs, totalling, but not totalling with every record..

[Geel]

Hello, i'm working on a MS Access system wich works as most systems do.
Forms -> Tables -> queries -> [totalquery] -> Reports


I've got like everything up and working, but i need totals below on the
reports. All the veriaous numbers can be summed cos they need to be
summed per record. But i also got some numbers (budgets in this case,
and an number showing an percentage) Wich only need to be showed on
department base, but they do need to be included in my totalquery so i
can show them in my report.

There are 2 minor different problems, the first is caused by the
following:

We have a Budget being entered on Category level. But the budgets need
to be in the totaleverythingquery so i can bring it up in my report.
Therfore i'm using the First(..). Everything is fine and up and working
when i just use it in the report on the category level. But once i go
and sum the numbers up underneath the line, it will sum every record.
So when i have 2 records in 1 category with 1 budget it will give me a
total budget of budget*2. I was wondering if there is anyway to get rid
of the repeating number (first?) so that it will show the first number
once and the rest will be 0. That way it would be fixed, or maybe you
guys got a diff solution for me.


Then there is almost the same but a little bit different problem, is
has to do with the Sum to get a total again. I'm calculating an
percentage and showing this percentage in one of the reports. These
percentages are showed per record. Everything is fine untill i want to
get the things calculated per category or f.e. the overalltotal.
Because i can't just SUM things, i have to calculate these numbers
otherwise they won't be right.

I thought about creating some kind of query where they calculate this
per total and per category, but havn't been able to try this yet. Do
you think this will work, and if you got hints they are always welcome.

Any other ideas very much apreciated.

Thanks in advance, ( if things ain't possible (in your oppinion) lemme
know aswell )

Tim

 

[John Vinson]

Hello, i'm working on a MS Access system wich works as most systems do.
Forms -> Tables -> queries -> [totalquery] -> Reports


I've got like everything up and working, but i need totals below on the
reports. All the veriaous numbers can be summed cos they need to be
summed per record. But i also got some numbers (budgets in this case,
and an number showing an percentage) Wich only need to be showed on
department base, but they do need to be included in my totalquery so i
can show them in my report.

Rather than trying to do everything in a complicated Query, use the
powerful Sorting and Grouping feature of the Report.

Right mouseclick the little square at the upper left intersection of
the rulers in Report Design view and select Sorting and Grouping. You
can sort/group by multiple layers of fields, and for each grouping
level, you can make the group header and footer visible. If you put
textboxes in these footers, with control sources like

=Sum([fieldname])
=Avg([fieldname])
etc.

you'll be able to get both the individual row data (in the Detail
section) and summary data.

John W. Vinson[MVP]

 

[Geel]

John Vinson schreef:

Hello, i'm working on a MS Access system wich works as most systems do.
Forms -> Tables -> queries -> [totalquery] -> Reports


I've got like everything up and working, but i need totals below on the
reports. All the veriaous numbers can be summed cos they need to be
summed per record. But i also got some numbers (budgets in this case,
and an number showing an percentage) Wich only need to be showed on
department base, but they do need to be included in my totalquery so i
can show them in my report.

Rather than trying to do everything in a complicated Query, use the
powerful Sorting and Grouping feature of the Report.

Right mouseclick the little square at the upper left intersection of
the rulers in Report Design view and select Sorting and Grouping. You
can sort/group by multiple layers of fields, and for each grouping
level, you can make the group header and footer visible. If you put
textboxes in these footers, with control sources like

=Sum([fieldname])
=Avg([fieldname])
etc.

you'll be able to get both the individual row data (in the Detail
section) and summary data.

John W. Vinson[MVP]

Nice idea but i don't think this will work, my report(s) is build up
like this already. But some of the totals on the same level need to be
on record level and some on category level.. Since i have to put these
new settings up for a new section (with footer + header) i won't get
the result i want to have.

 

[John Vinson]

Nice idea but i don't think this will work, my report(s) is build up
like this already. But some of the totals on the same level need to be
on record level and some on category level.. Since i have to put these
new settings up for a new section (with footer + header) i won't get
the result i want to have.

Well, you can't get totals at two levels in a Totals query either - in
fact it's even harder!

Might you consider using textboxes with control sources like

=DSum("[fieldname]", "[tablename]", "<criteria>")

or using DSum in a calculated field in a query? Inefficient, I'll
grant, but if you need many levels of sums it may be your only
choice...

John W. Vinson[MVP]

 

[Geel]

John Vinson schreef:

Nice idea but i don't think this will work, my report(s) is build up
like this already. But some of the totals on the same level need to be
on record level and some on category level.. Since i have to put these
new settings up for a new section (with footer + header) i won't get
the result i want to have.

Well, you can't get totals at two levels in a Totals query either - in
fact it's even harder!

Might you consider using textboxes with control sources like

=DSum("[fieldname]", "[tablename]", "<criteria>")

or using DSum in a calculated field in a query? Inefficient, I'll
grant, but if you need many levels of sums it may be your only
choice...

John W. Vinson[MVP]

So what you are saying is i got one option: DSum, but that option is
inefficient aswell. So out of this i can conclude their isn't any real
option to get what i want.. different level totals..