Count ......

[KRISH]

Hi!
any one help me.

I am having two tables [applicant] and [Instt] to store
applicant details in [applicant] and institute details in
[instt].

Now I want to count Males and females selected.
Problem is an applicant can apply more than one Instt. and
he may also selected at more than one Instt. Hence my
counting value is duplicating, if he selected at more than
two places. Help how to get it solved.

Thanks for any help

Krish.
..

 

[Steve Schapel]

Krish,

An Applicant may be involved with more than one Instt, but this does not
mean that they should be entered more than once in the Applicant table.
Each Applicant should be listed only once in the Applicant table, no
matter how many Instts. Therefore, assuming you have the Sex field in
the Applicant table, there should be no problem to count males and
females. Therefore I don't really understand the question... can you
please explain further?

 

[KRISH]

Hi Schapel,

Thanks for immediate reply,

You are absolutely correct sex is in applicant table.
status of applicants selection is in instt table. Now I
joining both tables for getting results of selected
applicants only. problem is if an applicant sellected at
more than one instt. say if A001(Male) has selected at 3
instts. then I am getting 3 as count value instead of one.
Hope I make you under stand. Thanks for your help

krish

-----Original Message-----
Krish,

An Applicant may be involved with more than one Instt, but this does not
mean that they should be entered more than once in the Applicant table.
Each Applicant should be listed only once in the Applicant table, no
matter how many Instts. Therefore, assuming you have the Sex field in
the Applicant table, there should be no problem to count males and
females. Therefore I don't really understand the question... can you
please explain further?

--
Steve Schapel, Microsoft Access MVP

Hi!
any one help me.

I am having two tables [applicant] and [Instt] to store
applicant details in [applicant] and institute details in
[instt].

Now I want to count Males and females selected.
Problem is an applicant can apply more than one Instt. and
he may also selected at more than one Instt. Hence my
counting value is duplicating, if he selected at more than
two places. Help how to get it solved.

Thanks for any help

Krish.
.

.

 

[Steve Schapel]

Krish,

Ok, thanks for the explanation.

The most straightforward way to do this is in two steps. Here's one
approach...
1. Make a query based on your two tables, joined on the field from each
table that identifies the person.
2. Add the person field and sex field from the applicants table, and
the field that identifies selection from the Instt table, to the query
design grid
3. Apply your criteria to select only those applicants 'selected'.
4. Make this a Distinct query: right-click anywhere on the background
in the top panel of the query design window, select Properties from the
popup menu, and then set the Unique Values property to Yes.
5. Save and name this query. The SQL view (substituting your own
actual field names of course) will be something like this...
SELECT DISTINCT Applicant.PersonID, Applicant.Sex
FROM Applicant INNER JOIN Instt ON Applicant.PersonID = Instt.PersonID
WHERE Instt.Selected = -1
6. Make a new query, based on this first query, and add both fields to
the query design grid.
7. Make it a Totals Query (select Totals from the View Menu.
Leave Group By in the Totals row of the Sex field, and put Count in the
Totals row of the PersonID field.
8. Run this query, and check it gives the expected results.