Right, this is a challenge, so bear with me...
I am collecting data from 6 different sites providing mental health services to children. The purpose is to compile a comprehensive central dataset with a single line of data holding every possible piece of information for each patient, with null values where appropriate. This is for submission to a central body.
Each service has a copy of an Access database I've built. This has a table for patient information and further tables for questionnaires relating to those patients' care. The database is normalised. Although each table is relatively small, there are approx 1000 possible pieces of information on each patient. Since the query limit is 255 columns I've written 4 queries which pulls the data together.
Each month I need to add new records to my central table (easy using an append query, with date of export as the filtering criteria). But, and this is the tricky bit, I also need to update existing data, which will have changed or have additions. With 1000 bits of info this has to be automated. Does anyone have any suggestions on how to do this please?
Thank you
Rob
I am collecting data from 6 different sites providing mental health services to children. The purpose is to compile a comprehensive central dataset with a single line of data holding every possible piece of information for each patient, with null values where appropriate. This is for submission to a central body.
Each service has a copy of an Access database I've built. This has a table for patient information and further tables for questionnaires relating to those patients' care. The database is normalised. Although each table is relatively small, there are approx 1000 possible pieces of information on each patient. Since the query limit is 255 columns I've written 4 queries which pulls the data together.
Each month I need to add new records to my central table (easy using an append query, with date of export as the filtering criteria). But, and this is the tricky bit, I also need to update existing data, which will have changed or have additions. With 1000 bits of info this has to be automated. Does anyone have any suggestions on how to do this please?
Thank you
Rob
