consolidate queries

[zSplash]

I have many too many queries, which I use to gather data for a report. I
hope someone can suggest how to consolidate them. For example, 8 of my
queries are of the exact same form, except the value of one field is one of
8 possibilities. Here is the SQL for the qCUS query:
SELECT q1Main.DReferred, q1Main.CaseType
FROM q1Main
WHERE (((q1Main.DReferred)>=[Forms]![fgetStats]![tbxBegDate] And
(q1Main.DReferred)<=[Forms]![fgetStats]![tbxEndDate]) AND
((q1Main.CaseType)="CUS"));

The only difference between the 8 queries is that in the qCUS query,
CaseType = "CUS", in the qLTD query, CaseType = "LTD", in the qOTH, CaseType
= "OTH", etc. In my report, I need to list the CaseType totals, so I'm
using the following as the controlSource in the form/report:
=DCount("*","qCUS")

How can I consolidate my 8 queries into a single one, with the only
difference being the CaseType? Or, do I put the criteria for the CaseType
field/parameter in the DCount equation?

TIA

 

[Guest]

Create a CaseType table with two fields - CaseType & Active (Yes/No).

Put the CaseType table in the query like this ---
SELECT q1Main.DReferred, q1Main.CaseType
FROM q1Main, CaseType
WHERE (((q1Main.DReferred)>=[Forms]![fgetStats]![tbxBegDate] And
(q1Main.DReferred)<=[Forms]![fgetStats]![tbxEndDate]) AND
((q1Main.CaseType)=[CaseType].[CaseType])) AND [CaseType].[Active]= -1;

Check as active those of the CaseType table that you want to use in the query.