Count Prob.........??

[KRISH]

Hi!

Everybody.

My database is having table1, fields are: ID, AppName,
DeptChoice1, DeptChoice2, Recommended1, Recommended2.

Now I want a report showing no. of applicants applied,
recommended, rejected under each department.

Both the choices should be taken under a common dept name
means should not be separated according to their choices.

Any help is greately appreciable.

Krish.

 

[Douglas J. Steele]

You're running into the sort of problem that usually happens when you have
unnormalized database tables.

You should never have a repeated group (DeptChoice1, DeptChoice2): you
should store those as 2 separate rows in a related table.

If you're stuck with the design, try creating a query that normalizes your
data:

SELECT ID, AppName, 1 As Choice, DeptChoice1 As DeptChoice, Recommended1 As
Recommended
UNION
SELECT ID, AppName, 2 As Choice, DeptChoice2 As DeptChoice, Recommended2 As
Recommended

and use that query for subsequent reports.

"Columns are expensive, Rows are cheap!"