Alumni database, employment history: how to quantify career moves

[Yogibear]

I created an alumni database, to track outcomes of our graduate students. We (and the government!) want to know what career area our alumni first started in and where they are now. I have no *@#$&@ idea how I am going to output this data. We need to answer the question "how many alumni started in BioPharm (variable) and are now in Academia (variable)?"


I do not need to know length of service in each job.

I store employment history in it's own table, with these fields:

PK: employee history ID
FK: employee ID (linked to biographical table)
Employment Start Date
Employment End Date (Null = current job)
Job Title
Employer Name
Career Area (BioPharm, Academia, etc.)

i don't have sufficient Access experience to help myself.....I would love to see if someone can provide guidance for me.

Also, if I am allowed to ask -- if their are any Access consultants out there who live in Central Mass/Northern RI who would be willing to do a site visit - please PM me.

THANK YOU FOR LISTENING!

[June7]

The following will pull all Academia records:

SELECT * FROM History WHERE [Employment End State] Is Null AND [Career Area]='Academia';

The following will pull all records where employment is currently Academia and individual was in BioPharm at some time.

SELECT * FROM History WHERE [Career Area]='BioPharm' AND HistoryID IN (SELECT HistoryID WHERE [Employment End State] Is Null AND [Career Area]='Academia');


BTW, should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be EmpStartDate or Emp_Start_Date.