If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
how should i set this up?
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 |
#3
|
|||
|
|||
how should i set this up?
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 |
#4
|
|||
|
|||
how should i set this up?
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 |
Thread Tools | |
Display Modes | |
|
|