For the Kids

[Rob Marr]

Hi,

I am building an Access database for children's mental health services.

Essentially, there will be one record for each child, comprising of some information about them (age, etc) and the results of several questionnaires which determine how their treatment is going. These questionnaire are typically completed at entry to the system and at 6 months, with possibility of annual reviews for long cases.

There are perhaps 10 different questionnaires, not all of which will be completed for every child. We will need to record the answers to the relevant questionnaires for each child.

My question is this: should I hold all the information in one table or create a number of different, linked tables, one for each questionnaire? The end goal is a single line of data for each child combining all the recorded information. And if I do create linked tables, what is the exact link needed?

A further complication is that I will be populating this database from a number of different service's IT systems. So, I'm likely to be getting .csv files which will have some but not all of the fields completed and which I will need to integrate into my database.

Any advice gratefully received!

Thanks

Rob

 

[namliam]

Just "normalize" your database, if you dont know what that is you need to go find out...

 

[WIS]

Hi,

I am building an Access database for children's mental health services.

Essentially, there will be one record for each child, comprising of some information about them (age, etc) and the results of several questionnaires which determine how their treatment is going. These questionnaire are typically completed at entry to the system and at 6 months, with possibility of annual reviews for long cases.

There are perhaps 10 different questionnaires, not all of which will be completed for every child. We will need to record the answers to the relevant questionnaires for each child.

My question is this: should I hold all the information in one table or create a number of different, linked tables, one for each questionnaire? The end goal is a single line of data for each child combining all the recorded information. And if I do create linked tables, what is the exact link needed?

A further complication is that I will be populating this database from a number of different service's IT systems. So, I'm likely to be getting .csv files which will have some but not all of the fields completed and which I will need to integrate into my database.

Any advice gratefully received!

Thanks

Rob

I would have a master tbl for the Child, with ChildID, Autonumber, PK.

As each questionnaire can be filled out more than once per child then you will need 10 questionnaire tbls, each of them with ChildID, Long, FK and a fld "DateEntered" or similar name for each of the 10 questionnaires. All will be linked via the ChildID (1 : many).

Build a form holding the master child info with 10 questionnaire subforms.

WRT the .csv files, do they have any consistent unique identifier apart from the Child's name.

 

[Rob Marr]

Hi, thanks, that's useful.
yes, each child will have a unique (but anonymous) ID for each episode of care, which is what we'll use to link children and questionnaire info.

 

[neileg]

I would have a master tbl for the Child, with ChildID, Autonumber, PK.

As each questionnaire can be filled out more than once per child then you will need 10 questionnaire tbls, each of them with ChildID, Long, FK and a fld "DateEntered" or similar name for each of the 10 questionnaires. All will be linked via the ChildID (1 : many).

Build a form holding the master child info with 10 questionnaire subforms.

WRT the .csv files, do they have any consistent unique identifier apart from the Child's name.

I would avoid having ten tables if at all possible. One table with a field to identify the questionnaire would be preferable.

 

[DCrake]

Neil

Alot will depend on the structure of each questionairre.

 

[neileg]

Neil

Alot will depend on the structure of each questionairre.

I agree, David. That's why I said 'if at all possible' and 'preferable'.

 

[Rob Marr]

Gents,
Thanks for your input.
The quesionnaires all differ in structure and the number of responses. But, essentially, each one generates a number of scores in several categories. Questionnaires are also likely to be added as time goes on. My original thining was in line with what is suggested here, with a separate table for each questionnaire, linked by child ID. Multiple responses (over time) complicate matters slightly but I think I will either: 1) structure each questionnaire table to allow two iterations of the questionnaire to be recorded, or 2) have a date field which allow multiple completed questionnaires under the same child ID.

I'm not sure how I would keep it a to a single questionnaire table? I suppose I could have a huge table with all possible responses to all possible questionnaires but I think this would be difficult to manage and update.

 

[DCrake]

Keeping your questionairres in different tables seems to me to be the only logical way to go. It may not sound normalised but at the end of the day you will be analysing them in the singular you will not be comparing one against the other. Where more than one completion per person is neccessary then using the data to identify the occurance or use a counter to record the entry number would suffice.

One thing I would do though is to place each questionnaire into a seperate mdb, don't know exactly why but it feels as though this would make the data more secure should anything happen to the back end.

David