Very simple Access database help

py2006

Hi there,

I used to be pretty nifty with an Access database back in the day but I am a bit rusty so a little help/advice here would be appreciated. (I hope this is the right thread?).

In work I am creating a simple database that contains:

• A list of current students.
• A list of employers currently being used for work experience
• A list showing progression (students who have left and gone to another course or got a job.

The database should be able to provide the following info:

• What students are doing what modules.
• What students are currently on work experience with an employer from the database.
• Which students are not.
  

I think a lot of the info can be drawn using simple queries etc. I think once the tables are set up and linked via a relationship that process will follow easily enough.


So I am thinking a table with a list of all students with the primary field being their PPS number.

A table of employers that are being used for work experience.

But I am not certain as to how to link these tables via a relationship? As in what should be the linked fields?

Thanks in advance.

kidneyfan

I would suggest a Students table studentID, PPS, FNAME ,LNAME
An employers table employerID, BUSNAME
A work experience table ID, employerID, studentID,StartDate, EndDate

You join studentID to studentID with an inner join and employerID to employerID with an inner join

py2006

kidneyfan wrote: »

I would suggest a Students table studentID, PPS, FNAME ,LNAME
An employers table employerID, BUSNAME
A work experience table ID, employerID, studentID,StartDate, EndDate

You join studentID to studentID with an inner join and employerID to employerID with an inner join

Thanks a million, I will tinker with that and see how I get on.

The Corinthian

Try something like this:



I've left out the bulk of the data fields you'll be using and concentrated more on the relationships between the different entities. Of note:

• tbl_students.student_id could be their PPS number.
• Student modules are covered using a many-to-many relationship.
• Work experience employer is covered by a 1-to-many relationship as you've not specified that they can have more than one work experience employer in the course of their course. If they can, then you'll need to introduce a many-to-many relationship between tbl_students and tbl_employers, as well as 'from' and 'to' datestamps in the look-up table.
• In the case of no employer or "gone to another course or got a job", you could use a combination of flags in tbl_students, not inforce a value for tbl_students.employer_id, or even create special employer entries for such cases.

Hope this helps.

py2006

Thanks for your help. I have a quiet day in work tomorrow so I will put it into practice.