Huge database weighs heavily on small brain

[Fred Worthington]

Greetings:

My database has too many subforms on too many levels. That is my conclusion
based on the fact that new subforms are not displaying properly and I
sometimes get a "Not enough memory" message (I have 1 GB of RAM). The
problem stems from the fact that I must archive virtually every Field in
every Form every six months while maintaining the data from each previous
six month period, etc. So, I have been creating an exact image of the Main
Form (including all subforms) to accommodate this task (and nesting them in
the Main Form as well). My Main Form always contains current data which is
the basis for my Queries and Reports, thus requiring that old data be moved
to the Archives. This worked fine for a while, but now I'm working on the
24 month archive period, and things are grinding to a halt. What I would
like to do is separate these Archive periods from the Main Form so they can
stand on their own, either within the same database or as a separate
database. Whether I separate the forms and tables within the existing
database, or create a new database for the Archives, I must link them to the
Primary Form/Database by the Client ID so I can enter and retrieve data for
individual records. I thought maybe a Macro (to open a regular Form to
either view an existing record or open a new one) would work, but so far I
haven't been able to get it to register a new record (since the Form is no
longer a SubForm) or match an existing one using the Client ID. I need to
maintain the design and relationships (as well as the data) of my existing
subforms and tables, but just need to "kick them out of the house" so to
speak. Any ideas on how to streamline a database by breaking it up while
still maintaining the important Client ID links? Perhaps there is a better
way to archive data than duplicating the original Forms. I am open to
suggestions. Also, I am using Access 2003.

Thanks . . . Fred

 

[Niklas Östergren]

Hi Fred!

I may not be the right man to help you out and my english is a little bit
rusty so please forgive me if my Q is alittle bit odd!

Do you realy need to save the main form every month? Isn´t it just the
records in the table´s that´s interesting to save (backup)?

// Niklas

 

[Byron]

Fred,

Take a look in the help file for "Split an existing
database between its data and its objects". This will let
you backup or archive your data without having to have
the forms, queries, macros, modules and reports with it.
The size of what you actually have to backup or archive
will be much less.

Besides, this it actually the way that Microsoft suggests
that database applications built using Access be built.

HTH

Byron

 

[Steve Schapel]

Fred,

Here's an essential idea that might help you: Forms don't have data!

"My Main Form always contains current data" is not true The data is
in your tables. The form provides you with an avenue via which you can
see the table data, but the data is independent of the form. You have
complete control over which data is seen via the form. The most
straightforward way this is done is by altering the form's Record
Source. Either by using macro or vba procedure to change the forms's
recordsource property setting as required, or by using a query as the
recordsource, and controlling the data via the query criteria. Make
sense? Therefore, your idea of replicating the subforms over and over
for each date range is definitely going to cause the type of probelms
you have experienced. The first thing to consider now is whether it is
really necessary to "archive", i.e. move data from one place to another?
In practice, this only normally makes a noticeable difference to
database preformance when you get quite large numbers of records
(100,000s). Otherwise, I would recommend this approach:
- leave all the data in one place
- use just one form
- include a calculated field in the query that the form is based on, to
identify each 6-month period
- put a combobox or option group on the form to select which 6-month
period you want to see the data for
- refer to this combobox/option group in the criteria of the query

 

[Fred Worthington]

Steve,

Thanks for your detailed response. It will take a little time to road-test
your recommendations, but I am going to give it a go. I'm still a little
unclear as to how I will control my data links (i.e., Master/Child links
between Main Form and Sub-Form). The original Master Form and nested
Subforms behave quite nicely. It is the Archived duplications of the
original that are bogging things down (as I believe you have already
surmised). I assume you are suggesting I change the record source of every
form (including nested Subforms) in the original Master Form? I am further
assuming the arhival data will be linked to the Main Form by way of Queries.
This database contains dozens of Tables and hundreds of Fields. I currently
have about 2,600 records (based on your remarks regarding same, not anywhere
near capacity), but still complex enough to present a few challenges in the
organization department. If I am understanding you correctly, I will have
the same number of Tables, but one set of Forms, and they will all be
correctly linked by the ClientID through the use of Queries. When I select
a different Table by using one of the triggers you suggest (combo box,
option group) all the fields in the existing Forms will change to the
specific data being called up. I'm going to work on this tonight and all
day tomorrow, so I'll keep you apprised of my progress and report
complications as they arise. Thanks again for your help.

Fred

 

[Fred Worthington]

Thanks Niklas, and your English is not all that bad. You are right, it is
just the records in the Tables that I need to update and review. I am going
to pursue the recommendations of Steve Schapel (see message in this string).
I think he has expanded somewhat on your comments.

Thanks . . . Fred

 

[Fred Worthington]

Thanks, Byron,

I actually did try splitting this database, but, after the deed was done
couldn't figure out what to do next. I think I need to prepare this
database by scaling it down a little (see Steve's comments this string),
then I can maybe make better use of a split database. At this point I don't
really want users to have much control over the design of the database. My
experience has been, the more "user friendly" a database the less likely the
uninitiated are to "tinker" with the controls. I think it will take me a
little time to fully implement the recommendations I've been given. I will
post additional messages in this string as I progress with my task. Your
observations and comments will be appreciated.

Thanks . . . Fred

 

[Steve Schapel]

Fred,

Without knowing more about your actual tables and data, and the existing
forms, it is difficult to give specific advice. To be honest, when you
talk about "dozens of Tables and hundreds of Fields", I become
suspicious that the actual table design is also in need of revision, as
well as the forms. But here's a simplified example of the kind if thing
I mean... Let's say you have a table with records that have a date
field which goes back to the beginning of the year 2002. Ok, let's say
you make a query based on this table, and because you want to relate to
the 6-monthly periods, in the query you put a calculated field like this...
DatePeriod: Year(Date())-2002+IIf(Month([YourDateField])>6,2,1)
So this field will evaluate to 1 for January 2002 to June 2002, and 2
for July to December 2002, and 3 for January to June 2003 etc. OK, and
let's suppose your main form is based on this query. So then you can
put an unbound option group or combobox in the form header, with each
option/line corresponding with one of the 6-monthly date ranges. So in
the query, in the criteria of the calculated DatePeriod colun, you refer
to the combobox or option group using syntax such as
[Forms]![NameOfYourForm]![NameOfOptionOrCombo]. And all you need to do
then is put a line of code like this:
Me.Requery
on the AfterUpdate event of the combobox or option group, and hey
presto, you just select the option in the option group or combobox, and
the form immediately will display the data for the required 6-monthly
period. And then, if the subforms are correctly related to the main
form, they will automatically show the correct data as well - you
shouldn't need to do anything with the Link Master Fields and Link Child
Fields. Hope this makes sense.

 

[Fred Worthington]

Thanks for hangin with me, Steve. And, since you are, it might help if I
gave you a little better description of my database. It is used by a
non-profit organization that gathers and tracks medical information on
individuals. I have about 16 Tables with corresponding Forms. (My estimate
of dozens comes from the fact that I multiply this number for each 6 month
evaluation period, and that has been going on for nearly 3 years). Several
of my SubForms are combo boxes and several of my SubForms have SubForms
(mostly Combo Boxes). I have a couple of sets on tabbed pages. Two of my
data groups are Brand and Generic Drugs, which number about 500 each. The
actual number of Fields I have to deal with (including Subforms) is
approximately 165 (not counting the multiple entries in the Combo Boxes).
If it ended here, I probably wouldn't have a problem, but, as you know, I
have duplicated this set of Tables and Forms (and nested them as a subset in
the Main Form) several times. The folks who administer this program rely on
the results of nearly 50 Reports (based on Queries) for analysis and
evaluation. Whereas I cannot be responsible for the honesty of the Client
or the competence of the data entry person (who is not me), I am, obviously,
accountable for the proper design and function of the database.

To me, the most important aspect of this database is the Main Form and
original set of SubForms nested therein. All the Queries and Reports are
based on this primary set of Tables, and if I can maintain their integrity,
I'll at least know the Reports are accurate. However, the biggest challenge
for me is going to be the modifications you suggest to the Main Form so it
will display information from multiple subsets of Tables organized by Review
Period and knitted together by the Client ID. As long as the controls are
straight forward and easy to comprehend, the database operator should have
no trouble keeping data organized (e.g., entering archival data in the right
tables and not failing to properly update the current ones on which the
Reports are based).

It is probably important for you to understand the significance of the 6
month review period. This review takes place every six months for each
individual, not the organization. Therefore, there are several reviews
taking place every day (currently there are 2,821 people in the program). I
already have date fields on the Main Form that calculate the 6, 12, 18, 24,
30, 36, and 48 month review dates for each individual based on the date they
entered the program. In its present state, these fields are simply a visual
que for the operator. These dates can also be referenced by a Report that
generates a list of individuals who are due for a periodic review for a
given month (by setting the date criteria in the Query). So I already know
the date the review must take place. I must now implement the controls you
have described to display either an existing record or a clean slate
(depending on whether the user is entering or reviewing data). My
assumption is that all my tables will remain in tact (a separate set for
each review period), while all but the Main set of Forms will be removed.
One question comes to mind, and that regards the Client ID link between
tables of different review periods. Will the controls you have discussed
automatically enter and/or find the proper ID when the Form displays the new
tables, or will this have to be done manually?

I am going to begin this process today. I hope you will continue to patrol
this group for the next couple of days as I grapple with this challenge. It
is difficult to find a specific set of instructions for this type of
operation in the Help Files. I'm sure it's all there somewhere, but not all
in one place and not that easy for someone at my level to compile and
implement. I appreciate you help immensely.

More later . . . Fred

Fred,

Without knowing more about your actual tables and data, and the existing
forms, it is difficult to give specific advice. To be honest, when you
talk about "dozens of Tables and hundreds of Fields", I become
suspicious that the actual table design is also in need of revision, as
well as the forms. But here's a simplified example of the kind if thing
I mean... Let's say you have a table with records that have a date
field which goes back to the beginning of the year 2002. Ok, let's say
you make a query based on this table, and because you want to relate to
the 6-monthly periods, in the query you put a calculated field like this...
DatePeriod: Year(Date())-2002+IIf(Month([YourDateField])>6,2,1)
So this field will evaluate to 1 for January 2002 to June 2002, and 2
for July to December 2002, and 3 for January to June 2003 etc. OK, and
let's suppose your main form is based on this query. So then you can
put an unbound option group or combobox in the form header, with each
option/line corresponding with one of the 6-monthly date ranges. So in
the query, in the criteria of the calculated DatePeriod colun, you refer
to the combobox or option group using syntax such as
[Forms]![NameOfYourForm]![NameOfOptionOrCombo]. And all you need to do
then is put a line of code like this:
Me.Requery
on the AfterUpdate event of the combobox or option group, and hey
presto, you just select the option in the option group or combobox, and
the form immediately will display the data for the required 6-monthly
period. And then, if the subforms are correctly related to the main
form, they will automatically show the correct data as well - you
shouldn't need to do anything with the Link Master Fields and Link Child
Fields. Hope this makes sense.

--
Steve Schapel, Microsoft Access MVP

Steve,

Thanks for your detailed response. It will take a little time to road-test
your recommendations, but I am going to give it a go. I'm still a little
unclear as to how I will control my data links (i.e., Master/Child links
between Main Form and Sub-Form). The original Master Form and nested
Subforms behave quite nicely. It is the Archived duplications of the
original that are bogging things down (as I believe you have already
surmised). I assume you are suggesting I change the record source of every
form (including nested Subforms) in the original Master Form? I am further
assuming the arhival data will be linked to the Main Form by way of Queries.
This database contains dozens of Tables and hundreds of Fields. I currently
have about 2,600 records (based on your remarks regarding same, not anywhere
near capacity), but still complex enough to present a few challenges in the
organization department. If I am understanding you correctly, I will have
the same number of Tables, but one set of Forms, and they will all be
correctly linked by the ClientID through the use of Queries. When I select
a different Table by using one of the triggers you suggest (combo box,
option group) all the fields in the existing Forms will change to the
specific data being called up. I'm going to work on this tonight and all
day tomorrow, so I'll keep you apprised of my progress and report
complications as they arise. Thanks again for your help.

Fred

 

[Steve Schapel]

Fred,

I had imagined that the 6-monthly period was based on a calendar period,
but now I realise it is specific to each person's participation on the
program. No problem. So does this mean that at present you are moving
records from one table to another every day, as people's review date
arrives? Fred, I am not sure what to advise here. You have a system
which is in real live usage, which really should have a fair bit of work
done on it. Your idea of a separate "set" of tables for each 6-month
period is frankly not a good one. I would recommend only one set of
tables, with all the data. I can see no reason at all for doing
otherwise. Can you give some examples of what your tables are, and the
fields within the tables? And also the subforms... I can't quite grasp
your meaning at the moment regarding "Several of my SubForms are combo
boxes and several of my SubForms have SubForms (mostly Combo Boxes)" -
subforms and comboboxes are two quite different concepts.

If my rudimentary understanding of what you've got is approximately
correct, making some of the changes I would suggest will result in your
database becoming *hugely* simpler and more maintainable, so it will be
well worth the effort to sort it out. The first and most important step
is to get the table design right. This may not be too difficult, and
taking care of your existing data may just involve a few Append Queries.

 

[Fred Worthington]

Steve,

Thanks for the comeback. I am truly grateful for your help.

Regarding the movement of records from one table to another every day, the
answer is yes. When I first began working on this database, I was not aware
that the users would want to archive virtually every field in every table
for each 6 month period for each individual. Hence the rather clumsey
retro-fit. There is no doubt this database needs an overhaul, and if you
are willing to help me, I will do my best to communicate what is going on at
my end.

When you mention one set of tables for all data, I assume you mean current
and archival (and the archival records are going to continue to grow). This
sounds great, but I am unclear as to how all this data with virtually
unlimited duplicate sets can reside in one place. And further, I can't
quite visualize a single set of primary Forms (Main and Sub) displaying
unique sets of data based on the Client ID for either the current period, or
one of several review periods without the Form having unique links to
separate tables for the current and each review period. I don't doubt for a
second you know what you're talking about. I just haven't quite grasped the
concept yet.

Regarding the combo boxes as subforms, I have mislead you there. They are
not combo boxes, but subforms displayed in Datasheet View. Sorry about
that. I have several of these SubForms that display lists of items like
Drugs, Doctors, Pharmacies and Medical Conditions. Most of these forms are
based on Queries that sort the items in the Table alphabetically. Most of
the Tables contain very few fields. For example, the Client Brand Drug
Table contains only three fields - ClientBrandDrugID, ClientID and
BrandDrugID. The BrandDrugID is a Lookup field that uses a Combo Box for
the Display Control (hence my reference to Combo Boxes), because the Drug
List is in the Brand Drug Table which contains only two fields - BrandDrugID
and BrandDrugName (this table contains 673 items). The Row Source for the
Client Brand Drug Table (which is the source for the SubForm), is the Brand
Drug Query which populates its fields from the Brand Drug Table (this is to
facilitate the presentation of Drug items in alphabetical order). Other
Tables containing only two or three fields are ClientGenericDrug,
ClientHospital, ClientMedicalConditions, ClientPharmacy,
ClientPrimaryDoctor, ClientReferral, etc. And, each of these tables uses
the same Row Source and Query technique I described for the ClientBrandDrug
Table (e.g., ClientHospital uses Hospital as its row source, and a Query
sorts the list alphabetically for display in the Form, etc.).

The Main Form contains several SubForms. Three of those SubForms contain
quite a few Fields and they each contain one or two SubForms as well
(primarily to edit and display multiple items in a list such as those
mentioned above). These three SubForms are expansive enough to take up as
much display real estate as the Main Form, so they sort of need to remain
SubForms to keep the Main Form from requiring a lot of scrolling to get
around. One thing that would help is if these three SubForms could somehow
be opened via a command button, but I haven't been able to figure out how to
do that so the Subform will form a Master/Child Link. The way I have them
set up now is to minimize their size with just a corner of the Form visible
in the Master Form from which to display the ClientID (the number by which
all objects are linked) and a "check box" that I use to set the link. Once
the SubForm is "registered" with the Main Form it can be opened via a
Command Button that has the following line of code in its Event Procedure to
apply a filter so only the linked Child Form will be displayed:
stLinkCriteria = "[Client ID]=" & "'" & Me![Client ID] & "'"DoCmd.OpenForm
stDocName, , , stLinkCriteria

I hope I've given you enough incite to proceed. At this point, I am
completely in your hands. I am eager to implement your recommendations, and
am prepared to do whatever you suggest to rebuild this database so it
functions properly. I know you know this, but I must be mindful of the data
contained in these Tables as I move things around. The skill with which a
house is remodeled would not be so greatly appreciated if the furniture got
detroyed in the process.

I really appreciate your willingness to give so much of your valuable time
to help me with this task. I will do my best to achieve a successful
outcome in your honor.

Thanks . . . Fred

 

[TC]

Fred, jumping in, just a couple of tips that might help.

You probably need to read-up about the topic of database "normalization".
This is a fundamental requirement of a properly designed database (in
Access, or otherwise). Start here for a dry, but instructive, read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

Normalization is usually performed as the first step in designing a
database, before you even >think< about writing any forms or reports.
Normalization ensures that you store the data in a way that mimics the
natural (real-world) relationships between the various data entities
(patients, drugs, whatever). Those natural relationships normally >do not<
depend on how you want the forms & reports to look. The normalization
process comes first - the queries, forms & reports come next.

Not suggesting that you junk your existing database & start again! But you
should definitely take these comments on board, before you write another
one.

HTH,
TC

Steve,

Thanks for the comeback. I am truly grateful for your help.

Regarding the movement of records from one table to another every day, the
answer is yes. When I first began working on this database, I was not aware
that the users would want to archive virtually every field in every table
for each 6 month period for each individual. Hence the rather clumsey
retro-fit. There is no doubt this database needs an overhaul, and if you
are willing to help me, I will do my best to communicate what is going on at
my end.

When you mention one set of tables for all data, I assume you mean current
and archival (and the archival records are going to continue to grow). This
sounds great, but I am unclear as to how all this data with virtually
unlimited duplicate sets can reside in one place. And further, I can't
quite visualize a single set of primary Forms (Main and Sub) displaying
unique sets of data based on the Client ID for either the current period, or
one of several review periods without the Form having unique links to
separate tables for the current and each review period. I don't doubt for a
second you know what you're talking about. I just haven't quite grasped the
concept yet.

Regarding the combo boxes as subforms, I have mislead you there. They are
not combo boxes, but subforms displayed in Datasheet View. Sorry about
that. I have several of these SubForms that display lists of items like
Drugs, Doctors, Pharmacies and Medical Conditions. Most of these forms are
based on Queries that sort the items in the Table alphabetically. Most of
the Tables contain very few fields. For example, the Client Brand Drug
Table contains only three fields - ClientBrandDrugID, ClientID and
BrandDrugID. The BrandDrugID is a Lookup field that uses a Combo Box for
the Display Control (hence my reference to Combo Boxes), because the Drug
List is in the Brand Drug Table which contains only two fields - BrandDrugID
and BrandDrugName (this table contains 673 items). The Row Source for the
Client Brand Drug Table (which is the source for the SubForm), is the Brand
Drug Query which populates its fields from the Brand Drug Table (this is to
facilitate the presentation of Drug items in alphabetical order). Other
Tables containing only two or three fields are ClientGenericDrug,
ClientHospital, ClientMedicalConditions, ClientPharmacy,
ClientPrimaryDoctor, ClientReferral, etc. And, each of these tables uses
the same Row Source and Query technique I described for the ClientBrandDrug
Table (e.g., ClientHospital uses Hospital as its row source, and a Query
sorts the list alphabetically for display in the Form, etc.).

The Main Form contains several SubForms. Three of those SubForms contain
quite a few Fields and they each contain one or two SubForms as well
(primarily to edit and display multiple items in a list such as those
mentioned above). These three SubForms are expansive enough to take up as
much display real estate as the Main Form, so they sort of need to remain
SubForms to keep the Main Form from requiring a lot of scrolling to get
around. One thing that would help is if these three SubForms could somehow
be opened via a command button, but I haven't been able to figure out how to
do that so the Subform will form a Master/Child Link. The way I have them
set up now is to minimize their size with just a corner of the Form visible
in the Master Form from which to display the ClientID (the number by which
all objects are linked) and a "check box" that I use to set the link. Once
the SubForm is "registered" with the Main Form it can be opened via a
Command Button that has the following line of code in its Event Procedure to
apply a filter so only the linked Child Form will be displayed:
stLinkCriteria = "[Client ID]=" & "'" & Me![Client ID] & "'"DoCmd.OpenForm
stDocName, , , stLinkCriteria

I hope I've given you enough incite to proceed. At this point, I am
completely in your hands. I am eager to implement your recommendations, and
am prepared to do whatever you suggest to rebuild this database so it
functions properly. I know you know this, but I must be mindful of the data
contained in these Tables as I move things around. The skill with which a
house is remodeled would not be so greatly appreciated if the furniture got
detroyed in the process.

I really appreciate your willingness to give so much of your valuable time
to help me with this task. I will do my best to achieve a successful
outcome in your honor.

Thanks . . . Fred

Fred,

I had imagined that the 6-monthly period was based on a calendar period,
but now I realise it is specific to each person's participation on the
program. No problem. So does this mean that at present you are moving
records from one table to another every day, as people's review date
arrives? Fred, I am not sure what to advise here. You have a system
which is in real live usage, which really should have a fair bit of work
done on it. Your idea of a separate "set" of tables for each 6-month
period is frankly not a good one. I would recommend only one set of
tables, with all the data. I can see no reason at all for doing
otherwise. Can you give some examples of what your tables are, and the
fields within the tables? And also the subforms... I can't quite grasp
your meaning at the moment regarding "Several of my SubForms are combo
boxes and several of my SubForms have SubForms (mostly Combo Boxes)" -
subforms and comboboxes are two quite different concepts.

If my rudimentary understanding of what you've got is approximately
correct, making some of the changes I would suggest will result in your
database becoming *hugely* simpler and more maintainable, so it will be
well worth the effort to sort it out. The first and most important step
is to get the table design right. This may not be too difficult, and
taking care of your existing data may just involve a few Append Queries.

if

I

of

my

know,

I

so

it

18,

24,

date

they

controls

you

the

new

challenge.

It

not

all

 

[Fred Worthington]

TC,

Thanks for the Tip. I checked out the article and will definitely keep the
rules of normalization in mind as I revise this database. Actually, I have
created separate Tables that contain directories for Drugs, Doctors,
Pharmacies, etc., however, my repeated replication of the entire database
for the purpose of archiving data is an especially egregious violation of
the rules, and that must be corrected - pronto.

Jump in anytime . . . Fred

Fred, jumping in, just a couple of tips that might help.

You probably need to read-up about the topic of database "normalization".
This is a fundamental requirement of a properly designed database (in
Access, or otherwise). Start here for a dry, but instructive, read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

Normalization is usually performed as the first step in designing a
database, before you even >think< about writing any forms or reports.
Normalization ensures that you store the data in a way that mimics the
natural (real-world) relationships between the various data entities
(patients, drugs, whatever). Those natural relationships normally >do not<
depend on how you want the forms & reports to look. The normalization
process comes first - the queries, forms & reports come next.

Not suggesting that you junk your existing database & start again! But you
should definitely take these comments on board, before you write another
one.

HTH,
TC

Steve,

Thanks for the comeback. I am truly grateful for your help.

Regarding the movement of records from one table to another every day, the
answer is yes. When I first began working on this database, I was not aware
that the users would want to archive virtually every field in every table
for each 6 month period for each individual. Hence the rather clumsey
retro-fit. There is no doubt this database needs an overhaul, and if you
are willing to help me, I will do my best to communicate what is going

on

at
my end.

When you mention one set of tables for all data, I assume you mean current
and archival (and the archival records are going to continue to grow). This
sounds great, but I am unclear as to how all this data with virtually
unlimited duplicate sets can reside in one place. And further, I can't
quite visualize a single set of primary Forms (Main and Sub) displaying
unique sets of data based on the Client ID for either the current

period,

or
one of several review periods without the Form having unique links to
separate tables for the current and each review period. I don't doubt

for

a
second you know what you're talking about. I just haven't quite grasped the
concept yet.

Regarding the combo boxes as subforms, I have mislead you there. They are
not combo boxes, but subforms displayed in Datasheet View. Sorry about
that. I have several of these SubForms that display lists of items like
Drugs, Doctors, Pharmacies and Medical Conditions. Most of these forms are
based on Queries that sort the items in the Table alphabetically. Most of
the Tables contain very few fields. For example, the Client Brand Drug
Table contains only three fields - ClientBrandDrugID, ClientID and
BrandDrugID. The BrandDrugID is a Lookup field that uses a Combo Box for
the Display Control (hence my reference to Combo Boxes), because the Drug
List is in the Brand Drug Table which contains only two fields - BrandDrugID
and BrandDrugName (this table contains 673 items). The Row Source for the
Client Brand Drug Table (which is the source for the SubForm), is the Brand
Drug Query which populates its fields from the Brand Drug Table (this is to
facilitate the presentation of Drug items in alphabetical order). Other
Tables containing only two or three fields are ClientGenericDrug,
ClientHospital, ClientMedicalConditions, ClientPharmacy,
ClientPrimaryDoctor, ClientReferral, etc. And, each of these tables uses
the same Row Source and Query technique I described for the ClientBrandDrug
Table (e.g., ClientHospital uses Hospital as its row source, and a Query
sorts the list alphabetically for display in the Form, etc.).

The Main Form contains several SubForms. Three of those SubForms contain
quite a few Fields and they each contain one or two SubForms as well
(primarily to edit and display multiple items in a list such as those
mentioned above). These three SubForms are expansive enough to take up as
much display real estate as the Main Form, so they sort of need to remain
SubForms to keep the Main Form from requiring a lot of scrolling to get
around. One thing that would help is if these three SubForms could somehow
be opened via a command button, but I haven't been able to figure out

how

to
do that so the Subform will form a Master/Child Link. The way I have them
set up now is to minimize their size with just a corner of the Form visible
in the Master Form from which to display the ClientID (the number by which
all objects are linked) and a "check box" that I use to set the link. Once
the SubForm is "registered" with the Main Form it can be opened via a
Command Button that has the following line of code in its Event

Procedure

to
apply a filter so only the linked Child Form will be displayed:
stLinkCriteria = "[Client ID]=" & "'" & Me![Client ID] & "'"DoCmd.OpenForm
stDocName, , , stLinkCriteria

I hope I've given you enough incite to proceed. At this point, I am
completely in your hands. I am eager to implement your recommendations, and
am prepared to do whatever you suggest to rebuild this database so it
functions properly. I know you know this, but I must be mindful of the data
contained in these Tables as I move things around. The skill with which a
house is remodeled would not be so greatly appreciated if the furniture got
detroyed in the process.

I really appreciate your willingness to give so much of your valuable time
to help me with this task. I will do my best to achieve a successful
outcome in your honor.

Thanks . . . Fred

Fred,

I had imagined that the 6-monthly period was based on a calendar period,
but now I realise it is specific to each person's participation on the
program. No problem. So does this mean that at present you are moving
records from one table to another every day, as people's review date
arrives? Fred, I am not sure what to advise here. You have a system
which is in real live usage, which really should have a fair bit of work
done on it. Your idea of a separate "set" of tables for each 6-month
period is frankly not a good one. I would recommend only one set of
tables, with all the data. I can see no reason at all for doing
otherwise. Can you give some examples of what your tables are, and the
fields within the tables? And also the subforms... I can't quite grasp
your meaning at the moment regarding "Several of my SubForms are combo
boxes and several of my SubForms have SubForms (mostly Combo Boxes)" -
subforms and comboboxes are two quite different concepts.

If my rudimentary understanding of what you've got is approximately
correct, making some of the changes I would suggest will result in your
database becoming *hugely* simpler and more maintainable, so it will be
well worth the effort to sort it out. The first and most important step
is to get the table design right. This may not be too difficult, and
taking care of your existing data may just involve a few Append Queries.

--
Steve Schapel, Microsoft Access MVP


Fred Worthington wrote:
Thanks for hangin with me, Steve. And, since you are, it might help

if

I
gave you a little better description of my database. It is used by a
non-profit organization that gathers and tracks medical information on
individuals. I have about 16 Tables with corresponding Forms. (My estimate
of dozens comes from the fact that I multiply this number for each 6 month
evaluation period, and that has been going on for nearly 3 years). Several
of my SubForms are combo boxes and several of my SubForms have SubForms
(mostly Combo Boxes). I have a couple of sets on tabbed pages. Two

of

my
data groups are Brand and Generic Drugs, which number about 500

each.
The

actual number of Fields I have to deal with (including Subforms) is
approximately 165 (not counting the multiple entries in the Combo Boxes).
If it ended here, I probably wouldn't have a problem, but, as you

know,

I
have duplicated this set of Tables and Forms (and nested them as a subset in
the Main Form) several times. The folks who administer this program rely on
the results of nearly 50 Reports (based on Queries) for analysis and
evaluation. Whereas I cannot be responsible for the honesty of the Client
or the competence of the data entry person (who is not me), I am, obviously,
accountable for the proper design and function of the database.

To me, the most important aspect of this database is the Main Form and
original set of SubForms nested therein. All the Queries and

Reports
are

based on this primary set of Tables, and if I can maintain their integrity,
I'll at least know the Reports are accurate. However, the biggest challenge
for me is going to be the modifications you suggest to the Main Form

so

it
will display information from multiple subsets of Tables organized

by
Review

Period and knitted together by the Client ID. As long as the

controls
are

straight forward and easy to comprehend, the database operator

should
have

no trouble keeping data organized (e.g., entering archival data in

the
right

tables and not failing to properly update the current ones on which the
Reports are based).

It is probably important for you to understand the significance of

the

6 18, date a
visual Report
that

for

a already
know controls the challenge. not

 

[Fred Worthington]

Steve,

Resolving my archival issues may be easier than we thought. I was reviewing
one of your previous messages where you suggested using a macro or vba
procedure to change the Main Form's record source. I have been
experimenting with changing the record source of my main form to open the 6,
12, 18 . . . month tables that were previously viewed with a nested SubForm
designed just like the main form, and it seems to work. How do I create a
Macro that will change the record source automatically? Also, when I open
one of the other Tables, it displays the correct Client ID as well as the
data, however, when I open a review period in which no data has been
previously recorded for the Client whose record is currently open, it does
not insert the Client ID. I suppose this can be entered manually, but it
would be better to have Access do the deed, would it not? I have removed
the Review Period SubForms from my Main Form and it is now quite snappy. I
think that was the main problem.

Thanks . . . Fred

 

[Fred Worthington]

Steve,

Well, I think my previous message regarding succesfully opening other tables
by changing the record source in the main form may not work so well after
all. At first it looked like the data was matching up, but upon more
careful scruitiny I have discovered it is not. Can't understand why.
Perhaps you have a clue.

Fred

 

[Steve Schapel]

Fred,

At this stage, I am unable to comment specifically, as I don't know any
of the relevant details. Can you post back with an indication of the
fields in the table(s) that your main form is based on, and similarly
with the most significant subforms. And an indication of the
relationships between the main form and the subform. And an indication
of what determines which data belongs to which 6-monthly grouping, and
how you have gone about calculating this so far. Thanks.

A note in response to your earlier post... I expect it would be possible
to work a system of keeping a separate table for each 6-monthly period,
and changing the record source of the form dynamically. But this is not
the best solution, and since you have also expressed a willingness to
re-structure your database, I figure we'll re-structure.

 

[Fred Worthington]

Steve,

If I am understanding you correctly regarding the table fields on which the
forms are based, they are as follows:

The Main Form contains primarily personal and contact information (name,
address, marital status, education, etc.). There are eight Forms nested in
the Main Form (Doctor, Pharmacy, Referral, BrandDrugs, GenericDrugs,
Eligibility, Healthcare and PharmacyInterview). The first five in this list
are single forms that are displayed in Datasheet View so multiple records
can be entered and displayed. The last three (Eligibility, Healthcare and
PharmacyInterview) are standard Forms and each has additional subforms (also
Datasheet View)nested within them. Eligibility deals with income, insurance
coverage, activity levels and caregiver assistance. HealthCare deals with
doctor and hospital visits as well as general health conditions. The
PharmacyInterview covers medical conditions, special aids and behaviors,
physical capacity and allergies. The Master/Child links for all are based
on the ClientID. (NOTE: The Client ID is not AutoNumber - it is the SS# of
the Client). The Relationships between the the Main Form and the Subforms
are all one-to-many. The data that is archived in the 6 month periodic
reviews is at the discretion of the data-entry person, however, because the
review forms are identical in design to the Main Form (including Subforms),
they have the option of archiving any or all of the data that resides in the
Main Form and its SubForms. The Main Form contains an array of calculated
date fields that display review dates from 6 to 48 months based on the date
they enter the program. There are no controls associated with these fields,
however I have created a Report based on a Query that generates a list of
individuals who are due for a review (any period) for a specific time frame
(i.e., the month of April). Regarding the archive scheme, the Main Form is
the only one that ever changes because it must always contain current
information. All my Queries and Reports on based on the Main Form.
Presently there is no requirement to formally analyze and evaluate the data
in the Review Forms. The Review Form designation (6, 12, 18 . . .)
corresponds to the length of time the Client has been in the program.
(E.g., the 36 month review would take place 36 months after entry in the
program).

I hope this answers your questions. If not, let me know and I'll refine my
answers. I'll check back with the Group tomorrow. I don't know what time
zone you are in, but it won't be long before the sun pops up where I am
(Central), so I'm checking out for the night. Thanks, as always, for your
help.

Later . . . Fred

 

[Steve Schapel]

Fred,

You're doing a great job of filling out the details. But I'm afraid I
still don't quite get it. I can't see what you have been trying to
achieve by your multiple copies of tables and forms every six months. I
guess I don't understand the concept of the Review. Are you trying to
track the changes over time of the clients' doctors, drugs, etc? Is
that the primary purpose? And also to track changes that might take
place in "personal and contact information"? Or is the Review on the
6-monthly review dates something else entirely? And if so, what data
does this generate, and where is that kept? Sorry, but I'm still
missing something fundamental here!

On another point, am I correct in my interpretation that each client
will at any given time have more than one doctor recorded? More than
one pharmacy, more than one referral, etc, as represented by the subforms?

 

[Fred Worthington]

Steve,

This review process (in itself) is probably not the best system out there.
One problem with the design is that it was not included in the
specifications for the original database, which, on its own, is still quite
functional. As an afterthought, I had to figure out some way to give the
users the option of archiving virtually every field of data in every Form on
a 6 month periodic basis. My self-destructing method of resolving this
issue seemed, at the time, to be the best way to accommodate this
requirement. Now, of course, I know better.

The way the archive process works now is this: When a client reaches one of
the 6 month anniversaries of their entrance into the program, the data in
the Main Forms (Tables) is updated to reflect current information. As the
data entry person performs this task, they record the information that was
changed in the appropriate review Forms. This way they can see, for
example, who the Client's current doctor is, as well as the doctor they were
seeing 6 months ago, 12 months ago, etc. It's basically a linear history of
the Clients activities and services. Bear in mind that the numeric
designation for each review period (6, 12, 18 month . . .), indicates the
time elapsed since their entry into the program. So, if a Client has been
in the program for 24 months, and you want to know what their file looked
like 6 months ago, you would look in the 18 month review. If you wanted to
know what their file contained 6 months after they entered the program
(effectively 18 months prior to their 24 month anniversary), you would then
look at the 6 month review. Unlike the Main Form, the Review Forms are
never updated. They just get moved down the line as each successive review
period in completed. At this time there is no plan afoot to set a time
limit on the review process. So, theoretically, they could be recording
archival data years from now (though I hope not).

The purpose of this Review process is as you state. To track changes over
time of virtually every component of the Client's file (name, address,
doctor, drugs, health conditions, income, insurance coverage, etc.). The
reason for this review is to reassess circumstances relevant to treatment
programs and eligibility. The purpose of the program is to provide
medication to Clients at a reduced (or no) cost. The degree of assistance
they recieve from the Foundation is based on an individual needs assessment
derived from a rather complex appraisal of the information contained in this
database.

You are correct in assuming that the purpose of all the subforms that appear
in Datasheet View (doctors, pharmacists, drugs, hospitals, etc.) is to allow
multiple entries. All the fields in the Main Form as well as those
contained in the three standard subforms, Eligibility, Healthcare and
Pharmacy Interview, are for single entries. In the case of subforms nested
within these subforms, they appear in Datasheet View to allow multiple
entries.

I have created a temporary work-around so the database can continue to be
used while we deliberate over its final form. I have removed the
Master/Child link to the Review Forms from the Main Form (essentially by
deleting them from the Form) so the Periodic Reviews can be called up
manually, and the Client ID along with archival data can be entered as
needed. If someone wants to review the history of a Client, they can simply
open the Review Form and locate the desired records using the Client ID.
I'm sure there is a better way for all of this, but that is my temporary
work-around.

Thanks again, Steve, for going these extra miles with me.

Later . . . Fred

 

[Fred Worthington]

Steve,

Just a note to let you know of my progress in the world of Macros. I
believe I have finally worked the bugs out of my Macro that opens the Review
Forms from the Main Form. I observed an interesting phenomenon when I
opened the Review Forms with my 'Open Form" command button. Once the Form
opened, I would navigate to the desired record by conducting a search for
the Client ID. The data would come up, but because the Name Fields use
=[Forms]![ClientProfile]![LastN], etc. to fill their respective fields, the
names don't display properly without the Master/Child link to the Main Form.
I have further noted that, because of this method of displaying data in
those fields, names are virtually nonexistent in the Review Tables. Whereas
the Names are convenient for users, they are nonessential to the integrity
of the tables because all associations are based on the Client ID, so I am
not too concerned about it. But now, enter my Macro, and behold . . . not
only does it open the desired Review record (based on the Client ID), but it
also fills in the Name Fields. I must be livin' right. It may just be
possible to restore a modicum of functionality to this database so the
troops won't get too testy while we perform surgery.

Later . . . Fred