Counting problem! Please Help!

[Guest]

I have a report that pulls info from a query. The query gets acct_num (count), total_charges (sum), acct_bal (sum), trans_code (group by), trans_date (group by), and expr1: Month(trans_date) (expression). The expr1 prompts the user to enter the number of the month.

In the report I am trying to get the count only certain trans_codes. This is a summary report and for two fields of the summary I need to count 5 different trans_codes in each field. Example: In Total No Precert I want to count the number of accounts for the month entered that have specific trans_codes. There are 5 different trans_codes that relate to No Precert. I need the corresponding dollar amount for each of the two fields.

I can do this by creating a query for each field and then do a subreport in my main report but it asks for the Number of the Month for each subreport. There are 3 subreports that I have.

This is killing me! I have been trying to do this for 2 day without any luck!

Any help will be appreciated!

TIA,

John

 

[Duane Hookom]

If you want the sum of Total_Charges where Trans_Code values are A, B, C, D,
& E:
=Abs(Sum(([Trans_Code] In ("A","B","C","D","E"))*[Total_Charges]))

--
Duane Hookom
MS Access MVP

I have a report that pulls info from a query. The query gets acct_num

(count), total_charges (sum), acct_bal (sum), trans_code (group by),
trans_date (group by), and expr1: Month(trans_date) (expression). The expr1
prompts the user to enter the number of the month.

In the report I am trying to get the count only certain trans_codes. This

is a summary report and for two fields of the summary I need to count 5
different trans_codes in each field. Example: In Total No Precert I want to
count the number of accounts for the month entered that have specific
trans_codes. There are 5 different trans_codes that relate to No Precert.
I need the corresponding dollar amount for each of the two fields.

I can do this by creating a query for each field and then do a subreport

in my main report but it asks for the Number of the Month for each
subreport. There are 3 subreports that I have.

 

[Guest]

Thank you Duane! It works like a charm! Another question, how would I get a count of the accounts with a specific trans_code?

When I use =Count([trans_code] In ("T5051", T3014")) I get all of the accounts. I would like to just get the number of accts for specific trans_codes.

Thanks for you help!

John

 

[Duane Hookom]

=Abs(Sum( [trans_code] In ("T5051", T3014") ) )


--
Duane Hookom
Microsoft Access MVP

Thank you Duane! It works like a charm! Another question, how would I

get a count of the accounts with a specific trans_code?

When I use =Count([trans_code] In ("T5051", T3014")) I get all of the

accounts. I would like to just get the number of accts for specific
trans_codes.

 

[Guest]

Thanks Duane!