Urgent help - Counting fields depending on criteria

[Guest]

Please help.
I have a form with mulitple fields (status 1, status 2, status 3) which can
contain one of four criteria (open, pending, closed, cancelled). I need to
count the number of fields depending on the criteria.
i.e total number of fields at "open", total number of fields at "pending" etc

 

[OfficeDev18 via AccessMonster.com]

susie,

Use VBA. initialize 4 separate variables, one for each possiblity. Given the
status of each of the form's fields, increment the value of one of the
variables. When you finish, the value of each variable will be your answer.

Sam

 

[John Spencer]

Since you asked this in the queries forum, I will assume that you need a
query. If you need to do this on a form then a different solution might be
needed.

SELECT Abs(Sum([Status 1]="Open" + [Status 2]= "Open" + [Status 3] =
"Open")) as OPEN
, Abs(Sum([Status 1]="Pending" + [Status 2]= "Pending" + [Status 3] =
"Pending")) as Pending
, Abs(Sum([Status 1]="Closed" + [Status 2]= "Closed" + [Status 3] =
"Closed")) as Closed
, Abs(Sum([Status 1]="Cancelled" + [Status 2]= "Cancelled" + [Status 3] =
"Cancelled")) as Cancelled
FROM [Your table Name]

If you don't know how to use the above, post back and I'll try to describe
the process using the query grid.

 

[Duane Hookom]

Forms don't have fields, tables and queries have fields. Assuming you have a
table "T1", I would normalize this table with a union query and then use a
crosstab.

==quniStati===============
SELECT 1 as StatusNumber, [Status 1] as Status
FROM T1
UNION ALL
SELECT 2 , [Status 2]
FROM T1
UNION ALL
SELECT 3 , [Status 3]
FROM T1;

Then create a Crosstab based of the union query.

TRANSFORM Count(quniStati.Status) AS CountOfStatus
SELECT "All Count" AS Title
FROM quniStati
GROUP BY "All Count"
PIVOT quniStati.Status;

If you want the counts by the status number field:
TRANSFORM Count(quniStati.Status) AS CountOfStatus
SELECT quniStati.StatusNumber
FROM quniStati
GROUP BY quniStati.StatusNumber
PIVOT quniStati.Status;


I would seriously look at normalizing your table structure if possible.