use IF in qry when count is >1 1

[jazminecat23]

Hello all -

I have a query I use to populate a subform. This subform lists all charges against a client, by case. Currently, if one case has multiple charges, it lists them each individually. What I would like it to do is this - if there is only one charge on the case, list the chargetype, but if there is more than one charge on the case, put the word "multiple" in the chargetype field. (There is a details form that pops up in a later step to show them all of the charges individually)

Here's my current code for the query:

SELECT tblNames.NameID, tblMain.OpenedDate, tblMain.ClosedDate, tblChargeTypes.ChargeType, tblMain.[BAC#], tblCharges.ChargeID, tblMain.[CB/CC#], tblCharges.[Violation Date], tblCharges.[Referral Date], tblCharges.DV, tblCharges.PV, tblCharges.[O/C], tblMain.AttyID, tblMain.CaseID
FROM (tblNames RIGHT JOIN tblMain ON tblNames.NameID = tblMain.NameID) INNER JOIN (tblChargeTypes INNER JOIN tblCharges ON tblChargeTypes.ChargeTypeID = tblCharges.ChargeTypeID) ON tblMain.CaseID = tblCharges.CaseID;

Here is an example of the current returned results:

NameID CaseID CB/CC# ChargeType
1 1 1234 [green]Disorderly Conduct[/green]
1 1 1234 [green]Assault 4 DV[/green]
1 2 4456 DWLS 3
2 3 9903 Fail to Sign

Here we can see that person 1 has 2 cases - in case 1 there are 2 charges, in case 2 there is one charge.

person 2 has one case (caseid 3) with one charge.

I would like my query results to appear like this instead:

NameID CaseID CB/CC# ChargeType
1 1 1234 [green]"multiple"[/green]
1 2 4456 DWLS 3
2 3 9903 Fail to Sign

 

[dhookom]

You could try SQL like:

SELECT qryJazmineCat.NameID, qryJazmineCat.CaseID,
qryJazmineCat.CB_CCNum, 
IIf(Count(*)>1,"Multiple",Min([ChargeType])) AS Charge
FROM qryJazmineCat
GROUP BY qryJazmineCat.NameID, qryJazmineCat.CaseID, qryJazmineCat.CB_CCNum;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jazminecat23]

Duane - haven't had a chance to try this yet, but it makes sense and I"ll give it a shot later tonight and let you know. Just wanted to say thanks for your response.

 

[jazminecat23]

Duane, it works great. thanks for your help!