Help creating a form

[Nixx1401]

I am creating this database to track inmate info from intake to release. I am in the process in creating the housing/accomodation form but i am experiencing some challenges. the relationship is 1 inmate 2 many housing. what i would like to achieve is when an inmate is moved from one housing unit to another his old cell is shown as available. when he is admitted/moved only available cells are displayed to be selected. any suggestions how i should design this form any code that is needed pleases assist me im a novice in certain aspects of access. thx



Currently i have a cell table listing the amount and type of max cells available

Nixx

[srbond]

You will need a table that lists all unique cells, available or occupied.

Then you will need to set up a table that cross references the inmate table key with the cell table key to maintain history of cells that the inmate has been in along with the most current cell (can be indicated by a boolean value in the table).

That cross reference table can then be used to query against the cell table to see which cells are open and can also be used to query against the inmate table to see the current cell the inmate is in.

At least that is how my jumbled up mind sees it.


Good luck!

[Nixx1401]

I mixed up my previous information here it is

I have attached my db the bed table is the one with the type of cells
the main table is the inmate info table

There are single cells, triple cells and dormitories n they are cell numbers

can you explain your email alittle bit more or what the necessary changes i need to make. i need to create a table with the inmate number and the information from the bed table

Please explain a little bit more

Nixx

[Access_Blaster]

All I can say is WOW! I took a quick peek at your relationships.
I have some thoughts on your project.

First I think you really need to BOIL your tables down just a weeeee bit.
Some tables could be combined to reduce the number.
Example: Ethnicity, Nationality, Religion could be one table.
tblclassification and tblclassification code is another.
Also Date is a reserved word.
What do the prefix str and det mean? Just curious.

Good luck
Richard

[Nixx1401]

str stands for string and dte stands for date

[Nixx1401]

what are your thoughts on inmate cell accomodation need some help with this

[Access_Blaster]

If I were you, I would break all the relationships. Then make a printout of your relationships (see attached). I would sit down at your desk with printout in hand, and start high lighting similar tables. The good thing is you have all the info within this structure. This may take many passes before its more normalized. After that I would set a primary key, a (autonumber) will do just fine. Example of that might be your tblAddress

Your version
strInmateNo
strAddressLn1
strAddressLn2
strCountry

My version
AddressID - Primary Key Autonumber
strInmateNo
strAddressLn1
strAddressLn2
strCountry

After the tables are more normalized with primary keys w/autonumbers. Then you would start thinking about Foreign Keys. The process keeps moving forward.