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/29/04 

Date: Mon, 29 Mar 2004 06:30:33 GMT

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" <schapel@mvps.org.ns> wrote in message
news:#SAWcNPFEHA.3372@TK2MSFTNGP10.phx.gbl...
> 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
> > 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
> >

Relevant Pages

  • Re: Huge database weighs heavily on small brain
    ... of my SubForms are combo boxes and several of my SubForms have SubForms ... accountable for the proper design and function of the database. ... will display information from multiple subsets of Tables organized by Review ... > put an unbound option group or combobox in the form header, ...
    (microsoft.public.access.forms)
  • Re: Huge database weighs heavily on small brain
    ... And also the subforms... ... > gave you a little better description of my database. ... All the Queries and Reports are ... > will display information from multiple subsets of Tables organized by Review ...
    (microsoft.public.access.forms)
  • Re: Huge database weighs heavily on small brain
    ... Eligibility, Healthcare and PharmacyInterview). ... the Client). ... The Relationships between the the Main Form and the Subforms ... review forms are identical in design to the Main Form, ...
    (microsoft.public.access.forms)
  • Re: Huge database weighs heavily on small brain
    ... Since the Review process, though incorporated in this database, is not ... I would rather not press my luck by having them as Subforms. ... ClientID is automatically entered when a new record is created. ...
    (microsoft.public.access.forms)
  • Re: Huge database weighs heavily on small brain
    ... the Review Period SubForms from my Main Form and it is now quite snappy. ... And also the subforms... ... All the Queries and Reports ...
    (microsoft.public.access.forms)