occasionally changing tables - need easy way to update queries & f

[Guest]

I have a database that requires fields to be added to tables and/or new
tables to be added 1-5 times per month. One of my forms is for new entries,
another form is to edit older entries, and a couple of other forms only show
filtered data (which is filtered via queries). Every time I make a change to
an existing table or add a new table, I have to update every single form.
Almost all of the forms look exactly alike, they are just connected to
different queries. The new info also has to be added to every query.
Is there an easier to may to make changes? Can the forms and queries be
based on a "template" form and a "template" query, so that I only have to
change the "templates" and not each individual form and query?

 

[Guest]

I have a database that requires fields to be added to tables and/or new
tables to be added 1-5 times per month.
Sounds like your database is not 'normalized' and may even be a spreadsheet
type.

Can you post your table structure? Table names, Field names and datatype.

 

[John W. Vinson]

I have a database that requires fields to be added to tables and/or new
tables to be added 1-5 times per month.

Well... then there is clearly something wrong with your table design. A
properly normalized design should add new fields or new tables only when
you're adding *completely new capabilities* to the database. What are these
fields and these tables?

One of my forms is for new entries,
another form is to edit older entries, and a couple of other forms only show
filtered data (which is filtered via queries). Every time I make a change to
an existing table or add a new table, I have to update every single form.
Almost all of the forms look exactly alike, they are just connected to
different queries.

Could you perhaps just dynamically change the Recordsource for the form? If
you're adding new fields to the table, don't you need to add new controls to
the forms for thhose fields?

The new info also has to be added to every query.
Yep.

Is there an easier to may to make changes? Can the forms and queries be
based on a "template" form and a "template" query, so that I only have to
change the "templates" and not each individual form and query?

You can keep the form with its controls and change the form's Recordsource; a
query won't be as easy.

John W. Vinson [MVP]

 

[Guest]

My database stores information about emergency patients and clinical trials
for a medical provider. The people doing the data entry are physicians.

The database was originally designed to capture patient arrival information
and collect information as to which clinical studies they were enrolled in or
the reason they weren't enrolled in particular study.

The database is not normalized in the sense that every clinical trial has
its own table. There are 2 reasons for this:
1) Not all of the same information is captured for each clinical trial.
Sometimes a dosage amount is captured, sometimes it isn't. Also, if a patient
is excluded from a trial, a reason must be given. The list of exclusions are
specific to each clinical trial.
2) It is important that the physicians give info for ALL active trials. If
all the studies were in one table, then, as far as I know, there would be a
subform (datasheet style) on the main form where the physician could enter a
clinical trial for each row of the datasheet. BUT... then it would be up to
the physician to make sure they completed a row for each ACTIVE trial (even
though the old trials would have to remain in the table - but I know I could
use a query to populate the drop down box with only active trials). I can not
leave it up to the physicians to make sure they select all of the appropriate
trials. I have to have an individual textbox field for EACH clinical trial on
the data entry form to ensure they complete all the required data.

So the clinical trials change a couple times a year and we periodically
collect more and/or different information about each trial.

As far as the main table goes... we are always finding new applications for
the database and new data is collected.

This is where the blunt of my problems comes in. As I mentioned before, I
have many different forms. One for new entries (or else the physicians may
forget to hit "ADD NEW" and type over an old entry), one to review old
entries, one form for me that has spaces for data that I am to complete
retrospectively, and other forms for various applications (i.e. a teaching
tools form that are only populated with cases that are designated as a
teaching cases). So each form is populated with a specific query. Most of the
forms are identical (except for form headings), but they are linked to
different queries. All the queries are generally the same, they just have
different criteria. Whenever I add something to a table, I have put it in all
the queries and then modify all the forms. I'm looking for an easier way to
do this.

I hope this helps… and makes sense.