Brand New to Access (need help and advice please) (1 Viewer)

Hello, I am brand new to access, but I am not new to the world of computers. I work for a mental health agency that operates a treatment program within a jail and we need a way to track patients, outcomes, etc. I have only worked for this agency about a month and prior to that, they were trying to track over 300 patients in an excel spreadsheet with a few columns. Needless to say that when the jail's director came to ask about anything it takes 2 people half the way to put the information in a report and half the time the numbers are wrong due to human error. As a result, I am trying to build a database that will allow me to run reports that will tell me things such as

1. How many patients are active in the program
2. How many are on a waiting list to get into the program
3. How many graduated successfully

I will also need to be able to run reports that show

County the conviction was in

Court System the patient is involved with

Name of the Judge

Patients incarcerate by that Judge

and that has to be further broken down to show a list of active by that judge, on the waiting list for only that judge, etc.

I am including a photo of the tables and their structures. I know this is not going to be an easy project but our budget is zero and I am tired of spending half a day looking up this stuff, so I am willing to invest as much time as it takes to learn how to do it. I have watched several udemy videos, and lynda.com courses but I have no idea if I am laying these tables out correctly or if I am setting myself up for a disaster as soon as I try to enter 300 patients into it.


Thank you for any help and assistance I am able to get.

In plain English describe a typical day or week in the life of your "business". The "business" is the group of processes your intended database is suppose to support/automate. It is these business rules/facts that determine the relationships between your tables.

Good luck.

It is a program for inmates who have alcohol and drug addiction issues. An inmate comes into the jail and we assess them and provide treatment. When they complete treatment they may be released or they may spend several more months in jail. When they are released we refer them to outside agencies for additional treatment.

The jail accepts inmates from 5 counties in Northwest, Ohio

Henry
Fulton
Lucas
Defiance
Williams

Each county is allotted x number of beds.

for example

Lucas 50 beds
Henry 20 beds
etc

Due to over crowding in the jail, Lucas County may be at 50 beds already and have 10 people waiting to be incarcerated. When this happens the judges need to decide how many people if any can be released to free up bed space

When this happens the director of the jail will ask us to provide him with that information, for example.

Director: Lucas County is at 50 beds. I need a list of all the Lucas County people in the program so I can give it to each judge so they can release people.

I need a list of each judge in Lucas County and under that judge's name, and under the judge's name, I need a column of all active clients they have locked up a column of all completed client who are still incarcerated by that judge.

I have attached an example of what he asks for and what I am in part trying to accomplish with the database

Looking at your relationship screenshot (actually there are no relationships, just tables) I see these things:

1. You don't need tables with only one field of your data (autonumber primary keys don't count as your data). All those tables can go. Instead, you store the value that the ID corresponds to in the external table. That eliminates 7 tables.

2. Consolidate your people. Excluding Patients, all your people tables are essentially the same. You should make just 1 table (let's call it TblAids) to hold those people with an additional field to determine their role (e.g. Judge, Therapist, etc). You might be able to get TblAgencies data in there as well. That consolidates 3 tables into one, but...

3. You need a new table to assign aids to roles for a patient. [Judge], [ProbationOfficer] and [Therapist] come out of TblPatients and instead they become 3 records in a new table: TblPatientsAids. This table will have 3 fields:

[PatientAidsID] - autonumber, primary key
[PatientID] - number, foreign key to TblPatients
[AidID] - number, foreign key to TblAids
[Role] - text, will hold role of the aid (e.g. Therapist, Judge, etc)

4. CourtSystem shouldn't be in both TblJudges and TblPatients. If a judge can be assigned to multiple courts (which is not possible in your current tables), you will need a new table to organize those relationships--like I illustrated in #3. If a judge can only be in one court, then TblPatients doesn't need CourtSystem because that can be determined by the judge they are assigned to.

Plog,

I will give that a try and let you know how it goes. A judge cannot be assigned to multiple courts but one court can have multiple judges that's what I was trying to set up, did I do that wrong?

When I print a report the key items I absolutely have to have are the patient's name, the judge that sentenced them, the court that judge is from, what program they are in (we run several different programs) and their status within that program.


Additionally, we are required to run (although less often) a report showing where people got linked to and if they followed up on those referrals. Eventually (like the next month or so) patients will be assigned a therapist and that therapist will be responsible for maintaining the records in the database for their patients.

If a judge can only be in one court, then you have done it correctly. However, you need to remove [CourtSystem] from TblPatients. Since they are assigned a judge, you will know what court system they are assigned via that.

Okay, I made some changes. I have not combined the tables into one yet like you suggested because I am starting to confuse myself LOL. Am I close to being on the right track? I have things like linkstatus and ProgramName, ProgramStatus in their own table so that if I program name needs to be changed or new programs are added or old ones deleted it can be done through an add/remove form for that table, is that a bad way of doing it? You have to remember that I am dealing with people who have very little knowledge and understanding of computers beyond the basics so it has to be as simple and straightforward for them as possible.

I am including an updated relationship table with the relationships defined. Thank you, everyone on here who has offered help I am very grateful.

You really didn't make any of the changes I suggested, you just added relationships. And some of those are incorrect.

One overriding principle is that there should only be one way to trace a path between tables. You've essentially created circular paths. You need to eliminate them.

I see 2 ways to get from TblPatients to TblProbationOfficers. One is directly the other is indirectly through TblCourts. The same can be said of TblJudges.

I'd review my first post as to changes you should make.