Access Relationship Question

Frisbee

Basically stuck on the relationships for a database.
What I have so far is:

Applicant: Applicant Reference, FName, LName, Phone number, Skills
University: Uni ref, Uni Name, Contacts name, address, phone.
Job Description: Job Ref., University ref, Job Type, Skills
Interview Details: Interview ref, applicant ref, uni ref, date, skills.

Relationships:
(1) One Uni can request many interviews from applicants for a job description.
(2) One Applicant can receive many interviews in relation to a job description.

The Relationships I have so far are:


Applicant+University in a M:M i nto Interview through App ref and Uni ref. Then Skills linked to Interview from Job Description.
(Screenie Below as I can barely understand that having written it...)

I'm fairly sure this is wrong though. But not exactly sure where. Can anyone help me out?

MoominPapa

Your going wrong with the Skills joining back to the Job Description Table.
The join from the Interview details to Job Description should be on Job Reference not skills. You should then join University Details with Job Description not Interview Details.
Reckon you'll be all right after that or at least on the right track

none

I don't think you can be on the right track with a Skills field as it violates the normalisation rules. Linking on such a field is even worse. Basically, every field must be about one fact, not more. What you have to do is create a Skill(s) table and something like ApplicantSkill(s) table to map every applicant with appropriate skills. Then you remove skills from all the other tables and it should be more or less OK.

MoominPapa

Frisbee, none is correct, but you'll need to normalise more than just Skills. My solution would have produced results but not a full solution. Normalisation, which should be your starting point, would have been my next suggestion

KStaford

Here is a very basic solution, One thing that you probably need to record, is applications. An application can be seperate to an interview. i.e. all applicants submit applications for jobs. Some applications will be successful and reach interview stage but other applications will not (perhaps their cv is not upto scratch etc) so it may be a good idea to include a seperate applications table.

The ERD I enclose allows the following

Universtites post jobs
Applicants submit applications for jobs
A Job can attract many applications
An application can be for many jobs
A successful application can go forward for interview
A successful application can go forward for subsequent interviews

It may not be great to allow the m:m relationship between applications and jobs. It may be more accurate to say that 1 job can attract many applications whilst 1 applications can only be for 1 job.

Without a decent description of what exactly you are trying to do, we can only make assumptions as to an effective ERD. You need to be more precise. Its a good exercise for yourself - write out in a paragraph or two exactly what you wish to do.

Frisbee

Thanks everyone, its getting clearer now. im sure I'll have another question soon enought though...

Frisbee

@ KStaford. Or anyone else who can help me.
I'm using access 2003 and I think the relationships work differently in this version. I'm guessing the '11' and the symbol that looks like 2 zeros mean one-to-one and one-to-many respectively?

Also I'm having a problem with my queries all of a sudden. They were fine but I've obviously changed something and I can't find what.
One of the queries is:

"Find an Applicant by name or reference Number."

So using the Design View I put Name and Reference Number into the Query both with a criteria of "ENTER".
However I'm getting this error constantly.
"Index or Primary Key cannot contain a Null Value"