Hey all!
I've created a rather complex database with numerous tables to capture some basic medical information like blood pressure, medications taken, height and weight, etc. each with it's own table (see example below). I have mostly one primary key (ID#) that links the data to a main table that I will want to export. The reason for creating several tables is that many of the patients could have numerous entries for the same type of data. For example, I want to capture Mr. Smith's blood pressure (BP) values each time he came in to the clinic so I created a BP table with the fields Name (actually used ID# in the real database); BP_Date; Sys_BP; and Dia_BP. In my limited database development experience it sounded like the rational thing to do so that each patient could have an essentially limitless amount of dates/entries and Access would keep it nice and tidy for me.
Now here's the problem: All data needs to be exported into excel and will be imported into SPSS statistical software. According to my research lead, the software requires that each patient's info be on one row only. According to sources from my research group when they usually design databases for export to SPSS they "guess" how many entries will be needed for each event and create a field for each one. So if they estimated a max of 5 blood pressure screeenings they would create the fields BP_Date_1; Sys_BP_1; Dia_BP_1; BP_Date_2; Sys_BP_2; Dia_BP_2; and so on until they got to 5. The same would be true for medications, height and weight, etc. They all need to be in the same row as the patient name so that each row represents one patient. This seems kind of ridiculous to me from a database design standpoint (again I'm fairly new) resulting in many null fields while self-limiting the number of entries you can make.
At this point, the database is already created (which took me hundreds of hours) and data collection has already begun, so starting over from scratch is not an option. I'm thinking there has to be a way in Access to transpose the data into single rows to export as desired. Initial thought was some sort of query that counts the entries for each table per patient and makes a new table with new fields based on the counts and transposes them. I just don't have enough knowledge or coding experience to know how to make that happen.
I'd love to hear your thoughts and suggestions. I've also attached below a simplified example spreadsheet of how the data is currently stored in Access and how I'd like to export it.
Thanks!!
Currently stored like this (except using an ID# instead of Name):
Needs to be exported like this: