Nested IIF statements?

[laavista]

I'm trying to display the number of active cases for each CaseCode

fields:


CaseID, number long
casecode, text, 3 char
CaseStatus, text, 1 char (A or I)

data:
CaseID
CaseCode
CaseStatus
1
SOC
A
2
TMM
A
3
SOC
I
4
SOC
A

I need to display the number of active cases , e.g.,
SOC : 2
TMM: 1

In a form, I put the following in a text box and it sums the total number of active cases:
=sum(IIF(CaseStatus = "A",1,0)) results in A=3, but now I need it to provide the total active for each CaseCode.

I'd really appreciate any suggestions.

[ConneXionLost]

Just use a query:

SELECT tblCase.CaseCode, Count(tblCase.CaseCode) AS ActiveCases
FROM tblCase
WHERE (tblCase.CaseStatus)="A"
GROUP BY tblCase.CaseCode;

Cheers,

[laavista]

Thanks so much for responding. I apologize, but I did not give you my table definitions so the query does not work.


I have two tables:
tblCase,
field: CaseID, number, long (key)
field: CaseCode, text, 3 char
(plus additional fields)

tblCaseInfo,
field: CaseID, number, long (key)
field: CaseStatus, text, 1 char (A or I)
(plus additional fields)

tbl_case is a 1 to many to tbl_caseinfo


The SQL I tried was:
SELECT tblCase.CaseCode, Count(tblCase.CaseCode) as ActiveCases
from tblCaseInfo INNER JOIN tblCase on tblCaseInfo.CaseID = tblCase.CaseID
Where (tblCaseInfo.CaseStatus)="A"
Group by tblCase.CaseCode;

I get the error "You tried to execute a query that doesnot include the specified expression "CaseCode" as part of an aggregate function" when I try to execute the query.

If I add the field: Casestatus to the query, I get "enter parameter value tblCase.CaseStatus.

Any suggestions?

[ConneXionLost]

There's something here that doesn't make sense.

You appear to be using the same Primary Key in both tables, then you state the relationship is one-to-many; that can't work without violating the uniqueness of the key in tblCaseInfo. If however, you mean that CaseID is only a "Foreign Key" in tblCaseInfo, then the key arrangement is better, but you're using an Active/Inactive status field in tblCaseInfo as well. This causes a logic conflict where you might have simultaneous Active and Inactive statuses for the same case.

Please clarify.

[laavista]

You're right. The foreign key is "CaseID"--it was a typo

I really need to think about how I present my question, and then ensure I type the fields, etc. correctly.

Sorry.

[laavista]

To clarify:

I have two tables:
tblCase,
field: ID, number, long (key)
field: CaseCode, text, 3 char
(plus additional fields)

tblCaseInfo,
field: CaseID, number, long (key)
field: CaseStatus, text, 1 char (A or I)
(plus additional fields)

tbl_case is a 1 to many to tbl_caseinfo

[ConneXionLost]

At this point I'll just assume you have a CaseInfoID primary key in tblCaseInfo as well as the CaseID foreign key; but, based on your last post, it's no longer a concern.

Can you explain the second part of my question? It boils down to asking: "Why don't you have your CaseStatus in tblCase?"

[laavista]

This is an 'inherited' database and at this point cannot be changed. I have to work with what I've been given. I hope to do a redesign in the near future, but for now I have to get this one task done.

Any suggestions?

[ConneXionLost]

In that case, is there any other field in tblCaseInfo that might identify the "current" CaseStatus? For example, assume these records exist in the tables:

tblCase

CaseID	CaseCode
1	SOC
2	TMM
3	SOC
4	SOC

tblCaseInfo

CaseInfoID	CaseID	CaseStatus
1	1	A
2	2	A
3	3	I
4	4	A
5	1	I
6	4	I
7	1	A

This is possible due to the one-to-many relationship between these tables. If you queried these tables, you would find multiple CaseStatus for cases 1 & 4. Which ones are current?

[laavista]

Thanks to your questions, I was able to solve this.
Thanks for taking the time to help me.