Re: Huge database weighs heavily on small brain

From: Steve Schapel (schapel_at_mvps.org.ns)
Date: 03/28/04

  • Next message: connieharper: "Re: DSUM syntax problem" 
    Date: Mon, 29 Mar 2004 06:34:47 +1200

    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
>
  • Next message: connieharper: "Re: DSUM syntax problem"

    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: Subreports and SQL
      ... Subreports and subforms are not the same. ... relevant to neither forms nor reports. ... > difficulties with having subforms in an Access database and then migrating ...
      (microsoft.public.access.tablesdbdesign)
    • Re: Huge database weighs heavily on small brain
      ... And also the subforms... ... >> accountable for the proper design and function of the database. ... All the Queries and Reports ... >> month review period. ...
      (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)
    • 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)