Tables for Dummies

[bndouglas]

Hi there,
Very new to Access, I have used it before, but it was a database that had been built and I just entered data and ran the queries
.
I am currently running the stroke program at 3 local hospitals, which creates a huge amount of data. I have been using Excel as my database but with the amount of records and data I know I need to upgrade.
I am trying to learn everything I can about Access but need some help with tables.

In my mind I would have a patient table, with the primary key being their medical record number since it never changes. This table would have their demographic information.

Then I would have 2 tables for each admission the patient has. One for their emergency department visit, and one for their admission visit if they have one. With each visit the patient has a financial number that changes, but I could use that plus ED for the ED, and A for the admission to differentiate if I had to. These tables would have information regarding how long it took to get testing completed, if documentation was completed correctly, who the nurse and providers were, if certain measures were met, etc.

Another table for EMS information if the patient arrived by EMS.

The I wanted to have another table with each nurse listed so I can track their individual performance as far as numerator/ denominator compliance with documentation measures each month. I don't know if it would be better to have this be a part of the admission/ ED tables and have multiple columns available to add staff as needed or to link the patient table to the nurse table. I only want to track compliance if the patient actually codes out as a stroke patient and some of them may be in the database due to having had a stroke alert activated, but they may not actually code out as a stroke, but I may not know that for a few days and would already have that data in.

A few questions I guess... One, does my table structure seem to make sense on paper?
Two, What would be the best option to track the nurse data? Is it too difficult to track something like that? I want to be able to quickly audit the charts and eventually automate the data running and updating in Excel PivotTables so I can spend more time with patients, so if it isn't possible to do it without a lot of manual work it just won't happen.

Any help would be greatly appreciated.

 

[Micron]

1) No, not really, but one needs a thorough understanding of the process and how the db is supposed to support that.
2), and really the rest of the scenario, you need to study database normalization. It might help to do so while keeping in mind that the business/process is usually what makes something an entity, and those things are often intangible. F'rinstance, a person is an entity, and every characteristic of that person is an attribute. That might be DOB, FName, LName, etc. but it is very seldom something intangible such as any visit related data. A visit therefore is likely an entity of its own, with attributes (e.g.) location, date/time, reason... Things like reason, which is often one of a multiple possibilities, are often supported by lookup tables (not lookup fields), so they are often foreign key (FK) values, which is usually the same value that comprises the Primary Key from the parent table - an entity in itself.

Rather than confuse with a whole lot of unfamiliar concepts and terminology, research normalization and make sure you grasp it. Don't settle for one teacher if that info doesn't do it for you. I might as well give you the whole enchilada since you're not likely up to speed on any of the most important things. These links can save you a lot of headaches later.

Normalization Parts I, II, III, IV, and V

http://rogersaccessblog.blogspot.com...on-part-i.html

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?

http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
Naming conventions - General: Commonly used naming conventions

https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names - Microsoft Access tips: Problem names and reserved words in Access
About Auto Numbers
- UtterAccess.com
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

 

[bndouglas]

Thanks. I'm a nurse so this is a whole new side of things for me. Unfortunately, the multibillion-dollar company I work doesn't have anything already in place so I'm trying to learn on the fly.
Thank you for all the links. I'd rather take longer to build this right than throw it together and have constant problems later.

 

[Micron]

With what is going on this year, I have to wonder how you have any spare time for such a project. What you describe would be a challenge even for some experienced Access developers. My advice is that when you think you grasp normalization, get a pencil, a good eraser and some large (at least flip chart size) sheets of paper and pencil out your tables, their field names, and even draw lines to show the relationships. Make notes as you go when you have doubts or questions and then find the answers. Seriously, seasoned developers do this. We say that if you can't draw it out as it should be, you cannot build it.

 

[Legacy 456155]

Paper is my friend.

 

[bndouglas]

With what is going on this year, I have to wonder how you have any spare time for such a project. What you describe would be a challenge even for some experienced Access developers. My advice is that when you think you grasp normalization, get a pencil, a good eraser and some large (at least flip chart size) sheets of paper and pencil out your tables, their field names, and even draw lines to show the relationships. Make notes as you go when you have doubts or questions and then find the answers. Seriously, seasoned developers do this. We say that if you can't draw it out as it should be, you cannot build it.

Well, I had a good set up in Excel that seemed to be working for me. It calculated what I needed, and I had PivotTables that auto-updated dashboards for each site every month. Unfortunately it seems like each time my work updates Office, which is every few months, I have to go back and re work something in it, due to getting error messages, even when nothing has changed, except the update.
I was told Access wouldn't be as susceptible to problems like that, so I've been reading everything I can, watching YouTube videos, and experimenting, usually while my daughter is in Tae Kwon Do, or while I'm at home watching TV. I do enjoy the data part of this job and making it useful for others, so I'm hoping I can eventually figure this out or sign up for an actual course.

I'll try to work on paper. I did try it on notebook paper but I like the idea of actually seeing lines that link everything. Something visual like that would likely be a lot more useful for me.

 

[Micron]

Not sure if you can still find flip chart paper, but it would be ideal IMHO. I use sheets that movers use to wrap stuff but it's not very white.
I can identify with your zeal to learn Access. I'm mostly self taught and doing so opened up doors for me at work. My crowning achievement in making myself valuable with it was when I was able to reduce something that took 3 guys on 8 hours OT ($1,600) down to 1 person 10 minutes during a regular day. Best part was that for the most part, no one really wanted the OT job screwing up their Saturday or Sunday.

Since you don't seem to be under any urgent deadline because you have something you can fix every once in a while, you should take the time to learn the foundation before you try to erect any walls if you get my drift. I'd advise getting a book to read during those TKD lessons (assuming you're not supposed to be showing a lot of interest in what's going on). I think you'll soon agree that you didn't even know what it was that you didn't know.

 

[jackd]

*bndouglas,

I realize it's been a week or so since you've logged into the forum, but I want to emphasize the importance of what micron has suggested. Excel(spreadsheet) and Access/ACE (database) are very different animals -based on different object models and serving different audiences. That is to say different concepts are involved.
Too many people new to database think that the software will magically create a database for them. Don't believe it. It doesn't work like that. It is critical to understand the business (processes and facts) that you are trying to support with a database. Here is a link to several articles and tutorials re Database Planning and Design. I recommend the tutorials from RogersAccessLobrary mentioned in the linked materials to learn and experience Normalization. There are several related subjects within the materials.
Good luck with your learning and project.