Access rookie need help with table design for complex data already entered into Excel

[dlutter]

Hi, I am a complete rookie with Access. I am using Windows 7 64 and Access 2010. Before I even knew what Access was, I gathered and entered data in table format within a multiworksheet Excel spreadsheet. As you can imagine, the data is now way too cumbersome to work with. I have been reading tutorials and am working to import my data for Signalment, History/Exam, Treatment, and Followup into a separate table for each. I am very confused about what to do with the rest of my data.



If it helps, the project is looking at injury to a tendon in over 100 horses. I have the horses sorted by medical record number and have recorded the type (C,D, or S) and severity (1, 2, 3) of the injury within either the medial or lateral lobe of the tendon within each leg (L or R) at different levels of the leg (Pastern, PTNB, at NB, & DTNB). Additionally, I have divided the horses into 5 groups and subdivided each group into mild, moderate or severe (separate criteria from the 1,2,3 severity). I have pasted the "Severe" subgroup from one worksheet below. I can also provide the entire group 5 worksheet as an attachment if it would help. Group 5 is the smallest group.

I am struggling with how to divide up this data in order to make it work in Access. Do I need to have a separate table for each group, subgroup, limb, lobe, and location within the limb? (ex. Table 1: Group 5, Severe, L, pastern, medial lobe) Within each table I would then list each horse, the type of injury, and the severity of the injury. That gives me 18 separate tables for group 5 Severe; 54 tables for all of group 5; and 270 tables for all groups. Plus I still need to enter tables for Signalment, History/Exam, Treatment, and Followup.

Is there a better, more simple way to do this? Perhaps with tables for each group (approx 10 tables for all the data)? My plan is to enter what I can for Signalment, History/Exam, Treatment, and Followup and then come back to the rest. Any input would be appreciated, even if it is just to confirm that I will need over 270 tables for my data. Thanks.

Group 5 Severe	92283		102041		120588
Limb	L	R	L	R	L	R
Pastern (type)
lateral lobe		C2
medial lobe	C1	C2		C2	C3
PTNB (type)
lateral lobe		D2
medial lobe	D1	D2		C3	S2
at NB (type)
lateral lobe					S1
medial lobe				C2	S1
DTNB (type)
lateral lobe
medial lobe	C1	C3		C3

[jameslarsen]

You definitely don't need 270 tables to accomplish this. If I understand what you are trying to accomplish, the basic structure you want to strive for is actually pretty simple and would be as follows:

- "Horse" table to contain identifying information about each animal, such as medical record number, group number, etc.
- "Injury" table to contain information about each injury, including attributes like location of injury, severity, etc.
- Additional tables as needed for history, treatment, followup, etc.

One of the complications you are going to experience with this design is that you already have the data entered into Excel, so there could be a difference between what would be an "ideal" model for tracking this data, and what is the best method that conforms to the way you already have the data (unless you want to do some significant data translation, which if you aren't experienced with, might not be the best approach). For example, if you have the record numbers running across the top of the excel sheet in merged cells, and "L" and "R" under each one, you will find that will be very difficult to import. If you would feel comfortable posting your Excel file, I could take a look at it and see how difficult it would be to import. I have significant experience translating poorly-formed data sources into clean structures.

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[dlutter]

Thank you so much for your willingness to help. I would be more than willing to post my file/files, just worried about overstaying my welcome with a bunch of poor data. I could provide the entire Excel files, if that would be best. In the injury file I have one worksheet per group (1-5) with the data entered in the same format for each sheet. I also have a file with horse info(one worksheet) and a file with followup findings (6 worksheets; one with all the cases and the other 5 are repeats of the data divided into groups). What would be best? For now I will post a small sample of each so you can see how I have my data structured. Some of the followup data is incomplete as I have not been able to obtain the info for all horses.

Thank you again.

[dlutter]

I forgot to mention my end goal with all of this. I would like to be able to relate any info about the injury(type, severity, group, multiple locations, multiple legs, etc) to the horse's return to use. I also am running into trouble identifying primary keys for each table I try to make. I keep wanting to use the medical record number of the horse as the primary key for each table. Should I be using the auto number feature?

[RayMilhon]

My suggestion is read up and understand Data Normalization prior to building your data structure. Adhering to 3rd Normal form (Minimum) will benefit you greatly. Secondly yes the auto number feature will help when creating Primary Keys. If the tables are designed to 3rd Normal form your reporting will be much easier. It will take a bit more effort on your part once you have the tables designed to upload your spreadsheets and put the data in the correct place but that's a one time impact and future data entry will be much easier.

[jameslarsen]

I took a quick look at your data files, and while the horse and followup sheets aren't bad, the injury sheet will be nearly impossible to import directly into Access, due to the layout and the use of merged cells. What I usually do in a circumstance like this is create an Excel load file with the structure that I want (one sheet per final table in Access and all the columns across the top) and populate it with data however I can (sometimes copy/paste, sometimes formulas, etc). Unfortunately, I don't know enough about the injury data to make a guess at all the columns you would need, but I think the structure I outlined in my first post would still hold true. I would create a Horse table, to contain identifying information about the horse that does not change (so no treatment or injury information, or anything else that would be considered an "event"), an Injury table, containing all information about the injury, such as location, severity, group, etc., and a treatment table to show a log of what was done to a given horse at a given time (you could even link it to an injury if it was applicable). You may also need to keep a Current_Status or Diagnosis table for the horse to store information about it's current lameness or condition, etc. (or you could create a series of "latest_" attributes in the horse table, and just keep updating the values). The guiding principle here is that you want separate tables for entities (such as horses), and tables for anything that could be considered an event or state (like an injury, treatment, etc.). You would then link entities to events using their unique identifiers. When you are building your tables, just look at each single piece of data, and try to decide what table it would best fit in (or if you need to start a new table to contain it).

I have attached a sample load sheet with a few columns I'd suspect you would need.

Load File.zip

I think it will be a large amount of work to manually translate the Excel data into a more normalized structure that an Access database can work with, but it will pay off in the long run for any new data you have to add. Also make sure to standardize the information you will be getting in the future (either a new Excel sheet that follows a more importable format, or a form in the Access database). There are a few tricks you can use to make populating the data a little easier, like to do all the most severe entries first, so that you can just paste the word "severe" over a large block of entries. I hope this solution works for you, and starts you on your way to a cleaner, streamlined database.

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[dlutter]

Thank you for taking a look, James. Your suggestions have helped me already. Thank you for your input, Ray. I will work on things some more and learn about normalization before getting too deep into things. Thanks again, I am sure I will be back.