Conditional Summation

[Greg]

I have a question I can't seem to resolve looking at my "Access 2000 Bible".
I am trying to create a report based upon 1 table. My table fields looks
like the following:
Telephone number, Monthly Bill Date(date format), Allowance
minutes(numeric), Peak Usage Minutes(numeric), Off Peak Usage
Minutes(numeric), Plan ID(character) .

My report sums by date and by telephone number, all is well so far!

What I would like to do is have another summary field in my report. This new
summary field would appear at the end of each month where my other summary
fields appear on the report.
I would like to look at the Plan Id field in a record and if it is a certain
value e.g.. "003" include the related Off Peak Usage Minutes in the Peak
Usage minutes Sum field. This may sound silly but let me try an example

770 444 4444, 01/20/2006, 800, 600, 250, 001
770 444 5555, 01/20/2006, 600, 400, 350, 003

What I have now is a report the sums;
Total Allowance minutes =1400
Total Peak Usage Minutes= 1000
Total Off Peak Usage Minutes= 600
THIS IS ALL GOOD

How can I either create a new field or include the 350 minutes(Off Peak
Usage Minutes) from the second record (Plan Id= 003) in the Sum of Total
Peak Usage minutes??

A report that looks like this :

Total Allowance minutes =1400
Total Peak Usage Minutes= 1000
Total Off Peak Usage Minutes= 600
New field Minutes= 1350 (1000 minutes from sum of Peak Usage minutes and
350 minutes from record with rate plan 003).

I am trying to learn access and would appreciate any assistance

Greg

 

[Marshall Barton]

I have a question I can't seem to resolve looking at my "Access 2000 Bible".
I am trying to create a report based upon 1 table. My table fields looks
like the following:
Telephone number, Monthly Bill Date(date format), Allowance
minutes(numeric), Peak Usage Minutes(numeric), Off Peak Usage
Minutes(numeric), Plan ID(character) .

My report sums by date and by telephone number, all is well so far!

What I would like to do is have another summary field in my report. This new
summary field would appear at the end of each month where my other summary
fields appear on the report.
I would like to look at the Plan Id field in a record and if it is a certain
value e.g.. "003" include the related Off Peak Usage Minutes in the Peak
Usage minutes Sum field. This may sound silly but let me try an example

770 444 4444, 01/20/2006, 800, 600, 250, 001
770 444 5555, 01/20/2006, 600, 400, 350, 003

What I have now is a report the sums;
Total Allowance minutes =1400
Total Peak Usage Minutes= 1000
Total Off Peak Usage Minutes= 600
THIS IS ALL GOOD

How can I either create a new field or include the 350 minutes(Off Peak
Usage Minutes) from the second record (Plan Id= 003) in the Sum of Total
Peak Usage minutes??

A report that looks like this :

Total Allowance minutes =1400
Total Peak Usage Minutes= 1000
Total Off Peak Usage Minutes= 600
New field Minutes= 1350 (1000 minutes from sum of Peak Usage minutes and
350 minutes from record with rate plan 003).

Use a text box woth an expression like:

=Sum(IIf([Plan ID] = "003", [Off Peak Usage] + [Peak Usage])

 

[Greg]

Marsh,
Thank You very much!
Greg

I have a question I can't seem to resolve looking at my "Access 2000 Bible".
I am trying to create a report based upon 1 table. My table fields looks
like the following:
Telephone number, Monthly Bill Date(date format), Allowance
minutes(numeric), Peak Usage Minutes(numeric), Off Peak Usage
Minutes(numeric), Plan ID(character) .

My report sums by date and by telephone number, all is well so far!

What I would like to do is have another summary field in my report. This new
summary field would appear at the end of each month where my other summary
fields appear on the report.
I would like to look at the Plan Id field in a record and if it is a certain
value e.g.. "003" include the related Off Peak Usage Minutes in the Peak
Usage minutes Sum field. This may sound silly but let me try an example

770 444 4444, 01/20/2006, 800, 600, 250, 001
770 444 5555, 01/20/2006, 600, 400, 350, 003

What I have now is a report the sums;
Total Allowance minutes =1400
Total Peak Usage Minutes= 1000
Total Off Peak Usage Minutes= 600
THIS IS ALL GOOD

How can I either create a new field or include the 350 minutes(Off Peak
Usage Minutes) from the second record (Plan Id= 003) in the Sum of Total
Peak Usage minutes??

A report that looks like this :

Total Allowance minutes =1400
Total Peak Usage Minutes= 1000
Total Off Peak Usage Minutes= 600
New field Minutes= 1350 (1000 minutes from sum of Peak Usage minutes and
350 minutes from record with rate plan 003).

Use a text box woth an expression like:

=Sum(IIf([Plan ID] = "003", [Off Peak Usage] + [Peak Usage])