Huge database weighs heavily on small brain

[TC]

Glad that it helped!

TC

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


on period, for grasped
the forms
are

Most

of is
to

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 furniture
got help
if

by

a

information

on Two
of Form
so which
the the 12,
18,

simply

a for set
for displays
the but
not

 

[Fred Worthington]

Steve,

Yesterday, during a conversation with one of the people who use this
database, a point came up that may deserve some consideration. You seem to
be leaning toward a single Table design or a single Form that would
interface with multiple Tables, and the idea sounds fine to me. However,
when I mentioned these potential changes (stating that currently they were
basically theories under consideration), the user responded by asking, "You
mean I won't be able to see the Main Form and the Review Form at the same
time?" She has been "Tiling" the two Forms when she archives data during a
Review. I got the distinct impression that any alterations to the database
that included removing this capability would not be well received.

Presently, since I got the Macro to work, the database is humming along
quite smoothly. At this point I don't see any real need to change the Main
Form. Without the encumberance of the Review Forms (as nested Subforms), it
couldn't be any snappier. If I limit the Review Forms to sets of 4 each,
(6,12,18, 24), (30,36,42,48), etc., and cluster each group of 4 in a single
Form that displays each on a tabbed page (the current configuration), I
should be able to (in Theory) grow this database, ad infinitum. I noted in
the Access Help Files (under "Specifications"), that the limit for number of
objects in a database is 32,768. If that is the wall, I don't see how I
will ever hit it with this design. What say ye?

Fred

 

[John Vinson]

"You
mean I won't be able to see the Main Form and the Review Form at the same
time?" She has been "Tiling" the two Forms when she archives data during a
Review.

No, it does NOT mean that. It's perfectly possible to have two Forms
open referencing the same table.

There would be limitations if the user wants to edit the same *record*
at the same time using the two forms, but otherwise it should work
fine.

 

[Steve Schapel]

Hi Fred,

I am not "leaning toward a single Table design". Let be go back a step
or two. In database design, there is very little room for "leaning".
The structure of the data itself more or less determines the correct
structure for the database design. The best idea is to get the tables
set up correctly first. Before you have done this, you should try to
eliminate the word "form" from your vocabulary. However, after you get
the data design right, it is time to provide the user interface for
accessing the data in the tables, so this is then the time to design
your forms. As mentioned earlier, froms do not have fields, forms do
not have data. Forms have a record source property, on the basis of
which is determined which data is accessed via the form at any given
time. Most often, the record source is a query, based on which you can
apply selection criteria, and bring together data from more than one
table if required. If you wish to see more than one data set, of the
same structure, any attempt to do so by using replicas of the form for
each data set will inevitably lead to a huge jump in your paracetomol
expenditure.

OK, back to the tables. The primary consideration in deciding whether
an entity requires a separate table is whether there is a one-to-many
relationship between the real data. In your case, you have clients, and
for each client, certain data is reviewed periodically, with the need to
retain historical records. That means there is a one-to-many
relationship between the Client and the Reviews. For some of the data
categories related to the reviews, there can be more than one entry, for
example drugs. I am sorry, I am right in the middle of a conference
right now, so I haven't got the time to do a thorough analysis of your
whole system. But this is a skeleton of the tables you should have...

Table: Clients
ClientID
ClientName
Address
other client-specific data

Table: Reviews
ReviewID
ClientID
ReviewDate
ReviewType (i.e. 6, 12, 18 etc)
other review-specific data

Table: Doctors (lookup table)
DoctorID
DoctorName
DoctorPhone
other docter-specific information

Table: ClientDoctors
ReviewID
DoctorID

Table: Drugs
DrugID
NameOfDrug
DrugType (i.e. generic, brand)

Table: ClientDrugs
ReviewID
DrugID

etc

After you get that all sorted, the process of getting hold of whatever
data you want at any given time will be relatively trivial.

 

[Fred Worthington]

Hello Steve,

In your very first response about 3 days ago, you mentioned using Macros and
VBA procedures to automate the tasks that were strangling my database. At
the time I hadn't had enough experience with Macros (in Access) to make good
use of them, but as it turns out, that seems to have been the best route to
go all along. I now have a Macro to open each Review Form, and each command
button is strategically located next to a calculated date field that
reflects the due date for the Review (sound familiar?). I have a lot of
Tables, but they're well organized and the structure is quite simple. I
have gained a healthy respect for your advice regarding the sequence of
devloping Tables and Forms. Presently I have given users the capacity for 8
Reviews (up to 48 months), and have not noticed any degradation in
performance, and probably never will. Reviewers (the folks who use the
database for analysis) should really like the new setup as well. By tiling
the Forms, it is quite easy to see at a glance (for individual Clients)
clear trends over time for a wide range of treatment and behaviors. The
Queries and Reports take care of Global perspectives.

My Table array is very much as you suggest and my one-to-many relationships
are pretty straight forward. Removing the Reviews from my Main Form and
setting up the Macros was the key. Also, I abandoned my earlier idea to
cluster Review Periods on tabbed pages - that would just be asking for more
of the same trouble later on.

With a few very inciteful tips and a little hand holding from you, I believe
I've got a pretty good handle on this database and I feel good about the way
it is performing. I still have two questions, though. One, can my Macros
automatically insert the Client ID in the Review Forms (so that they are
written to the Table and not just to appear in the Form) when they are
opened? And two, what in the world is a paracetomol expenditure?

Thanks . . . Fred

 

[Fred Worthington]

Hello John,

You may not remember, but you have given me some very valuable assistance on
a number of occasions. Thanks for dropping in on our discussion.

At this time, all my Forms and Tables are pretty much one-on-one. But
opening an array of Forms simultaneously facilitates a good overview of
trends regarding individual Clients. It's virtually impossible to
anticipate every perspective from which a Reviewer may want to see his data
(via Queries and Reports), but easy access to the Forms as well as some
convenient command buttons that pop open a few key Tables in datasheet view
should satisfy just about anyone's curiosity.

Take care . . . Fred

 

[Steve Schapel]

Fred,

A few comments...

... Presently I have given users the capacity for 8 Reviews

This is a statement which would not make sense in a normalised database.
The type of structure I suggested before automatically implies
infinite reviews per client.

With a few very inciteful tips

Hope you meant 'insightful'

...can my Macros
automatically insert the Client ID in the Review Forms

Your Review form should be a (continuous or datasheet view) subform on
the Client form, linked on the ClientID field, and as such the ClientID
for the review will be automatically entered.

... what in the world is a paracetomol expenditure?

In my country, paracetomol is the "standard" drug for the relief of
headache. What do you use?

 

[Fred Worthington]

Steve,

Yes, I did mean insightful. I should be more careful since I'm not using
spell-check.

Since the Review process, though incorporated in this database, is not
integral to it, I would rather not press my luck by having them as Subforms.
Once bitten by a snake, its hard to pick another one up. All of the
reconciliation, accounting, reporting, etc., is based on current data.
Therefore, the Reviews could, for all practicality, reside in another
database (and maybe that is worth consideration). As long as those records
are accessible via the Client ID, everyone will be happy. I'm not even
certain precisely how the archived data is being used, and until I can get a
definitive answer to that, I am at a disadvantage to know the best way to
set it up. My goal is to eventually have "the powers that be" re-evaluate
the Review process and try to reduce the amount of historical data they want
to keep.

All my Subforms are set up as you say, linked on the ClientID field, and the
ClientID is automatically entered when a new record is created. As the
Review Forms are currently set up (not as Subforms), the ClientID will be
entered manually. I was thinking maybe the Macros could be configured to do
this automatically, as a convenience, not a necessity. The important thing
is that the ClientID be recorded in the Table, and not just displayed in the
Form.

Fortunately, I rarely have headaches, but next time I do I'll try to get my
hands on some paracetomol.

Thanks . . . Fred

 

[Steve Schapel]

Hi again, Fred.

... I would rather not press my luck by having them as Subforms.

This, like my other recommendations, is aimed at simplifying and
streamlining what you are doing.

Therefore, the Reviews could, for all practicality, reside in another
database (and maybe that is worth consideration).

Sorry, I don't think this is worth consideration. Your database is very
simple, if you want to set it up correctly. This idea would add one
further unnecassary complication.

As long as those records
are accessible via the Client ID, everyone will be happy.

Yep, that's what I had in mind!

I'm not even
certain precisely how the archived data is being used, and until I can get a
definitive answer to that, I am at a disadvantage to know the best way to
set it up.

Fred, this is incorrect! The best way to set it up is according to
database design principles, based on the nature of the data. How the
data is being used is almost totally irrelevant. I'm talking about the
tables here. The consideration if "how used" comes in later.

My goal is to eventually have "the powers that be" re-evaluate
the Review process and try to reduce the amount of historical data they want
to keep.

Why? The amount of historical data should have no impact at all on
anything.

 

[Fred Worthington]

Hello Steve,

When I first created this database, I had 10 one-to-many Subforms that
recorded multiple items for each record. I created 3 one-to-one Subforms
just to consolidate data so my Main Form wouldn't be a mile long. At this
stage, the database hummed along without a hitch, and everyone was happy.
It was a good, functional, well constructed database that I think conformed
to reasonable standards of database design. Then, I was presented with the
task of recording bi-annual reviews that would archive every field in the
database virtually for the life of the program, which could be decades. I
hope you can understand why I am reluctant to tear apart the original
platform to accommodate this monstrosity. Based on the way this database is
being used, I feel I can confidently speculate that the purpose of the
Reviews has little to do with the Function of the database. As I have
stated, none of the 49 Reports is based on Archival data. The people who
manage this non-profit program are not responsible for the health and
treatment of Clients, (doctors and pharmacists bear that burden), so
liability can't be the motivation. I know data is going into the Reviews,
but I don't know anyone who accesses the data. So, I think it is just
something they think they should have just in case they ever need it. I'm
the one who wanted to integrate the Reviews so they would function like
Subforms, and now I think that was a mistake, and I also think any further
effort to do so would be unconstructive. What turned things around for me
was the Macro. With those little buttons I was able to give data entry
people, as well as reviewers, a simple way to enter and access their
archived data without having to re-design the database. Before my Macro
revolution I thought I had no choice but to make way for the Reviews.

I'm not trying to abandon the project. You state that my database is very
simple if I set it up correctly, and I'm all for that, and welcome your
suggestions. I just don't want to spend time (especially yours) building a
bridge if there is no river to cross. To me those Reviews are like family
members who come to town for a long visit - you love 'em but you're still
relieved when they accept your invitation to stay at the Red Roof Inn.

What say ye? . . . Fred