Medical Database Design for Research

[JJ22]

Hi all! Completely new to database design so I tried to be as descriptive as possible. I started with humble beginnings in Excel and now that I have more variable columns than I can handle, I need a relational database like Access. I am a graduate student studying nutrition, to be specific, obesity.



I designed a data extraction from medical record systems to collect the data for a chart review project and the raw data as come back.

I have five separate excel spread sheets.

1) Patient list: With columns including patient name, their medical #, and date of birth.
My thoughts: I will have to de-identify these patients so my plan is to make a unique Pat_ID for each. Ex: Jake = 1, Jason = 2 etc and remove their name, medical number and keep their DOB only to their birth year. This will serve as a primary key and a foreign key for a lot of tables.

2) Patient PER visit data. Each patient has the following recorded PER VISIT. Thus, 3 visits to the clinic = 3 rows with columns including their name, unique medical #, year of birth, gender, ethnic group, employment, insurance brand, insurance plan, marriage status, date of visit to the clinic, who the provider was (name of doctor, nurse, dietitian), an unique encounter ID for each patient per visit (I'll call it Pat_ID_Visit), blood pressure systolic, blood pressure diastolic, height, weight, and BMI.
My thoughts: I was thinking on making the unique patient ID for each visit the primary key here, and using the Pat_ID as the foreign key, a one to many relationship.

3) Patient problem list. This records their diseases or conditions they have at the time it was noted. The columns include: patient name, medical #, year of birth, problem name, problem ICD9 code (this code is unique to the problem it codes. (Ex: 278.00 = obesity), problem status (Active / resolved), problem noted date, and problem resolved date (if problem status was resolved).
My thoughts: I am unsure how to resolve this many to many relationship. I am thinking is use the Pat_ID as a foreign key here and then make a unique ID (maybe the ICD9 code?) to link them to each patient.

4) Patient Medication List: Each patient had all of their medications extracted between a time period. Thus patients can have multiple prescriptions. The spreadsheet includes the columns: patient name, unique medical #, year of birth, prescription name with size of dosage unfortunately in the same column, Rx order date, Rx start date (if listed) and Rx end date (if listed).
My thoughts: Similar to the problem list, I will have to make a unique ID per medication and make that the primary key of this table.

5) Patient Laboratory Test Results: Gigantic spreadsheet, over 250,000 rows of results. Each patient had their lab results extracted, as such patients have extensive rows of results based on what type of panel they had done. Columns include: Patient name, unique medical #, year of birth, a unique Procedure_ID for the lab test, order date, description of the test (Ex: Comprehensive Metabolic panel), procedure code (I believe its the same as order ID), Component_ID (this links to the Procedure_ID, Ex:a Comprehensive Met. Panel = Procedure_ID of 69083609. This Procedure_ID is made up for various Component_IDs for different parts of the procedure), a result date of the procedure, and the value of the component ID.
My thoughts: Separate this into multiple tables, one for the procedure and one for the components of the procedure. This is where I am really confused however.

In general, I want this database to be able to query certain populations. Ex: I want to be able to pull patients with a BMI > 30, with diabetes, and a HDL cholesterol value below 50.

At the moment I believe I will need tables for: Patients, Providers, Visit Data, Problems, Medications, Procedures and Components.

I can provide small samples of the excel data, deidentified of course to make it easier to process on what is going.

I hope to hear some thoughts. Until then, I'm going to play with what I can!

Thanks!

[jzwp11]

Welcome to the forum!

The patient table should contain those things about the patient that will not change

tblPatient
-pkPatientID primary key, autonumber
-MedicalNumber
-BirthYear
-Gender
-fkEthnicGroupID foreign key to tblEthnicGroups (I assume that you are only recording 1 ethnic group for a person; if you plan on recording more than one then the structure would be different)
-MaritalStatus (technically speaking marital status can change over time. If you want to track those changes, they you would need to do that in a separate but related table)

Since a patient can have many visits, that is a one-to-many relationship.

tblPatientVisits
-pkPatientVisitID primary key, autonumber
-fkPatientID foreign key to tblPatient
-dteVisit

We need a table to hold the various tests that can be conducted (blood pressure systolic, blood pressure diastolic, height, weight, BMI and the lab tests you mention etc.) Each of these would be a record in the following table

tblTests
-pkTestID primary key, autonumber
-txtTestName

Now since many test are conducted during each visit and a the same test can occur at many visits we have a many-to-many relationship which requires the following junction table. I would be

tblVisitTests
-pkVisitTestID primary key, autonumber
-fkPatientVisitID foreign key to tblVisits
-fkTestID foreign key to tblTest
-TestResult

In terms of the problems list. You need a table to hold the problem (ICD9) code list you mention

tblCodeList
-pkCodeID primary key autonumber
-ICD9No
-txtProblem

Then since a patient can have many problems, we need another junction table. Now if the codes are associated with a visit then you would reference the visit like I show in tblVisitTests. That table would look like this

tblVisitCodes
-pkVisitCodeID primary key, autonumber
-fkPatientVisitID foreign key to tblVisits
-fkCodeID foreign key to tblCodeList


Now if the codes do not need to be associated with a visit, but rather associated directly with the patient, the table would look like this:

tblPatientCodes
-pkPatientCodeID primary key, autonumber
-fkPatientID foreign key to tblPatients
-fkCodeID foreign key to tblCodeList

You will have to make the determination as to which of the two approaches above is appropriate for your application.


You would use a similar approach for the medications.

A table to hold all possible medications

tblMedications
-pkMedID primary key, autonumber
-txtMedName

And a junction table to associate meds with patients

tblPatientMedications
-pkPatientMedID primary key, autonumber
-fkPatientID foreign key to tblPatients
-fkMedID foreign key to tblMedications


Since a patient's insurance info may change over time that describes a one-to-many relationship

tblPatientInsurance
-pkPatientInsuranceID primary key, autonumber
-fkInsuranceProviderID foreign key to tblInsuranceProvider
-fkPatientID foreign key to tblPatient
-dteEffective
etc.

with columns including their name, unique medical #, year of birth, gender, ethnic group, employment, insurance brand, insurance plan, marriage status

You would not repeat this information in multiple tables, doing so is a violation of normalization rules. The data should be stored in 1 and only one table.

Just some general recommendations:
1. Do not include spaces or special characters in your table or field names
2. Do not use reserved words or symbols in your table or field names (see list here)
3. Do not use lookup fields in your tables; they are best left for forms. Here is a site that explains the problems table level lookups can cause.

To properly structure databases, it is best to know the rules of normalization. This site describes normalization from a technical perspective.

This site provides some tutorials for someone just starting out with Access and provides examples of how to apply the rules of normalization.

[JJ22]

I have been working through your suggestions and they are clear thus far. Thank you for the help; you went way beyond what I expected!

I'll update if I stumble into any roadblocks.

[orange]

Further to jzwp11's good info:
Are you familiar with Normalization? How about Entity relationship diagramming?
Here is a list of free video tutorials that may help.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

[jzwp11]

You're welcome. Thank you orange for the list of sites.

[JJ22]

I've spent a lot of time watching youtube videos but I haven't come accross many that talk about normalization extensively nor entity relationship diagramming. I will take note of those videos and watch them accordingly. I wanted to get the data off of excel and put it into a tentative database in access. I'm trying to get that framework done before, probably a bad idea however.

That said, this forum has been immensely helpful and I am so happy and grateful to have stumbled upon it.

Since a patient can have many visits, that is a one-to-many relationship.

tblPatientVisits
-pkPatientVisitID primary key, autonumber
-fkPatientID foreign key to tblPatient
-dteVisit

We need a table to hold the various tests that can be conducted (blood pressure systolic, blood pressure diastolic, height, weight, BMI and the lab tests you mention etc.) Each of these would be a record in the following table

tblTests
-pkTestID primary key, autonumber
-txtTestName

Now since many test are conducted during each visit and a the same test can occur at many visits we have a many-to-many relationship which requires the following junction table. I would be

tblVisitTests
-pkVisitTestID primary key, autonumber
-fkPatientVisitID foreign key to tblVisits
-fkTestID foreign key to tblTest
-TestResult

This is the area where I am stuck at. I can't figure out how to get the data from excel into access, or I have but I am unsure if how I have it structured is correct.The problem is that in my excel data extraction the data is recorded as such.

Name	Date of Visit	BPSYS	BPDiastolic	Weight
Joe Abbot	4/27/2011	120	80	165
Joe Abbot	8/21/2011	130	85	170
Joe Abbot	12/12/2011	140	90	175

Here is my access information:
Bold are tables, italics are field names.
I have three tables:
1) PatientVisits: The PatientVisitID is the pk, Visit date, PatientID as the fk to the patients table.
2) Tests: BPSys, BPDiastolic,Height, Weight, BMI
3) VisitTests: VisitTestID is the pk (autonumbered), patientVisitID is a fk to the patientvisits table, testID which is the fk to tests table, and test result.

Here is my PatientVisits table example

PatientVisitID (autonumber,pk)	VisitDate	PatientID (fk)
4	4/27/2011	1 (fk to patientID in patient table where Joe = 1)
400	8/21/2011	1
865	12/12/2011	1

Joe is patientID = 1 (I alphabetically added patients) and has a PatientVisitID of 4, 400, and 865. I imported excel data with the visit date sorted by oldest thus it generated these autonumbers. He is thus the 4th, 400th and 865th visit to the clinic.

Tests Table:

TestID	TestName
1	BPSys
2	BPDiastolic
3	Weight
4	Height
5	BMI

My Visittests table.

VisitTestID	PatientVisitID	TestID	TestResult
1	4	1	120
2	4	1	130
3	4	1	140
4	400	2	80
5	400	2	85
6	400	2	90
7	865	3	165
8	865	3	170
9	865	3	175

Obviously it would look different with more patients added in. I just want to be sure this is the correct way to do this.

If this is indeed the correct way, any suggestions on the quickest way to get the data from excel into access tables? For this test data, I've assigned all 2500 visit encounters a unique id by creating a patientvisitID column in the excel data after sorting by date. I then assigned the oldest record as 1 and the newest as 2500. I then copied this patientvisitID excel column into the PatientVisitsID for the Visittests table and then copied the descending excel dates to create the same patientvisitID values with autonumber on the patientvisits table.

If any more information is needed, I'd gladly try to clarify. I looked at the relationships using the above format and it looks correct.

[jzwp11]

I'm trying to get that framework done before, probably a bad idea however.

Setting up the normalized structure provides the foundation for everything in a relational database, so it should/must be done first.

Now, did you import all of your Excel data into 1 table? Access has a 255 field (column) limit, so if your Excel file exceeds that, you will have to break up the data.

I will assume that you have it in a table, so the task at hand is to get the data into the normalized structure. You will need a series of append queries to get the data from the imported table into the normalized tables.

The first would be the patients since you need the primary key value that Access generates in order to populate the visits table.

So you will need to create a new query.


I will assume that your data is structured as follows:

Name	Date of Visit	BPSYS	BPDiastolic	Weight
Joe Abbot	4/27/2011	120	80	165
Joe Abbot	8/21/2011	130	85	170
Joe Abbot	12/12/2011	140	90	175

The following lays out the process I would typically use, but I see you have populated some of the tables already. So if you only need to add the test data, you could scroll down towards the end of this long-winded explanation

So we need to create the records in the patient table. So create a SELECT query and include the table that holds the Excel data. Add the fields to the lower pane that has just the information about the patient such as their name, address, date of birth etc. but no visit or test information. Do you have some sort of identification for a patient that uniquely identifies them? If so, make sure to include that as well.

Now, run the SELECT query you just created (a SELECT query only displays records, it does not alter records so it will not impact any data). You will see that a patient's information is repeated many times (because they have had many visits), but you only want 1 record per patient appended to the patient table, so we will have to modify the query. Switch from the design grid view to SQL view and add the word DISTINCT after the word SELECT. So you should have the following:

SELECT DISTINCT patientname, address etc.
FROM importeddatatablename (whatever it is)

BTW, the word "name" is a reserved word in Access, so it should not be used as a field or table name, so your patient table should have something like txtPatientName or just PatientName. Are you planning on separating first and last name into separate fields? If so, that should be done now as well. Assuming that the name is the first name followed by a space and then the last name for all records, you will need some text manipulation functions to separate them. You will need to create two new fields in the query like this:

SELECT left(txtpatientname, instr(txtpatientname," ")-1) as txtFName, right(txtpatientname, instrRev(txtpatientname," ")+1) as txtLName
FROM exceldata;

I nested an instr() function within the left() function in order to get the first name. The instr() function has a general form: instr(string1, string2). The instr() function returns the starting position of string2 within string1 (so it returns a position number). So in this case it looks for the space between the first and last names in your name field. So in the case of Joe Abbot, the space is the fourth character, so the instr() function returns 4. Now the left() function pulls the characters from the left part of the string. If you do not specify it, you start at the first position and then you tell the function how many characters from there you want pulled. I use the instr() function to tell the left function to go to the space (so the 4 left most characters), but really I do not want to include the space with the first name, so I subtract 1 from what the instr() function returns, so for Joe Abbot, the length we need is 3. For the last name I used the instrRev() function which works the same as the instr() function except that it approach the string from the right end and works it's way in from there to find the space. I use the Right() function in this case. When the instrrev() function returns the position of the space I add 1 to it to get to the position of the first letter of the last name.


Now go back to design grid view and run the query & you should see how the results have changed. If everything looks good, we can proceed with the append query. To do that just change the query type from SELECT to APPEND. Access will prompt you to select the destination table (i.e. the patient table). You will need to map the fields of the excel data table to the corresponding fields in the patient table in the lower grid. Access will handle the autonumber field automatically so it should not appear in the append query at all. Now just run the append query & verify that the records have been appended.

Now for the visits table, you will need another query. Again, start with a SELECT query. Add your excel data table and your patient table. In the upper pane, make a join between the two tables. You will need to join the two via a field that is unique to the person. I would probably join via the unique patient ID number that you have assigned to the patient if you have one. Joining by the name may be problematic if you have two patients with the same name. If you are forced to join by the name & you have separated into first and last names, then you would make joins for both of those fields.

Now select the pkPatientID field, and the visit date fields (and any other fields that pertain to the visit; again not the tests). Run the query to verify the data. If all is OK change the query type from SELECT to APPEND and map the fields appropriately. The pkPatientID should be mapped to fkPatientID in the visit table.

The next step would be the tests themselves. Each test will needs its own append query. (You could do it in code but that might take longer than actually doing the queries yourself).

You will need to create the table of tests first in order to know the primary key value for each test (it looks like you have that already).

You would use a similar process. Start with a select query, in this case you would need your imported data table and the visit table (need the pk for the visit) and possibly the patient table in order to link the test results to the correct patient/visit combination. You would make joins between the various tables. When you select the fields, you will need the pk of the visit to map to the fkVisitID field in the visittest table. Now for the actual test, you would select 1 test say BPSYS field and map that to the testresult field. To populate the TESTID field you would put in a constant value corresponding the primary key value of the BPSYS test name (1). The SELECT query would look something like this

SELECT pkVisistID, BPSYS, 1
FROM...

You would then change from SELECT to APPEND and run

You could then alter the query above by changing BPSYS to BPDiastolic and 1 to 2 & run the append query

SELECT pkVisistID, BPDiastolic, 2
FROM ....

And you would continue altering & running for each successive test.

I would recommend making a copy of the database after each append (unless you are very confident in your query construction capabilities) so if something goes awry, you do not have to start from scratch.

[JJ22]

Now for the visits table, you will need another query. Again, start with a SELECT query. Add your excel data table and your patient table. In the upper pane, make a join between the two tables. You will need to join the two via a field that is unique to the person. I would probably join via the unique patient ID number that you have assigned to the patient if you have one. Joining by the name may be problematic if you have two patients with the same name. If you are forced to join by the name & you have separated into first and last names, then you would make joins for both of those fields.

Now select the pkPatientID field, and the visit date fields (and any other fields that pertain to the visit; again not the tests). Run the query to verify the data. If all is OK change the query type from SELECT to APPEND and map the fields appropriately. The pkPatientID should be mapped to fkPatientID in the visit table.

The next step would be the tests themselves. Each test will needs its own append query. (You could do it in code but that might take longer than actually doing the queries yourself).

You will need to create the table of tests first in order to know the primary key value for each test (it looks like you have that already).

You would use a similar process. Start with a select query, in this case you would need your imported data table and the visit table (need the pk for the visit) and possibly the patient table in order to link the test results to the correct patient/visit combination. You would make joins between the various tables. When you select the fields, you will need the pk of the visit to map to the fkVisitID field in the visittest table. Now for the actual test, you would select 1 test say BPSYS field and map that to the testresult field. To populate the TESTID field you would put in a constant value corresponding the primary key value of the BPSYS test name (1). The SELECT query would look something like this

SELECT pkVisistID, BPSYS, 1
FROM...

You would then change from SELECT to APPEND and run

You could then alter the query above by changing BPSYS to BPDiastolic and 1 to 2 & run the append query

SELECT pkVisistID, BPDiastolic, 2
FROM ....

Well I've spent most of the day trying to figure out these steps. I've decided to take pictures of what I have because I can't figure out how to get the query to show results to append to the test result field. I have attached what the final queries I made that was required to create the test result fields. The black boxes I put on the picture are what I created today, other tables are from previous method's work. Vitals are just another name for the basic BP, height and weight tests. Thus, Vitals instead of Tests.

I was intrigued by the way you outlined because I previously was copying and pasting from excel column by column. Using that process, I came up with the relationships found in picture two, "old relationships." I was content doing it this way because it was going great and I felt like I knew what I was doing.

However, the method you outlined will be beneficial to know when my database grows with a second data extraction that is planned in a few weeks. So if I can learn how to take a large excel spreadsheet, break it down, and take that data and make it into a useable database, I'd do that.

Pretty lost at this moment but I would be content knowing if either method was on the right track.

In the mean time I am going to go through both that wiki on database normalization and access basics by Crystal.

[jzwp11]

To append the test/vital results you only need 3 tables: raw data, App_PatientList and App_PatientVisits in the upper pane of the design grid.

Then select the PatientVisitID field from App_PatientVisits, and whatever vital field you want from Raw Data. The last piece of data to add is a constant in the third field. That constant would correspond to the value of the primary key in the test table that corresponds with the vital field you are selecting from the Raw Data table.

Now switch the SELECT query to an APPEND query. You will be appending to the App_VisitVitals table. The PatientVisitID field you selected will map to the similarly named field in App_VisitVitals. The vital field will map to the Test Result field of App_VisitVitals and the constant field will map to VitalID.

[JJ22]

To append the test/vital results you only need 3 tables: raw data, App_PatientList and App_PatientVisits in the upper pane of the design grid.

Then select the PatientVisitID field from App_PatientVisits, and whatever vital field you want from Raw Data. The last piece of data to add is a constant in the third field. That constant would correspond to the value of the primary key in the test table that corresponds with the vital field you are selecting from the Raw Data table.

Now switch the SELECT query to an APPEND query. You will be appending to the App_VisitVitals table. The PatientVisitID field you selected will map to the similarly named field in App_VisitVitals. The vital field will map to the Test Result field of App_VisitVitals and the constant field will map to VitalID.

Just wanted to update you that your suggestion is working as described.

EDIT: Quick Question! Why is it when I look under the patientlist subfields for the visits I see multiple PatientVisitID and test results. I have added BPSys, BpDIa and Height thus far.

Example: I Expand the record patientID 85 and then it shows the five visits this patient has with patientVisitIDs of 425, 729, 1380, 2021 and 2453.
I then expand patientVisitID 425 (date 6/13/2011) and then see VisitVitalID 2943, 2944 and 2947 with BPsys results 112,108,111 from different dates (12/12/2011, 4/9/2012,6/25/2012) respectively. Ironically, the date 6/13/2011 in excel has no recorded BPSys.



I then expand patientvisitID 729 (8/9/2011) and the results are repeated, but with different visit IDs.


Why are the results being duplicated into visits on which they didn't occur?

[jzwp11]

I would like to know why having the additional tables in the original query I made would not allow any data to be generated?

If you have a join between two (or more) tables and there are no matching records in one of those tables then the query will not return any results. If there are some matching records only those will be returned. This is because of the join type that Access sets up as a default (INNER JOIN). You can edit the join type when you create a query, just make the join line and the double click the line. There are 3 types that you can choose from Type 1 is the INNER JOIN.

[JJ22]

If you have a join between two (or more) tables and there are no matching records in one of those tables then the query will not return any results. If there are some matching records only those will be returned. This is because of the join type that Access sets up as a default (INNER JOIN). You can edit the join type when you create a query, just make the join line and the double click the line. There are 3 types that you can choose from Type 1 is the INNER JOIN.

I just read about this in Crystal's access information. Good example.

[JJ22]

In regards to the Problems with the ICD9 codes and using them in the database. I got a few questions. The original Raw data set checked each visit to any clinic for the presence of a problem without dating the check. Thus if the patient had the same problem at multiple visits, the record is a duplicate. I've removed all the duplicates so distinct records remain. Here is how the problem list looks like in excel:

FullName	Medical Record #	Date of Birth	Problem Name	ICD9No	ProblemStatus	ProblemNotedDate	ProblemResolvedDate
Joe Clark	2	1/1/2011	Diabetes II	250.6	Active	7/28/2011
Joe Clark	2	1/1/2011	Diabetes II	250.6	Resolved	7/28/2011	10/28/2011
Joe Clark	2	1/1/2011	Hypothyroidism	244.9	Active	8/1/2011
Joe Clark	2	1/1/2011	Diabetes II, uncontrolled	250.0	Active	8/28/2011
Joe Clark	2	1/1/2011	Diabetic Nephropathy	250.4	Active	7/28/2011	10/28/2011

Goal: I think I want a lot of the ICD9 codes to be consolidated for simply a Yes / No presence of a general disease. For instance, there are various codes for Type II diabetes: 250.0, 250.02, 250.60 coding for general type II diabetes, whether it is controlled, or uncontrolled respectively. It depends whether the clinic chose to use the more descriptive code versus the 250.0 catchall. In addition, things like diabetic nephropathy has its own code 250.40. While it's great to have the detail, we want just the presence of the disease, with the onsetdate and resolved date and knowing which codes were consolidated into the disease.

I see two options:
1) Sort extraneous problems out of the raw data sheet first: Sort through the excel data, filter the codes that associate with the morbidities I want, build a patient sheet per morbidity (ex:type II diabetics), and then import that into access. Then give that table a pkDiabeticID, autonumber, with a fkPatientID as a foreign key to tblPatientList.

OR

2) Import the raw data set with all the problems then in access build the following:

tblPatientProblem (Each patient can have multiple problems and each problem can be had by multiple patients... thus this table is needed as a junction.
-pkPatientProblemID, autonumber
-fkPatientID, foreign key to the Patient List Table
-fkProblemID, foreign key to tblProblemist

tblProblemList (Each problem is made up of multiple ICD9 codes but each ICD9 code is matched for only one consolidated morbidity.) Ex: Diabetes II is problemID of 1
-pkProblemID, autonumber.
-fkMorbidityName

tblDiabetesCodes (I would assign the ProblemID 1 to each ICD9No that I want consolidated into the problem of diabetes II.)
-fkProblemID,
-ICD9No
-txtdescription

tblDistinctProblemList: the imported data from excel
-Name
-MRN
-DOB
-ICD9No
-ProblemStatus
-ProblemNotedDte
-ProblemResolvedDte

Basically I would need a separate tables for each type of consolidated problem.

---
I would like to know which method would likely be easier to do, and if I go with the latter, does it look correct or will I get my database too “messy?”

---
Update: I had the previous stuff written out days ago, but decided to try option 2. I can't design a query or the correct relationships to do what I want. At this time, I think the best route may be option one but only removing the problems I don't need. I believe we want the resolution we could have if the problems were related to codes (instead of just simply saying X has diabetes, we could also figure out if X has diabetes). Opinions?

[orange]

I'm just reading this thread and wonder if you have a table of the ICD9 codes? Do you have info about the code's basic structure?
Seems code 250.x relates to Diabetes, but not necessarily DiabetesII.

Do you have a list of the types of questions you want to pose and get answers from whatever structure evolves?
You need to model against what you want, in my view, at this time.
Populate the structures based on the data you have as a prototype.
If you don't have the data, and can't get certain data, then you'll have to adjust the structure accordingly.
There may be some questions that can't be answered, but some general prototyping should highlight those, provided you have a good understanding of the data you have, and the questions you want answered.

Again, just some thoughts, after reading the thread quickly.

If you create any databases for testing/prototyping etc, can you supply mdb format? I'm willing to help, but have acc2003 and can not use accdb.

[JJ22]

I'm just reading this thread and wonder if you have a table of the ICD9 codes? Do you have info about the code's basic structure?
Seems code 250.x relates to Diabetes, but not necessarily DiabetesII.
Basicaly ICD9 codes work under a branch structure. 250.XX relates to diabetes. 250.00 is type II diabetes where as 250.01 is type I diabetes. The structure for all of Diabetes is listed here for example: http://www.icd9data.com/2012/Volume1...50/default.htm

Do you have a list of the types of questions you want to pose and get answers from whatever structure evolves?
Some. For example, based on intake questionnaires some patients are put on different weight loss tracks with different procedures. An example question: Do patients who have Type II diabetes fare better on the track that addresses Psychological Components or dietary components. Which track produces the best result for diabetics? Of the diabetics, did the presence of Byetta or X type of insulin have a more favorable result.

You need to model against what you want, in my view, at this time. Populate the structures based on the data you have as a prototype.
This is the problem I am having at the moment. I can't figure out how to build correct database structure. The structure jzwp11 proposed I understand and works as a prototype. I collected this data retrospectively, so I have some idea of the questions Iwant answered, but going through the data and analyzing it will help generate more ideas. It is why I am trying to build the database to query some very basic things. Another data extraction with the other half of the dataset is still coming, so I want to have the process of moving data from excel to access be familiar.


If you don't have the data, and can't get certain data, then you'll have to adjust the structure accordingly.
Definitely, I have come to terms with that.

There may be some questions that can't be answered, but some general prototyping should highlight those, provided you have a good understanding of the data you have, and the questions you want answered.

Again, just some thoughts, after reading the thread quickly.

Overall I definitely appreciate the thoughts. See bold for some of my comments.

I guess a fundament question I've skipped is that given the data set is still going to grow--supposedly it should be in a excel similar format--should I be even designing an access Database at the moment?