Re: Huge database weighs heavily on small brain

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Fred Worthington (fredworthington_at_prodigy.net)
Date: 03/28/04 

Date: Sun, 28 Mar 2004 17:58:25 GMT

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

"Steve Schapel" <schapel@mvps.org.ns> wrote in message
news:ue2F9YKFEHA.2768@tk2msftngp13.phx.gbl...
> 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
>
>
> Fred Worthington wrote:
> > 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

Quantcast