Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-15-2017, 12:54 PM   #1
scampbell70
Newly Registered User
 
Join Date: Jul 2017
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
scampbell70 is on a distinguished road
Brand New to Access (need help and advice please)

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.
Attached Images
File Type: jpg Relationships.JPG (98.3 KB, 37 views)

scampbell70 is offline   Reply With Quote
Old 07-15-2017, 01:27 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,723
Thanks: 36
Thanked 1,713 Times in 1,664 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Brand New to Access (need help and advice please)

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is online now   Reply With Quote
Old 07-15-2017, 01:58 PM   #3
scampbell70
Newly Registered User
 
Join Date: Jul 2017
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
scampbell70 is on a distinguished road
Re: Brand New to Access (need help and advice please)

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
Attached Files
File Type: doc Example.doc (44.5 KB, 23 views)

scampbell70 is offline   Reply With Quote
Old 07-15-2017, 10:16 PM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,115
Thanks: 10
Thanked 1,950 Times in 1,911 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Brand New to Access (need help and advice please)

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 is offline   Reply With Quote
Old 07-16-2017, 04:11 AM   #5
scampbell70
Newly Registered User
 
Join Date: Jul 2017
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
scampbell70 is on a distinguished road
Re: Brand New to Access (need help and advice please)

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.
scampbell70 is offline   Reply With Quote
Old 07-16-2017, 06:14 AM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,115
Thanks: 10
Thanked 1,950 Times in 1,911 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Brand New to Access (need help and advice please)

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.
plog is offline   Reply With Quote
Old 07-16-2017, 07:16 AM   #7
scampbell70
Newly Registered User
 
Join Date: Jul 2017
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
scampbell70 is on a distinguished road
Re: Brand New to Access (need help and advice please)

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.
Attached Images
File Type: jpg Capture.jpg (92.2 KB, 26 views)

scampbell70 is offline   Reply With Quote
Old 07-16-2017, 08:15 AM   #8
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,115
Thanks: 10
Thanked 1,950 Times in 1,911 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Brand New to Access (need help and advice please)

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.

plog is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Brand New to Access Chookz Introduce Yourself 3 09-18-2014 03:03 PM
Brand new to Access & Looking for advice jholly Introduce Yourself 1 09-12-2014 09:21 AM
Question Brand new to access - PDF / Email button? Malonik General 3 10-02-2013 06:54 AM
Brand new, here... medicalparticles Introduce Yourself 0 11-17-2010 06:21 AM
Brand New To Access foxfinance General 4 04-02-2008 05:16 AM




All times are GMT -8. The time now is 11:47 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World