Synchronizing forms/subforms to display the same record

[Guest]

I work at a hospital and I am building a database to keep track of patients
who have undergone a certain set of procedures. This database should
essentially be just a spreadsheet with a pretty interface--there are no
one-to-many relationships, just one table with a record for each patient.
The problem I have run into is that, since we are gathering a large number of
data points for each patient, I've exceeded the 255 field limit for tables.
I tried splitting the table apart and using a query to bring everything
together, but then I discovered that queries also have the 255 field limit.

I now have a main form which consists of a tab control, each page of which
has a subform based on its own table. For example: frmMain has tabs named
Demographics, Diagnosis/Therapy, Complications, and Follow-up. The
Demographics page has sfrmDemographics, which is based on tblDemographics,
the Diagnosis/Therapy page has sfrmDiagnosisTherapy, which is based on
tblDiagnosisTherapy, and so on for the rest of the pages. Also, the
patient's medical record number serves as the primary key in each of the
tables, and tblDemographics has a one-to-one relationship with each of the
other tables.

How can I tie it all the subforms together so they behave as one form based
on a single table? Have I made a good start, or have I just made things more
complicated than they need to be?

 

[Guest]

The problem you are having is because of the way you have your data
structured. There should not be just one record for each patient, it should
be two tables. The first should be a row for each Patient's demographic
information. The second should be your data points and have a row for each
Patient/Data Point combination. That way, your main form would give you
information on the patient, and the sub form would contain the data point
information for the patient.

 

[Guest]

But won't that still send me over the 255 field limit?

 

[Guest]

No, that is the purpose of chaning the structure. I don't know how many
fields you require for each data point, but it can't possibly be that many.
What I know and can guess is that for each datapoint you will need:
Patient_ID - this shoul not be the patients name, it should be the primary
key value of the Patient table. Usually it is an autonumber field.
DataPoint_ID - Somthing to identify what the meaning of the data point is.
Actually, you will find in most database applications this will also be the
primary key value to a table that has the description of the data point.
DataPoint_Value - Whatever the measurement was for this datapoint.
DataPoint_Date_Time - Now I'm guessing, but probably you will want to know
when the measurement was taken.

The way to look at it is to see what all fields you now have in common for a
data point. That becomes a row (record) in your table rather than a group of
fields the way you have it defined currently.