Help Please?

[KRISH]

I am having two tables [Applicant] and [Institute]

Main fields in [Applicant] are:
[Applicant ID], [FullName], [Programme], [Status]

Main fields in [Institute] are:
[Applicant ID], [Inst code], [Dept code]

Relationship is one-to-many. One applicant can opt more
than one institute.

Now I need institute-wise report, shows No. of applicants
applied, rejected, finally considered under each programme
(Prog1, Prog2). [Status] is Null if application considered
otherwise Rejected if rejected.

Finally considered = Applied - Rejected.

My problem is I am getting result which includes the
number counting all whereever the applicant applies means
say if ID001 applied for 4 institutions then 4. But I want
only 1 for each [inst code] applied.

Note: Applicant can opt more than one department under
each inst.

Any help is greately appreciated. Thanks

Krish

 

[Greg Kraushaar]

There appears to be a flaw with your object model
Add another Table tblApplication

This will have a 1 to many link to both Applicant and Institituion
Applicant can make many Applications
Inst can receive many applications

Status belongs to the Application, not the Applicant. Otherwise,
rejecting one application will reject them all.


The Structure becomes
tblApplicant
AppID - Primary Key
AppName

tblInstitute
InstID - PrimaryKey
InstName

(There appears to be another table here - Department. Is an
application made to a department or an institution? no matter, will
assume Institute for now. and ignore Dept as it is not relevant to the
example)

tblApplication
ApplicationID - primaryKey
ApplicantID -Foreign key to tblApplicant
InstituteID -ForeignKey to tblInstitute
Status - Suggest TriState - Pending, Approve, Rejected


In real life, an application will be made to a course which will be an
object that belongs to an institute, or maybe the application is to
an institute, and can contain a number of courses.

Get the model right, and it will fall into place. get it wrong and it
will become a nightmare to look after.

If my guess is right and this is an assignment, you will probably get
better marks for a good model and poor implementation, than a good
implementaion of a poor model. You would if I was marking it.
In the real world. I make most of my money fixing problems caused by
other people's poor models


To get back to your problem...
The institue simply looks at the applications and counts the various
categories. It doesn't need to know about the applicant at this time

If it then needs to know the applicant for a particular application,
it can get it easily by following the link back.

HTH
Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)

I am having two tables [Applicant] and [Institute]

Main fields in [Applicant] are:
[Applicant ID], [FullName], [Programme], [Status]

Main fields in [Institute] are:
[Applicant ID], [Inst code], [Dept code]

Relationship is one-to-many. One applicant can opt more
than one institute.

Now I need institute-wise report, shows No. of applicants
applied, rejected, finally considered under each programme
(Prog1, Prog2). [Status] is Null if application considered
otherwise Rejected if rejected.

Finally considered = Applied - Rejected.

My problem is I am getting result which includes the
number counting all whereever the applicant applies means
say if ID001 applied for 4 institutions then 4. But I want
only 1 for each [inst code] applied.

Note: Applicant can opt more than one department under
each inst.

Any help is greately appreciated. Thanks

Krish