Sum Data with multiple criteria in Reports 1

[trwebster]

I'm trying to count how many offices opened cases between 1/1 and 6/30.

My query is set up with these fields: office, disposition_status, and open_date.

In my report, for 1 organization I used:

=Sum(abs([office]="As" and [disposition_status] Between "001" and "002" and [open_date] Between #1/1/2004# and #6/30/2004# or [office]="Am" and [disposition_status] Between "001" and "002" and [open_date] between #1/1/2004# and #6/30/2004#))

This expression seems to work. But for our second organization, there are 5 offices I need to count from and when I put in:

=Sum(Abs([office]="De" And [disposition_status] Between "001" And "002" And [open_date] Between #1/1/2004# And #6/30/2004# Or [office]="Fr" And [disposition_status] Between "001" And "002" And [open_date] Between #1/1/2004# And #6/30/2004# Or [office]="Lm" And [disposition_status] Between "001" And "002" And [open_date] Between #1/1/2004# And #6/30/2004# Or [office]="Sa" And [disposition_status] Between "001" And "002" And [open_date] Between #1/1/2004# And #6/30/2004# Or [office]="To" And [disposition_status] Between "001" And "002" And [open_date] Between #1/1/2004# And #6/30/2004#))

I get #Error. If I put the Sum(Abs( before each office section, I get -1 (the total should be 1).

Any ideas on how I can write an expression that will pull on 5 different offices with the same disposition_status and date criteria?

 

[dhookom]

Try:
=Sum(Abs(Instr("DE~Fr~Lm~Sa~To",[office])>0 And [disposition_status] Between "001" And "002" And [open_date] Between #1/1/2004# And #6/30/2004#) )

Also, I don't like hard-coding office values into expressions. I would have a table of offices with a field that determines whether it should be included in this count or not.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[trwebster]

Duane,

Thanks a bunch. It appears to be working; and you have given me another expression string to explore. I took a few of those adult ed Access classes, but they just don't get very deep, so I'm still more self-taught than anything. I appreciate all the help I can get.

 

[dhookom]

I'm self taught also but have probably been self-teaching much longer than you ;-)

Give some serious thought to the second part of my answer. If you add or edit an office, you should not have to modify expressions. For instance, if you want to include "Mp" in your offices for this condition, you should be changing data values, not expressions.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[trwebster]

I think my query is too broad for that. The database does have an offices table, but my query is pulling anything that had to do with a tax case for the first half of the year. Then, in my report, I was summing the different activities: cases opened, closed, denied, etc. by the date criteria. The database is for 2 organizations: 1 organization has 2 offices; the other organization has 9 offices. But, that does give me the idea of creating a table that says what organization each office belongs to. Then I could just use the organization table instead.

Gee, more ideas. Thanks.

 

[dhookom]

Why not add a field to the Office table that identifies the organization?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[trwebster]

I don't think I have the access to do that. It's not my database; I just use it. If I changed anything, the IT Manager and her supervisor would probably have kittens.

If you have the time, I have another issue. I work in legal services and my queries almost always include the client name and case number. I have a timeslip query that shows all time entries for each case number. Our clients frequently have more than one case, so I need something that will look at the client name and case number and if the client has more than one case, to put "Yes" in a field or "No" if it's a unique combo. Any ideas?

 

[dhookom]

I don't have time to guess if this is on a form or report or query and needs to be updateable or not.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[trwebster]

It's on a query and does not need to be updateable.

If you can help, that'd be great. If not, thanks for the other help.

Tina

 

[dhookom]

Create a totals query that groups by client and counts the number of cases. Add this totals query to your query and join the Client fields. This should expose the count of cases in your query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.