Direction needed.....

[EVS Director]

Hello,
I am an Environmental Health and Safety Director for a Nursing Home. I am flooded with about 50 pages of handwritten data that I have to "Report" on every month. Granted the Data is well documented for the month but after trying to sort through quarterly info....it renders itself useless.


I need a database and I know Access can do it but the problem lies within the user....ME! I have bought 3 books and sat thru hours of training online but I just can't grab the concept mainly I guess because the tutorials do not really apply to me.
I am asking for help setting up the initial tables. I think I can work from there if I can just get two of my tables to work together.This database will have much more info added but I can't get the main function of what I need it to do to work.

I have a Resident Info Table
Field Name and EXAMPLE DATA
_________________________
*PK Resident ID-1945
First Name- Bill
Last Name- Smith
Date Admitted-6/20/10

Room Table
_________
*PK Room #- 101A

Here's what I need to happen:
Bill Smith gets admitted to room 101A on 6/20/10. This is information I never want to lose.
A week goes by and Bill doesn't get along with his roomate and asked to be moved to room 102B. This room is empty, we move him and I want to document that information as well but update the report to where he is currently at.
Let's say some more time passes and Bill's current roomate develops MRSA (an infection) and needs to be moved out of the room.....but this time he needs to be SWAPPED with another resident. The other resident we are going to swap him with also has MRSA and can be placed in that room. SO Bill Smith trades rooms with John Doe. So Bill (102B) goes to John's room (130P) and vice versa. I need to document this but I do not want to lose any of the info of where they were but I do need the info to update to show where they are currently.

Here are some rules to follow:
1) ONLY ONE RESIDENT CAN BE IN ONE ROOM at any given time. The same is true for a room, ONLY ONE ROOM CAN HOLD ONE RESIDENT AT ANY GIVEN TIME.
2) ONE RESIDENT CAN BE IN ANY OF THE 166 ROOMS we have available.
3) Any of the 166 Rooms can hold any resident
4) No one can predict when a resident may discharge or become deceased so having an end date to when my room may become available is not known, BUT once a resident leaves and I discharge that resident, I want the system to update and let me know (via query or report) that I can use that room again for someone else.
5) Bill Smith was admitted on 6/20/10 in my example but lets say he went home on 6/25/10. Bill comes back to the facility on 9/1/10. I would still like to be able to look up the info from when Bill was here thru his first stay if possible.

If this is something TOO complicated for an expert to set up, please let me know because I am rookie and if you couldn't do it, more than likely I can't either.

Thanks for trying and taking your time to look at this......
Sincerely
Frustrated EVS Director

[evander]

This type of system is comparable to a school enrollment system. I am impressed by the way you clearly defined your requirements.

I believe this is a simple system because it doesn't involve financial data. I'll try to give you a copy of my experiment when I have free time.

[EVS Director]

Thanks Evander!!!!! At least I know what to look for.....I will patiently await your reply but I am going to check out some other school enrollment databases.....I tried hotel booking and resource scheduling but they are not really getting the job done.....I REALLY REALLY REALLY appreciate your help.....i have been looking into this since DEC '09 and after exhausting myself I have decided to turn to outside help.

[ConneXionLost]

Here's a very basic model to tinker with.

Cheers,

[evander]

Kekekeke. ConneXionLost, may I use your model too? Actually it's my first time to get my hands on these types of databases. Thanks.

[EVS Director]

Thanks ConneXionLost. I really appreciate that you have used my examples.........I didn't know about the 3rd table or how to set it up but I can see how you did it in the relationships......I really really appreciate your help.

[EVS Director]

ConneXionLost-
How in the world did you make that occupany table? Yours works fine but when I try to make one it doesn't work like yours does.

Here's what I am doing so far.....
I create a table and drag the Resident_ID number over...it shows me the actual number and not the name. I created a query very similiar to the one you made and I can make it do what yours does and show the names.....Doe,John....I tried to use that query as the look up field but it doesn't seem to work....I still get the ID number instead of the names......

Can you possibly walk me thru creating that particular table? I am not sure if i am suppose to add Existing Fields or a Lookup Column......

[ConneXionLost]

Sure!

The important thing is that you are using matching keys (both numbers in this case).

The combo box and supporting query are really just for looks and to make it easier to fill in the table (pick a name instead of a number) but, it's important that THE TABLE STORES A NUMBER!

I suspect the only thing you're missing is with the bound column and it's width. Go to the table design and select the ResidentID foreign key, then find the properties for the combo box (I don't know how Access 2007 has this area laid out), in Access 2003 this is on the Lookup tab. There you will note these items:

Bound Column is: 1
Column Count is: 2
Column Widths is: 0cm; 5cm (or your equivalent measurement system)

This means the table will store the first column of info from the query (the ResidentID number) and, the first column is hidden, but the second is visible.

Let me know if you're still stuck.

Cheers,