> I've got three tables that I'm working with... the first is a table of
> staff members... firstname, lastname, staffid... nothing tough... the
> second is a table of jobs... job title, jobid... nothing tough... the
> third is a history of job assignments... it's got a staffid, a jobid, a
> timestamp, and a field to denote whether the job was added or dropped...
> this third table exists because it is useful in this project to be able
> to look at a staff member's job history...
...
> Any suggestions?
Perhaps not a helpful one... but I would be inclined to reorganize that
third table to have a "start date" *and* a "stop date" field, and ditch
the "dropped" boolean:
o it would keep you from having to figure out how to associate two
entries in the same table with the same job (the added and dropped
rows). How would you currently prevent a job from being entered as
"dropped" without a corresponding "added" row?
o it would make it easier to do the query you are asking about; to tell
whether someone has an active job, just select on a date -- like 'today'
-- between the start and stop dates.
o it may better match reality; jobs have a duration so represent that
explicitly.
hth
- Thomas