Too many fields defined in select query

[HeislerKurt]

My database has two tables which are linked in 1 to 1 relationship
because the number of fields exceed 255.

I should add that - believe it or not - the database is normalized.
This is a database for a medical study in which they are collecting
hundreds of data points (lab values, diagnoses, etc.) on each pt. at
one point in time.

I'm trying to create the data entry form (lots of tabs as you can
imagine) and attempting to make the record source a select query which
joins the two tables. But of course I get the "too many fields defined
error" when I run the query. I tried breaking up the query into
several smaller queries and joining them all together, but the error
persists.

Any ideas how I can populate the form? Maybe I should create two
subforms, each based on one of the tables?

Thanks.

Kurt

 

[John Spencer]

If you are going to stick with this structure your only hope is to use
subforms (as you postulated) to allow data entry. Also, you should be aware
that there is a limit of the total size of any record - 2000 characters
excluding Memo and OLE Object fields).

Despite your disclaimer, I would guess that your structure is not normalized
with that many fields.

My guess is that you have should have a table PatientLabTests with three (or
more fields)and fields like
++ PatientID - stores the value of the primary field in the Patient table
++ LabTestID - store the value of a primary field in a table that lists all
the types of lab tests
++ LabTestResult - stores the result for this patient for this lab test
++ TestDate (probably not needed since you say all this data is for one point
in time)
++TestNumber - store the id number assigned by the lab for tracking the test
(again may or may not be needed)

You should have another table for Patient diagnoses (with at least two fields)
++ PatientID
++ DXID (Or DXCode)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Michel Walsh]

You may consider a structure like:

sampleID, parameterName, parameterValue ' fields
1010 xyz 1.1
1010 abc -0.9 'data sample


rather than

sampleID xyz abc ... ' and other fields
1010 1.1 -0.9 ...



which will allow an undefinite amount of parameters, rather that allowing
less than 255.



Vanderghast, Access MVP

 

[HeislerKurt]

Despite your disclaimer, I would guess that your structure is not normalized
with that many fields.

My guess is that you have should have a table PatientLabTests with three (or
more fields)and fields like
++ PatientID - stores the value of the primary field in the Patient table
++ LabTestID - store the value of a primary field in a table that lists all
the types of lab tests
++ LabTestResult - stores the result for this patient for this lab test
++ TestDate (probably not needed since you say all this data is for one point
in time)
++TestNumber - store the id number assigned by the lab for tracking the test
(again may or may not be needed)

You should have another table for Patient diagnoses (with at least two fields)
++ PatientID
++ DXID (Or DXCode)

(Been trying to post a reply for 48 hours and it's not taking. Trying
again ...)

I do see your point. But consider this ...

The investigators want each possible answer to be a Y/N check box. For
example, they've listed up 15 diagnoses (not mutually exclusive) that
a patient may have, so I've created 15 Y/N fields for these options
(reflected as 15 check boxes on the form). My preference would be for
them to store diagnoses using a combo box in a continuous form, but
they prefer the look and feel of check boxes. Labs are handled in
similar way. Patients will always have the same 12 labs done (Lactate,
Wbc, Platelets, etc.), and the values are always numerical. So I've
created 12 fields (text boxes) to store the results for each lab.

Consequently, if I restructure the database as you suggest, am I right
to think that all of the tables would still be related to each other
in a 1:1 relationship?

Thanks!

 

[John Spencer]

Ok, but data storage does not have to equal data presentation.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================