Calculation Help - Dsum, Dcount...Iif??

[ImLost]

Hello!

I did a search and can't quite find what I need. I've put together several databases, but still consider myself a novice. I don't know any VBA or SQL.

I have a report based on a query and I need to calculate recidivism totals based on checked boxes for service providers.

Example:
ID# 1:
service provider one = checked
service provider two = unchecked
service provider three = checked
service provider four = unchecked

Recidivism = checked

ID#2:

service provider one = checked
service provider two = unchecked
service provider three = unchecked
service provider four = unchecked

Recidivism = unchecked

So basically, what I need to know is:

Service Provider One = 2 total records, 1 recidivated, 1 non-recidivate
Service Provider Two = 0 total records...
Service Provider Three = 1 total record, 1 recidivated, 0 non-recidivated
Service Provider Four = 0 total records...

The problem I'm having is that I can't get the formulas to count correctly. It is counting all the records regardless of which box is checked. Can you help?

 

[ajetrumpet]

I need to calculate recidivism totals based on checked boxes for service providers.

Example:
ID# 1:
service provider one = checked
service provider two = unchecked
service provider three = checked
service provider four = unchecked

Recidivism = checked

ID#2:

service provider one = checked
service provider two = unchecked
service provider three = unchecked
service provider four = unchecked

Recidivism = unchecked

So basically, what I need to know is:

Service Provider One = 2 total records, 1 recidivated, 1 non-recidivate
Service Provider Two = 0 total records...
Service Provider Three = 1 total record, 1 recidivated, 0 non-recidivated
Service Provider Four = 0 total records...

This looks like it should be a simple DCOUNT situation. One formula for each field in the table. Is Recidivism a separate field in the table?

I would guess that the conditional DCOUNT would be fine in a text box CS, wouldn't it...??

=DCOUNT("[SP1]", "SP table", "[SP1] = -1")

 

[ImLost]

Yes Recidivsim is a seperate field in a seperate table.

I've tried DCOUNT, but can't get it to work. For some reason it is counting all of the records in every instance, disregarding checked boxes and record ID #'s.

 

[ajetrumpet]

Lost,

The description is a little bit vague. Maybe you can post a file for someone to look at? That would help a lot...

 

[ImLost]

Lost,

The description is a little bit vague. Maybe you can post a file for someone to look at? That would help a lot...

This is probably a dumb question - how do I do that? I've uploaded pictures and videos, but never a document-type file. I appreciate your help.

 

[ajetrumpet]

This is probably a dumb question - how do I do that? I've uploaded pictures and videos, but never a document-type file. I appreciate your help.

In the "advanced" reply screen, click "manage attachments". It will give you the file extensions that are allowed.

Make sure to ZIP your file. If the size of the file is too big to be uploaded, it should give you a message with the maximum size.

I think size limit for .ZIP files is like 250K, or something like that.

Compress the database with your WINZIP program. Do you know how to do that?

 

[ImLost]

In the "advanced" reply screen, click "manage attachments". It will give you the file extensions that are allowed.

Make sure to ZIP your file. If the size of the file is too big to be uploaded, it should give you a message with the maximum size.

I think size limit for .ZIP files is like 250K, or something like that.

Compress the database with your WINZIP program. Do you know how to do that?

Yes. I'll have to check to see if this pc has it. (it is the work pc).

In the meantime, maybe this will help:

Service Provider control is in a table called “BaseTable” and one example field is [Prairie Center Qrt Cent]. There are seventeen in all, but on any one report, only up to ten will appear at a time.

Recidivism control is a table called “CrtTable” and the field in question is [Recidivated]

The relationship between the two tables is a one-to-many, BaseTable to CrtTable, and they are linked via BaseTable’s primary key which is a field called [ID #].

I have all of the fields from both tables in my query because I intend to use the same query for multiple reports and just limit the data I show and calculate on each report. The only condition I have on the query is to narrow it by Quarter, which is a field in CrtTable. I have it set to Like [Enter Quarter].

In text box controls on my report, I have the following functions:

To count the number of checked Prairie Center boxes (which does work):
=Sum([Prairie Center Qrt Cent]=Yes)*-1

If the Prairie Center box is checked yes, I need to know how many did NOT recidivate. This what I thought would work, but doesn’t:
=IIf([Prairie Center Qrt Cent]=Yes,DCount([Recidivated],"CrtTable",[Recidivated]=No)*-1,0)

If the Prairie Center box is checked yes, I need to know how many DID recidivate. This what I thought would work, but doesn’t:
=IIf([Prairie Center Qrt Cent]=Yes,DCount([Recidivated],"CrtTable",[Recidivated]=Yes)*-1,0)

I have also tried Dsum in place of DCount, but it’s just not working.

Now, when I try to count just the number of Recidivated boxes are checked, this works:
=DCount([Recidivated],"CrtTable",[Recidivated]=No)

I’ve never had such a problem doing a simple “if, then” count and I am stumped.

I'll try to upload the file, too. Thanks again.

 

[ImLost]

Ok, here is the zip file. There is only the one report and one query right now since I needed to get this figured out first.

 

[ajetrumpet]

Lost,

I would say that you have a corrupt report.

The expressions that are in the CS of the [Prairie Center Qrt Cent] controls are the exact same as for the ones below it, and those work just fine. The fields are also of the same data type. I don't see anything wrong with this:

=IIf([Prairie Center Qrt Cent]=Yes,Sum([Recidivated]=Yes)*-1,0)

Although I don't think it makes sense logically, I have seen that the controls that have the same expression in them work just fine. I'm not sure why you wrote it this way, but that's OK. If the form is corrupt, you can try exporting the object to a new database file. That has worked for me before.

I also tried the DCOUNT and DSUM functions. They result in #ERROR. I'm not sure why this is, because the report is bound.

Are the Prairie Center controls the only ones that are not working (meaning, the controls that have IIF functions in them)?

 

[ImLost]

I've toyed with the fake data to check those out and they don't count correctly either. The Prairie Center control is just the one I've been working with the most. For some reason, it seems to disregard the ID #'s when counting the fields. I'll try to export and see if that works.

 

[ImLost]

Update Added Later:

I abandoned both of the COUNT functions and created a work around for SUM. So far so good. If I experience problems with it, I'll post again. Thanks for all your help. I have no idea why the count functions weren't working properly, but at least SUM is getting me somewhere.

Exporting didn't change anything. I started from scratch and only put in the base essentials to this problem.

I have the base table - "BaseTab" which only contains the primary key "ID #" and two fields: [PCHS_QC] and [JJCC_QC]

Then the other table, "CrtTab" which contains the fields: [ID #] (which is linked to BaseTab in relationships), [Quarter], [Recidivated].

The query contains all these fields and the only condition is for the Quarter, which again, is set to LIKE [Enter Quarter:]. The only quarter entered is Quarter 1 2008.

I made up the report and tried to count the checked boxes as before and it is still counting without regard for whether the box is checked or not.

My fake data is as follows:

ID#1..........PCHS_QC = Y..........JJCC_QC=Y..........Recidivated = Y
ID#2..........PCHS_QC = Y..........JJCC_QC=N..........Recidivated = N
ID#3..........PCHS_QC = N..........JJCC_QC=Y..........Recidivated = Y
ID#4..........PCHS_QC = Y..........JJCC_QC=Y..........Recidivated = N

So the report should calculate this:

Program:.........# Referrals.......#Non-Recidivated......#Recidivated.....%Recid.
PCHS_QC...............3........................2........................1.................33%
JJCC_QC................3........................1........................2.................66%

I'm not having any problem with the % box, that is fine. But I tried using the same formulas as before (adjusted for the new fields and names) and came up with:

PCHS_QC...............3........................4........................0.................0

So it is counting regardless of what I'm telling it. Is there are better method or a function that counts WHEN instead of IF? I know that is what Dcount and Dsum do, but how do they work when the data is in two different tables? Thanks again. I'll attach the dummy file in case it will help.





Added later -

I think the problem is in the DCount function. I've been playing around with the different functions and this one will not work no matter what I try. It is counting all of the records without considering the parameters/conditions. I'm attaching a screen shot (in MS Word) of the report that has the DCOUNT fuctions I'm trying. I changed it just to count the ID #s when the PCHS_QC box = yes and it still counts all of them, and they are both in the same table, so it should work. This is so frustrating.

 

[ImLost]

I'm sorry I never posted back - I ended up adding another field that always equaled 1 and then used Sum to "count" those instead. DCount just wouldn't work, no matter what I tried. Thanks for all your help!

 

[ajetrumpet]

Seeing this thread now reminds me of the weird problem here...

At least you got through it.