Followup database

[Stormbreak Youth Program]

Hi, I hope that you all can help me. I am the Program Manager for a teen
shelter. I need help revamping our followup database. The database that we
have been using is not able to do as much as we need it to. What we do is
collect data on a variety of subjects, i.e. living situation, school,
drug/alcohol use, mental health, etc. at intake, 1 month after discharge,
and 6 months after discharge. The database is currently set up in 3
tables - intake, 1 mo, 6 mo. Each table has a corresponding form, query and
report. The tables are pretty much identical to each other - you enter the
resident name, case #, etc. then answer each of the questions, which
predominantly have combo boxes (possible answers yes, no, skip) in the
forms. We then have 3 reports (intake data, 1-mo, 6-mo) which break down
the responses to all the questions for that time period, e.g. #/% enrolled
in school yes, no, skip. But that is all that we can do with it.

I know that the tables are not set up right. I know there should be a table
for residents that has name, case #, intake date, exit date. But then,
should there be intake, 1 month, and 6 month tables, each with the same
questions, or should I break it down by question topic i.e. school,
drug/alcohol, family, etc.? If the latter, how do I then tell the database
to discern between intake, 1 month, and 6 month data?

What I would like my database to do: to be able to select a group of data to
analyze, i.e. intake data on all kids who left the program in 2003, or
compare one youth's intake data to 1-mo and 6-mo, or all 1-mo data to 6-mo
data.

I have been trying to figure this out on my own and by browsing newsgroups,
referring to books, etc. but it's not happening. Can anyone help me?

Thanks,

Kathi Ritchey
Stormbreak

--
This communication, including all attachments, may contain information that
is confidential and exempt from disclosure. Please safeguard accordingly.
If you are not the intended recipient, you are hereby notified that any
dissemination, distribution, or copying is strictly prohibited. If you have
received this message in error, please notify the sender via return email
and delete this message from your computer. Thank you.

 

[John Vinson]

I know that the tables are not set up right. I know there should be a table
for residents that has name, case #, intake date, exit date. But then,
should there be intake, 1 month, and 6 month tables, each with the same
questions, or should I break it down by question topic i.e. school,
drug/alcohol, family, etc.? If the latter, how do I then tell the database
to discern between intake, 1 month, and 6 month data?

Consider a completely different design.

Have two tables in a one to many relationship - Cases and Followups.

The Cases table would have case number as the Primary Key, with
specific info about the case in its own right. This table would be
related to a Followups table with a case number (as a foreign key),
and a FollowupPeriod field. If there are three followups, there'd be
three records in this table, with FollowupPeriod equal to 0, 1, and 6
(say). And if there are multiple questions, you need to consider
storing the questions *as data*, not as fieldnames; consider two
tables:

Questions
QuestionNo
Question Text

Answers
CaseNo
FollowupPeriod
QuestionNo
Answer

If you have 318 questions, there'd be room for 954 answers for each
case in the Answers table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Brett Collings [429338]]

Kathi, you're right, your tables are in need of some TLC.

Firstly, you have to get rid of the duplicated data in the three
tables. As you say, just make one table "tblResident" to record all
their personal details. You can retain the current case number but
change the field to an Autonumber so that they increment, if they
don't already.

To start that process
- Make a BACKUP COPY of the whole MDB file so we have something to go
back to
- make yet another copy of one of the tables and name it "tblResident"
- delete all the Question fields out of it, just leave the
non-repeating information about the resident.

Your table "tblResident" then should be something like
CaseID Autonumber Primary Key
Fname Text
Mname Text Middlname or initial
Lname Text
Addr1 Text Address 1
Addr2 Text Address 2
City Text
DOB Date/Time Date of Birth
.... etc etc ... NO question fields should remain
dteIntake Date/Time Use common Naming prefixes like dte, cbo,
cmd, where you can. You'll need them later
dteExit Date/Time

Now ... as for the questionnaire, to ensure long term analysis of
stats, I would use three tables "tblIntake", "tbl3Month", "tbl6Month"
they would have the same fields and I would structure them to relate
to the CaseID in tblResident. For example
IntakeIDAutonumber Primary Key
Q1 Text or Yes/No Depending on the question. Yes/No
is very good for analysis later when you want
to check against something like ...
InSchool Yes/No Easy then to apply the filter that
includes or excludes those in school
....etc etc ...
CaseID Number "Foreign Key" This is the most important
field as it is the one that links all of the
records in this table back to the Resident

To do this,
- Add a new field to the front of each named "IntakeID", "3MthID" or
"6MthID"
- Make that field both Autonumber and the Primary Key
- delete all the fields that you now have listed in tblResident
- Make sure that CaseID (or its equivalent) is no longer a Primary
Key, it is now a Foreign Key
- I would move CaseID to the end of my field list now (that's how I
quickly find my Foreign Keys)

You now have a Normalized database (I think - doing all this in my
head)


Then to your question about how to relate all of these things back
together
- Create a Main Form (sometimes called the Parent form) "frmResident"
which has all of the Resident's details on it.

- Using the "Tab Control" from the Design Toolbox, add 3 tabs to the
bottom of the Main Form

- Once the 3 period tables are created, create a subform (also called
a Child Form) of each "sfmIntake". The RecordSource for that form is
[tblIntake].

- add into that form all your question fields and format them.

- now using the Subform/Subreport tool box button draw a space for
your subform on the first tab on the Main form. The wizard will ask
you for the linking field which is of course CaseID.

That's it done! Repeat it for the other two tables and you have your
basic "Normalized" structure in place.

Cheers
Brett

Hi, I hope that you all can help me. I am the Program Manager for a teen
shelter. I need help revamping our followup database. The database that we
have been using is not able to do as much as we need it to. What we do is
collect data on a variety of subjects, i.e. living situation, school,
drug/alcohol use, mental health, etc. at intake, 1 month after discharge,
and 6 months after discharge. The database is currently set up in 3
tables - intake, 1 mo, 6 mo. Each table has a corresponding form, query and
report. The tables are pretty much identical to each other - you enter the
resident name, case #, etc. then answer each of the questions, which
predominantly have combo boxes (possible answers yes, no, skip) in the
forms. We then have 3 reports (intake data, 1-mo, 6-mo) which break down
the responses to all the questions for that time period, e.g. #/% enrolled
in school yes, no, skip. But that is all that we can do with it.

I know that the tables are not set up right. I know there should be a table
for residents that has name, case #, intake date, exit date. But then,
should there be intake, 1 month, and 6 month tables, each with the same
questions, or should I break it down by question topic i.e. school,
drug/alcohol, family, etc.? If the latter, how do I then tell the database
to discern between intake, 1 month, and 6 month data?

What I would like my database to do: to be able to select a group of data to
analyze, i.e. intake data on all kids who left the program in 2003, or
compare one youth's intake data to 1-mo and 6-mo, or all 1-mo data to 6-mo
data.

I have been trying to figure this out on my own and by browsing newsgroups,
referring to books, etc. but it's not happening. Can anyone help me?

Thanks,

Kathi Ritchey
Stormbreak

Cheers,
Brett

 

[Stormbreak Youth Program]

Hi Brett, thanks to you and John for the thought you each put into helping
me. I am not sure which approach to try, they are very different from each
other! I have some questions about your suggestion, see below.

Kathi, you're right, your tables are in need of some TLC.

Firstly, you have to get rid of the duplicated data in the three
tables. As you say, just make one table "tblResident" to record all
their personal details. You can retain the current case number but
change the field to an Autonumber so that they increment, if they
don't already.

They do increment; some youth enter the program more than once but they
retain the same case #. So there could be 2 or 3 sets of intake/1 mo/6 mo
data for Jane Doe. This is important info that I should have included in my
original post, sorry. So, should I just do their case #, instead of
Autonumbering? Obviously the intake/exit dates play a role in deciphering
between which data is which.

To start that process
- Make a BACKUP COPY of the whole MDB file so we have something to go
back to
- make yet another copy of one of the tables and name it "tblResident"
- delete all the Question fields out of it, just leave the
non-repeating information about the resident.

Your table "tblResident" then should be something like
CaseID Autonumber Primary Key
Fname Text
Mname Text Middlname or initial
Lname Text
Addr1 Text Address 1
Addr2 Text Address 2
City Text
DOB Date/Time Date of Birth
... etc etc ... NO question fields should remain
dteIntake Date/Time Use common Naming prefixes like dte, cbo,
cmd, where you can. You'll need them later
dteExit Date/Time

Is there a list somewhere of these common Naming prefixes that I could refer
to? I am not familiar.

Now ... as for the questionnaire, to ensure long term analysis of
stats, I would use three tables "tblIntake", "tbl3Month", "tbl6Month"
they would have the same fields and I would structure them to relate
to the CaseID in tblResident. For example
IntakeIDAutonumber Primary Key
Q1 Text or Yes/No Depending on the question. Yes/No
is very good for analysis later when you want
to check against something like ...

But Yes/No would not work if I also have "Skip" as a possible response,
right? I would also like to add N/A as a response, that was not included in
the original database but would be a useful addition. So would the type be
Text?

InSchool Yes/No Easy then to apply the filter that
includes or excludes those in school
...etc etc ...
CaseID Number "Foreign Key" This is the most important
field as it is the one that links all of the
records in this table back to the Resident

To do this,
- Add a new field to the front of each named "IntakeID", "3MthID" or
"6MthID"
- Make that field both Autonumber and the Primary Key
- delete all the fields that you now have listed in tblResident
- Make sure that CaseID (or its equivalent) is no longer a Primary
Key, it is now a Foreign Key
- I would move CaseID to the end of my field list now (that's how I
quickly find my Foreign Keys)

You now have a Normalized database (I think - doing all this in my
head)


Then to your question about how to relate all of these things back
together
- Create a Main Form (sometimes called the Parent form) "frmResident"
which has all of the Resident's details on it.

- Using the "Tab Control" from the Design Toolbox, add 3 tabs to the
bottom of the Main Form

- Once the 3 period tables are created, create a subform (also called
a Child Form) of each "sfmIntake". The RecordSource for that form is
[tblIntake].

- add into that form all your question fields and format them.

- now using the Subform/Subreport tool box button draw a space for
your subform on the first tab on the Main form. The wizard will ask
you for the linking field which is of course CaseID.

That's it done! Repeat it for the other two tables and you have your
basic "Normalized" structure in place.

Cheers
Brett

 

[Stormbreak Youth Program]

Hi John, thanks to you and Brett for the thought you each put into helping
me. I am not sure which approach to try, they are very different from each
other! I have a question about your suggestion, see below.

Consider a completely different design.

Have two tables in a one to many relationship - Cases and Followups.

The Cases table would have case number as the Primary Key, with
specific info about the case in its own right. This table would be
related to a Followups table with a case number (as a foreign key),
and a FollowupPeriod field. If there are three followups, there'd be
three records in this table, with FollowupPeriod equal to 0, 1, and 6
(say). And if there are multiple questions, you need to consider
storing the questions *as data*, not as fieldnames; consider two
tables:

So, would there be 4 Tables - Cases, Followups, Questions, Answers?

Thanks, Kathi

 

[John Vinson]

So, would there be 4 Tables - Cases, Followups, Questions, Answers?

At least that. There might be more (some lookup tables, though not
Lookup Fields in your tables <g>).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Brett Collings [429338]]

Both approaches will work for you Kathi. I'm intirgued by John's
hadn't seen it that way. I would happily retire my suggestion if
John's is technically better. We all learn from one another, that's
the fun of this place.

The thing about not only Access but all such similar applications is
that there are a number of ways to address each and every problem and
often it can come to personal preferrence or the way a developer can
best visualise the development and resulting user tools.

I didn't get into that just yet as too much information can just make
it too hard to get started. With the 3 period tables, I saw the
ability to have a subform with 3 rows of questions, one from each
table so that at a glance you could look down a column and see a
shift. I don't know if this will work visually here, but let's give a
try. This could be a tab with subform display like this (substitute
"Qx" for a real questions)...

Q1 Q2 Q3 Q4
Intake: [ ] [ ] [ ] [ ] etc etc
3 Mth: [ ] [ ] [ ] [ ]
6 mth: [ ] [ ] [ ] [ ]

I visualised that this would give your counsellors a quick view (or
alert) to any changes good or bad at a glance as soon as the file is
displayed.

There's some hocus-pocus (and our help) to get it looking like this
but I thought it worth it.

Brett Collings
CEO
BizSoft Systems
Management and Data Analysts

Hi Brett, thanks to you and John for the thought you each put into helping
me. I am not sure which approach to try, they are very different from each
other! I have some questions about your suggestion, see below.

Kathi, you're right, your tables are in need of some TLC.

Firstly, you have to get rid of the duplicated data in the three
tables. As you say, just make one table "tblResident" to record all
their personal details. You can retain the current case number but
change the field to an Autonumber so that they increment, if they
don't already.

They do increment; some youth enter the program more than once but they
retain the same case #. So there could be 2 or 3 sets of intake/1 mo/6 mo
data for Jane Doe. This is important info that I should have included in my
original post, sorry. So, should I just do their case #, instead of
Autonumbering? Obviously the intake/exit dates play a role in deciphering
between which data is which.

To start that process
- Make a BACKUP COPY of the whole MDB file so we have something to go
back to
- make yet another copy of one of the tables and name it "tblResident"
- delete all the Question fields out of it, just leave the
non-repeating information about the resident.

Your table "tblResident" then should be something like
CaseID Autonumber Primary Key
Fname Text
Mname Text Middlname or initial
Lname Text
Addr1 Text Address 1
Addr2 Text Address 2
City Text
DOB Date/Time Date of Birth
... etc etc ... NO question fields should remain
dteIntake Date/Time Use common Naming prefixes like dte, cbo,
cmd, where you can. You'll need them later
dteExit Date/Time

Is there a list somewhere of these common Naming prefixes that I could refer
to? I am not familiar.

Now ... as for the questionnaire, to ensure long term analysis of
stats, I would use three tables "tblIntake", "tbl3Month", "tbl6Month"
they would have the same fields and I would structure them to relate
to the CaseID in tblResident. For example
IntakeIDAutonumber Primary Key
Q1 Text or Yes/No Depending on the question. Yes/No
is very good for analysis later when you want
to check against something like ...

But Yes/No would not work if I also have "Skip" as a possible response,
right? I would also like to add N/A as a response, that was not included in
the original database but would be a useful addition. So would the type be
Text?

InSchool Yes/No Easy then to apply the filter that
includes or excludes those in school
...etc etc ...
CaseID Number "Foreign Key" This is the most important
field as it is the one that links all of the
records in this table back to the Resident

To do this,
- Add a new field to the front of each named "IntakeID", "3MthID" or
"6MthID"
- Make that field both Autonumber and the Primary Key
- delete all the fields that you now have listed in tblResident
- Make sure that CaseID (or its equivalent) is no longer a Primary
Key, it is now a Foreign Key
- I would move CaseID to the end of my field list now (that's how I
quickly find my Foreign Keys)

You now have a Normalized database (I think - doing all this in my
head)


Then to your question about how to relate all of these things back
together
- Create a Main Form (sometimes called the Parent form) "frmResident"
which has all of the Resident's details on it.

- Using the "Tab Control" from the Design Toolbox, add 3 tabs to the
bottom of the Main Form

- Once the 3 period tables are created, create a subform (also called
a Child Form) of each "sfmIntake". The RecordSource for that form is
[tblIntake].

- add into that form all your question fields and format them.

- now using the Subform/Subreport tool box button draw a space for
your subform on the first tab on the Main form. The wizard will ask
you for the linking field which is of course CaseID.

That's it done! Repeat it for the other two tables and you have your
basic "Normalized" structure in place.

Cheers
Brett

Cheers,
Brett