Handling Records with many Fields

[Kyle Genova]

I do not know much about creating access databases, so hopefully my problem has a simple solution!



I am trying to create a database using Access 2010 for a doctor's office to hold patient records. I am NOT a professional; I'm just trying to help out and have learned a little about access (and I am a CS major in college). The ideal database for their needs is relatively simple internally; there are patients, and many various forms and reports which display and allow the editing/printing of certain parts of a patient's medical history. My problem is that intuitively, I would create a single table with fields for all of a patient's information, including diagnoses, memos for doctors' and clinicians' notes, emergency contact info, billing info, etc. However, this appears to be impossible to do so straightforwardly as there are far too many specific pieces of information about each patient to fit in a single table- in total there are about 500 discrete pieces of information that need to be stored about each patient. Any one form may need to display approximately 250 pieces of information about the patient. No more than 5-10 people need to access the database at once, and there are about 17000 patients currently on record. How should I go about this? It was easy enough to create tables for various diagnoses, medical codes, etc., but I can't figure out how to store so much information about a single patient because a table can't hold that many fields. I have a feeling queries could help, but I'm not sure how exactly or if there is a better solution.

Thanks for the help!

[pbaldy]

Your first step is to learn about normalization. Here's one link among many:

Fundamentals of Relational Database Design -- r937.com

Normalization is the foundation the application is built on. You're describing a spreadsheet, not a relational database. Get the table design right, and the forms will be easy.

[June7]

This will be more complicated than you wanted. Necessary tables, at a minimum:

tblPatients
PatientID (primary key)
PatientLast
PatientFirst
Address
City
State
Zip
etc.

tblVisits
VisitID (primary key)
PatientID (foreign key)
VisitDate
Complaint
Diagnosis
Comments

tblCharges
ChargeID (primary key)
VisitID (foreign key)
ChargeCode

tblInvoices
InvoiceID (primary key)
ChargeID (foreign key)
InvoiceDate

I am sure there will be more.

Google: access database template patient history
Maybe this will help http://office.microsoft.com/en-us/te...010286214.aspx

[Kyle Genova]

Truth be told, what I am really looking for is a spreadsheet with a nice GUI, and it seems Access is not designed for this? There is no logical breakdown of the parts of the database for normalization- I am really looking for a way to store a lot of information about each of 17000 patients- I am not storing multiple visits, or a history of anything- only a patient's information, which includes current medical state as of last visit is important. Normalization won't save space because it is okay for the excess data to be overwritten- i.e. a patient's last diagnosis is overwritten with the new one. It really is just current info about a single patient, single visit, no charges, no invoices, but there are 500 aspects of that patient's record to be recorded. Should I be using a different program? I need to have dropdowns for long lists of medical codes, and the ability to view different aspects of a patient's status at a time and print those, and only a small amount of coding is required (like for displaying age from birthday). Is Access even the correct program or not? It seems like if a table could be larger it would be perfect, but it also seems like it is not designed to be used in this way.

[June7]

You are describing a giant spreadsheet ('flat file'). Excel 2007 and up allow 16,384 columns. There is capability for 'lookups' on an Excel spreadsheet. Age can be calculated from birthdate by formula in cell.