Hi,
I have used access databases but I have never designed one and I am considering creating on for my organization.
I work at a social work non-profit where clients pass through 4 phases of a case management process = triage and 3 case management steps. We do not have a record management system (paper files). However, during each phase a worker inputs data related to a client on their caseload using Survey Monkey at a particular point in time (quarterly or biannually). What I end up with are 4 tables with client data. This data has traditionally been analyzed in a non-relational way and has provided numbers for funding reports and descriptive communication data. We don't look at client flow from each phase or changes in client outcomes from point A to B. This can be done through analysis is excel, but it is formula heavy and not a systematic process.
I want to create a process where I import all of the cross sectional data from survey monkey into an access database on an ongoing basis. From access I would create a client table (with unique client ID and demographic info) in addition to the 4 tables. This client table would be linked to the 4 phase tables. The goal is to create a client-centered data system to assess things like client flow and outcomes. Client flow being movement from one phase to another and the time it took. Outcomes being health and social work related measures.
However, during test runs I am already struggling with creating this client table (clients visit the program at numerous times and they may not provide the same demographic information at each time point, so I must decide what particular variables make a client unique).
What I want to know is am I going about this right way? Do you have any suggestions about using cross-sectional repeat data to create a database?
Thanks!