how should i set this up?

[_Bigred]

I have a table

UnitData that contains the inmateName, inmateNumber,inmateCell and a bunch
of other data. I want to create a seperate table (or should I) that would
contain all the different housing unit jobs: janitor,recycler,servery lead,
laundry,library etc..

I ultimately want to generate a report (listing) of all the housing unit
jobs and what inmateName,inmateNumber,inmateCell and his jobCode,payrange
and hire date.

should i create this in 1 table (just add to my primary table) or put all
the job info in a seperate table and have lookup fields that pull from the
UnitData table?

I would also like to setup the form that will be used to manipulate the
data - that will get sent to the report so that when I input the
inmateNumber it will auto-fill the remaining data into the form fields in
the job table??

I hope this is understandable. I need to make sure that whatever I do I
cannot delete the inmate from the UnitData table unless he actually is
removed from the housing unit (he remains on the unit even if he is not in
the job).

Just want to avoid problems, if I can avoid them at the front end of things.

TIA,
_Bigred

 

[tina]

to decide on the proper table setup, you need to determine two things: can
an inmate hold more than one job at the same time? and, are you tracking ALL
of the jobs an inmate is assigned to over time, or ONLY his current job
assignment?

if you answer yes to the first question, or answer "all of the inmate's job
assignments over time" to the second question, then you should have a
minumum of three tables:

tblInmates
InmateNumber (suitable as primary key, perhaps?)
InmateFirstName
InmateLastName
<other fields that describe an inmate.>

tblUnitJobs
JobCode (again, suitable as primary key, perhaps?)
JobName
<other fields that describe a unit job.>

tblInmateJobs
InmateJobID (primary key)
InmateNumber (foreign key from tblInmates)
JobCode (foreign key form tblUnitJobs)
HireDate
FireDate (the inmate's last day at that job)
<other fields that describe that specific job as related to that specific
inmate, at that specific time.>

recommend that you do not use any Lookup fields in any table in your
database. see http://home.att.net/~california.db/tips.html#aTip8 for more
information.

hth

 

[_Bigred]

the inmate can only hold 1 job at a time, and I only need to track the
hireDate. I won't be tracking any inmates over time, I simply want to create
a form & report to manage what inmates are in the job. When they get moved
off the housing unit they would no longer have the job, it gets filled by a
new hire.

The Job Title, JobCode and PayRange for the job never change, they stay the
same. I would only need to pull the inmateName,inmateNumber,InmateCellNumber
from the OpenData table that already exists in the db.

Thanks,
_Bigred

 

[tina]

then you can probably get by with two tables:

tblUnitJobs
JobCode (pk)
JobTitle
PayRange

tblInmates
InmateNumber (pk)
InmateFirstName
InmateLastName
JobCode (foreign key from tblUnitJobs)
HireDate

the advice about Lookup fields in tables is the same.

hth