how should i set this up?

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

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" m wrote in
message ...
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

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" wrote in message
...
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" m wrote in
message ...
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

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


"_Bigred" m wrote in
message ...
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" wrote in message
...
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" m wrote in
message ...
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