By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Report Totals

P: n/a
Tom
All:

I have a report that lists quantities of stuff used over the course of
a year and it is grouped on each month. In the group footer I want to
insert the total for the month - easy stuff so far.

The part that has me stumped is that the units of use for each record
may not be the same (e.g., we may have one record at 5 lbs and one
with 4 kg). I've gone to some length in the forms for entering data
to prevent this, but I subscribe to the philosophy that nothing is
idiot proof - better idiots are unleashed very day. So what I want to
do is hide the totals if all the units aren't the same for the month
(since they would be meaningless). What is the best way to accomplish
this?

I've thought about running a query against the reports recordsource to
give me a count of the number of unique units - if I get more than 1,
I suppress the totals for all the months, but am not sure that is the
best approach.

For example:

I want totals in this case:
Jun 2007
Material 1 300 gal 3000 lbs
Material 2 200 gal 2500 lbs
Total 5500 lbs

I don't in this case:
Jun 2007
Material 1 300 gal 3000 lbs
Material 2 200 liter 250 kilograms

Any thoughts? Thanks

Tom

Jun 11 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Tom <rt*****@swbell.netwrote in
news:11**********************@k79g2000hse.googlegr oups.com:
All:

I have a report that lists quantities of stuff used over the
course of a year and it is grouped on each month. In the
group footer I want to insert the total for the month - easy
stuff so far.

The part that has me stumped is that the units of use for each
record may not be the same (e.g., we may have one record at 5
lbs and one with 4 kg). I've gone to some length in the forms
for entering data to prevent this, but I subscribe to the
philosophy that nothing is idiot proof - better idiots are
unleashed very day. So what I want to do is hide the totals
if all the units aren't the same for the month (since they
would be meaningless). What is the best way to accomplish
this?

I've thought about running a query against the reports
recordsource to give me a count of the number of unique units
- if I get more than 1, I suppress the totals for all the
months, but am not sure that is the best approach.

For example:

I want totals in this case:
Jun 2007
Material 1 300 gal 3000 lbs
Material 2 200 gal 2500 lbs
Total 5500 lbs

I don't in this case:
Jun 2007
Material 1 300 gal 3000 lbs
Material 2 200 liter 250 kilograms

Any thoughts? Thanks

Tom
In the report declarations, dim two variables per column, say
st1stum1 as string
bmixedUM1 as boolean.

In the month header_Format event, grab the first UM for the
month, and set bMixedUM to false.

In the details_format put something like
if me.UM <stFirstUM1 then bMixedUM1 = true

then you can code your total.visible property to false if
bMixedUM is true.

However, another solution is to build a lookup table of
UM_Equivalences.
with all your UnitSets,
so that if you want to total on gallons
um Factor
GAL 1.00
Liter 3.55
DRUM .0181818

so you can multiply the measurement by the factor
and get a valid total.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jun 12 '07 #2

P: n/a
Tom
On Jun 11, 6:44 pm, Bob Quintal <rquin...@sPAmpatico.cawrote:
Tom <rtmn...@swbell.netwrote innews:11**********************@k79g2000hse.google groups.com:


All:
I have a report that lists quantities of stuff used over the
course of a year and it is grouped on each month. In the
group footer I want to insert the total for the month - easy
stuff so far.
The part that has me stumped is that the units of use for each
record may not be the same (e.g., we may have one record at 5
lbs and one with 4 kg). I've gone to some length in the forms
for entering data to prevent this, but I subscribe to the
philosophy that nothing is idiot proof - better idiots are
unleashed very day. So what I want to do is hide the totals
if all the units aren't the same for the month (since they
would be meaningless). What is the best way to accomplish
this?
I've thought about running a query against the reports
recordsource to give me a count of the number of unique units
- if I get more than 1, I suppress the totals for all the
months, but am not sure that is the best approach.
For example:
I want totals in this case:
Jun 2007
Material 1 300 gal 3000 lbs
Material 2 200 gal 2500 lbs
Total 5500 lbs
I don't in this case:
Jun 2007
Material 1 300 gal 3000 lbs
Material 2 200 liter 250 kilograms
Any thoughts? Thanks
Tom

In the report declarations, dim two variables per column, say
st1stum1 as string
bmixedUM1 as boolean.

In the month header_Format event, grab the first UM for the
month, and set bMixedUM to false.

In the details_format put something like
if me.UM <stFirstUM1 then bMixedUM1 = true

then you can code your total.visible property to false if
bMixedUM is true.

However, another solution is to build a lookup table of
UM_Equivalences.
with all your UnitSets,
so that if you want to total on gallons
um Factor
GAL 1.00
Liter 3.55
DRUM .0181818

so you can multiply the measurement by the factor
and get a valid total.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -

- Show quoted text -
Thanks for the solution - much more elegant than my initial thoughts.

Tom

Jun 12 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.