How to use an text box entry to determine which field to bound to another entry box?

[akerenyi]

Hi there,

Another amateur question:

I am developing a medical patient database for our research group and I want to save an excessive amount of intensive care data.

My problem is that there are some variables which need to be registered chronologically precisely and have discreet values, not continuous.

So for example, patients have their blood physiology monitored 1-10 times a day, with each analysis having 10-15 outputs (pH, CO2, etc.) I want to save each variable in separate tables with fields corresponding to each hour over a week, so the header would look like this in the pH table:

ID | 1 hr | 2 hr | .... | 168 hr

Each patient would have about 40 pH values over that 1 week randomly disributed in time.

In the data entry form I would like to avoid putting on 168 text boxes for the user to choose which to fill out. Instead I would like to make one physiology monitor form, where the user can enter the date/time of that particular analysis and those single values of pH, CO2, etc. Than I would like Access to count which hour that date corresponds to, and save the values in the separate tables in the appropriate fields (hr).

I hope I wasn't too long to care, but also understandable. Thank you very much for your help beforehand.

Aron

 

[John Big Booty]

Your current proposed table structure is looking a little de-mormalised. So the first thing you might want to do is get your head around the concept of Data Normalisation this will make the future process of pulling the data together for reporting and display purposes a whole lot easier.

You might also want to have a look at the Data Models here to see if you can use one as a starting point for your DB.

 

[akerenyi]

Thank you very much for you answer.

I have researched data normalization and the data models and here is how I think about building the database now, based on the seen models:

Patients_table
- Patient_ID (PK)
- Address
- Phone
- Date of admission

Patients_tests_table
- Patient_ID
- Test_ID (PK)

Tests_Table
- Test_ID (PK)
- Test_date
- pH
- CO2

This way I could store many test connected to one patient and I could also define q query, which would only look at tests, where (Date of admission) - (Test_date) is < 1, aka the tests that happened on the first day.

My question would be:
a, Why is the middle table necessary (it a joined table, right?)
b, Do you have any comments on how to provide the needed number of test entry forms based on how many test the patient had (i.e. X patient had 2 test on the first day and 4 on the second, so when filling out the data from the first day, the user would need 2 entry options, while 4 for the second day)?

Thank you very much,
Aron

 

[John Big Booty]

That's looking a whole lot better However I'd probably structure thing slightly differently. Perhaps;

TBL_PatientHdr
- Patient_ID (PK)
- Address
- Phone

TBL_PatientHist
- AdmissionID (Pk)
- Patient_ID (FK)
- AdmissionDate
-DischgDate
-AttendingMD (Fk)

TBL_TestHdr
- TestReg_ID (PK)
- AdmissionID (Fk)
- RequstMD (Fk)

TBL_TestDtl
- Test_ID (PK)
- TestReg_ID (FK)
-Tech_ID (FK)
- Test_date
- pH
- CO2

You would use a From and Sub-Form set to populates test tables, with the Sub-Form for the tests being a continuous form. So there should be no real need to limit the numbers of records.

I've asked one of the other AWF VIP's, who is more familiar with the sort of process you are trying to control, to have a look and make some comments.

 

[wiklendt]

Aron,

are you able to post an excel spreadsheet of the kind of data you are collecting to help me conceptualise what data you are collecting? mainly because you say there need to be 2 entry options in day 1 but 4 in day two? are you taking more measurements in day two than in day 1, or are you referring to cumulative data?

 

[akerenyi]

Hi there,

I have made a simplified example. It is more measurements on day 2 than day 1, for example. So my problem is basicly with designing the entry form, which is optimalized for the user, who has hospital documentation from each day separately, with all the data from that particular day. I.e. ideally she wouldn't have to look up all the blood tests to fill out a FRM_blood_test, but rather have the FRM_day fill out the TBL_Tests.

I hope its more understandable now. Obviously there are many more clinical parameters that we monitor whcih I also try to link to the patients (different types of tests, drugs/infusions, etc)

Thank you all very much.
Aron

 

[wiklendt]

Hi Aron,

can you please clarify what you mean by "count which hour that date corresponds to"? it seems that each measurement is at an arbitrary time (i.e., not always at 12pm, 3pm, 6pm and 9pm but varying) and surely the date can be entered manually or by automatically applying the current date?

Then I would like Access to count which hour that date corresponds to, and save the values in the separate tables in the appropriate fields (hr).

 

[wiklendt]

I want to save each variable in separate tables with fields corresponding to each hour over a week, so the header would look like this in the pH table:

ID | 1 hr | 2 hr | .... | 168 hr

Each patient would have about 40 pH values over that 1 week randomly disributed in time.

Have you revisited this organisation since John Big Booty advised on normalisation? I'm concerned that you are saving these data in separate tables in order to display the data "like in excel"... are you happy to have the data saved in a better way if we help you?


also, i think i know what you meant by "calculate which hour that date corresponds to" - do you mean you want to know the "time, in hours" of any particular data point since the first reading for each patient?

 

[akerenyi]

Hi,

Thank you all for your help. I will clarify our work briefly, I hope it helps.

I work with neonatologist at an ICU for newborns. This means that everything needs to be monitored hourly (physiological parameters, blood tests, infusions/drugs) and everything is crucial in their first 168 hours of life, in life-hours.

This means that for example if a baby is born at 15:25 on 15/10/2011 and the first blood test could be done at 17:35 on the same day, the important time-dimension for us is that the test was done at 2 hours of life. These tests were done randomly, however the infusions and physiological variables were monitored continuously.

The database will be filled in retrospectively, from hospital documents.

I did read up on normalization and I am willing to incorporate, with much appreciation any constructive comments from you guys.

Right now I'm trying to find the best design for the tables structure, but right after that I will have to optimalize to input forms to match the structure of hospital documents.

Thank you for your kind help.
Aron

 

[spikepl]

You probably already know this much better than I do, but just in case:

I believe that a number of regulatory requirements exist for applications like yours, concerning traceability of data and changes therein, such as provenance, who adds what and when, perhaps securing the input against typos and misplaced decimal separator, and finally some qualification test prior to operational use.

 

[akerenyi]

Yes, I already have input masks for the text boxes and I will try to use as many combo boxes as possible to reduce human error.
I haven't given much thought yet to the tracability of the changes, how can I manage that most easly, maybe with a work log? How can I get acces to make one?

 

[spikepl]

Hmm , just to confirm, .. is this db a pure research tool? Or a tool to be actually used in patient care, which would likely make it subject to various regs? For logging changes there are various posts on this here: search for audit trail. Audit trail is also discussed at this site http://allenbrowne.com/appaudit.html (that site is a gold mine of all kinds of things Access)

 

[akerenyi]

This is a pure research tool for our private purposes, patient care uses a big, national medical software, which has no connection to our small access database. This is only to surpass having to look up everything in the wirtten patient files.
I will dive into that site now

 

[akerenyi]

Hi there,

I have read everything through and I would set up my patients-blood test relationships like this:

Patients_table
- Patient_ID (PK)
- Address
- Phone
- Date of birth

Tests_Table
- Test_ID (PK)
- Patient_ID (FK)
- Test_date
- Test_hour
- pH
- CO2

It is a one-to-many relationwhip, because one Test_ID can only belong to one patient. I also have left out John Big Booty's suggestions, because we are not interested in which docter ordered the particular test. I have created a continuous subform to enable for user-defined data entry.

I will start a new thread for my other problem with the continuous variables. I would much appreciate all your comments on that one as well.

Thanks a lot,
Aron

 

[wiklendt]

Tests_Table
- Test_ID (PK)
- Patient_ID (FK)
- Test_date
- Test_hour
- pH
- CO2

Hi Aron, i've just sat down to look at this properly. i would strongly advise against using separate fields for test date and test hour - this will make it harder to calculate your "time, in hours" since first test.

I would also advise that you have a separate table for your test types, this will make it easier to add other types, queries etc will also be easier with a more normalised structure (i.e., the separate table for test types). you would then join test types to your test data table with foreign keys.

Something i have done in a previous database (will hunt this down) is a situation where i needed different tests depending on the type of bacteria we were working on (i.e., Gram negative vs Gram positive). instead of getting the user to individually add each of the 20 different tests, i had a junction table with the 'panel' of tests grouped together, then, when i knew i had a Gram negative bacterium, i would simply chose the "Gram neg" panel in a combo box and click a button (append query hidden behind it) to say "apply this panel" at this time to this sample. This then presented me with only the relevant tests for that sample type, and i could fill in the results for those.

i was thinking this sort of thing would work well for you in terms of the different tests you do depending on the date/time of each test - this of course can only happen with a normalised structure for test types.

let me know if this sounds like it would be useful for you.

Agnieszka.

 

[wiklendt]

You probably already know this much better than I do, but just in case:

I believe that a number of regulatory requirements exist for applications like yours, concerning traceability of data and changes therein, such as provenance, who adds what and when, perhaps securing the input against typos and misplaced decimal separator, and finally some qualification test prior to operational use.

In terms of medical data, the only major requirement is to have de-identified patient details - that is, NOTHING that can identify the patient from that electronic source (this is especially important if the database is held on a server - even more so if the server is external (i.e., not a hopsital intranet-type of thing). This is stuff like MRN, Patient Name, Patient DOB and Patient Address/phone. These are all directly identifiable information.

The way we have to store de-identified data is by using just the autonumber PatientID and absolutely required patient data (e.g., admission & discharge dates, plus clinically relevant data for our research). This PatientID is then referenced back to a local list which can identify the PatientID to a name adress etc. of course, much of our research doesn't care about the patient name/address, so we don't even store that info.

The only way you'd be allowed to store identifiable info is if you have a truly secure DB, like major hospitals would have - that is, with secure access to the DB site (i.e., server/website), and then with secure access to the DB itself (e.g., pwd). Though i am not sure of the exact rules here, since we don't deal with such DBs.

Hope that helps.

 

[akerenyi]

Hi there,

First, to Agnieszka: Thanks for your date/time suggestion, I will follow your example.

Regarding test types my tables are currently set up like this:

tbl_TestType
-TestTypeID
-TestType name

tbl_TestDataItems
-DataItemID
-Test Data Item name

tbl_PatientTestResults
-PatientTestResultID
-PatientID
-TestTypeID
-DataItemID
-TestType
-DataItem
-Test Date
-Data Item Value

I would like to have an intermediate table for data item combinations in the particaluar tests (tbl_DataItemsInTests), but I don't really know how to set it up. I am basicly doing what you did with the Gram-neg/pos panels, how did your junction table look like?

I have another problem, it relates to forms, but I think it's a minor one. I have the following tables:

tbl_DrugInfusions
-DrugInfusionID
-Drug Infusion Name

tbl_PatientDrugInfusions
-PatientInfusionID
-PatientID
-DrugInfusionID
-DrugInfusionName
-Start date
-Speed

tbl_Patients
-PatientID
-Patient Name
-Date of birth

I have set up a main form based on tbl_Patients and a subform based on tbl_PatientInfusions. I have set up DrugInfusionName as a combo box, for witch the source data is set for Drug Infusion Name from tbl_DrugInfusions. Now the form saves the drug infusion name in the tbl_PatientInfusions, but not the DrugInfusionID values.

If I add the tbl_PatientInfusions to the raw data source selection with the connection between the InfusionID fields, all options disappear from the combo box (probably because that particular record already has a PatientInfusionID and a PatientID value, but no InfusionID)

Anyone have any suggestions?

Thanks a lot,
Aron

 

[wiklendt]

easy one first:

I have set up a main form based on tbl_Patients and a subform based on tbl_PatientInfusions. I have set up DrugInfusionName as a combo box, for witch the source data is set for Drug Infusion Name from tbl_DrugInfusions. Now the form saves the drug infusion name in the tbl_PatientInfusions, but not the DrugInfusionID values.

If I add the tbl_PatientInfusions to the raw data source selection with the connection between the InfusionID fields, all options disappear from the combo box (probably because that particular record already has a PatientInfusionID and a PatientID value, but no InfusionID)

firstly i assume you have the correct master/child properties set for your subform. next i would suggest you use a DrugInfusionID combo, with two columns in the source query but have the first column hidden (size 0cm). this ought to save the FK number in the table - currently it sounds like you're trying to save the Name in the FK field, which wouldn't work.

Second:

I would like to have an intermediate table for data item combinations in the particaluar tests (tbl_DataItemsInTests), but I don't really know how to set it up. I am basicly doing what you did with the Gram-neg/pos panels, how did your junction table look like?

This is where i put in my disclaimer: i'm a beginner just like you.

Now that that's out of the way, this is how i did my tables - mind you i don't know if this is the best way to do it (there's a 'loop' in the structure, which i'm not sure should be there, but it works for me). there are also a couple of associated action queries behind buttons on a form which do all the work (i tried SQL in the button forms but found that simply calling the action queries was MUCH easier).

i should also say that my DB doesn't automatically detect gram neg/pos and apply the correct panel, as a microbiologist i make that decision and [edit] manually choose which panel to apply [/edit] to the appropriate sample.

i've been working on a sample specifically for your case, mainly because just showing you my above relationship layout won't tell you much unless you know the ins-and-outs of phenotypic testing by that method....

 

[akerenyi]

Dear all,

Happy new year to all of you, I hope you had some nice relaxation over the holidays.

Agnieszka, thank you very much for your help. I have tryed to follow your example in both questions.

First, the combo box issue. What I'm trying to do is only select the InfusionName in the combo box, but to also save the corresponding InfusionID in the other field of the table. Right now I can either save the FKs or the infusion names, but not both. Or is there a way to auto-fill the field that the combo-box doesn't connect to?

Second, the junctional table issue. Thank you for your table-structure. Please correct me if I'm wrong, but in analogy, your tblPhoenixPanelAbs is the junctional table that contains the possible antibiotics for each panel-type, for example Gram-pos panels can have penicillin, other beta-lactame Ab-s and Gram-neg panels can have glycopeptides, fluorokinolons, etc.

I would be really interested in how your do forms work, especially the one that is based on tblPhoenixMICs. I presume you have to enter the particular run into tblPhoenixRuns-form and after that you can fill out the tblPhoenixMIC-form, but does that form already filter the possible antibiotic-options based on the type of test that has been run?

Also, I think if you could provide me with a specific sample, it would be a great help.

Thanks a lot,
Aron

 

[wiklendt]

Hi Aron. Sorry for the tardy reply.

First, the combo box issue. What I'm trying to do is only select the InfusionName in the combo box, but to also save the corresponding InfusionID in the other field of the table. Right now I can either save the FKs or the infusion names, but not both. Or is there a way to auto-fill the field that the combo-box doesn't connect to?

Wind back a sec - you're trying to select the Infusion name in what combo on what (parent?sub?)form? What do you want to happen when you select this Infusion record (add an infusion to a patient or to a regiment or other)?

I would be really interested in how your do forms work, especially the one that is based on tblPhoenixMICs. I presume you have to enter the particular run into tblPhoenixRuns-form and after that you can fill out the tblPhoenixMIC-form, but does that form already filter the possible antibiotic-options based on the type of test that has been run?

Yep, basically when you create a new run, you select the panel used and a subform is automatically populated with the appropriate antibiotic for that panel. I'll dig out the phoenix forms - they're currently embedded in a MUCH larger DB, so may have to do some work on it so the example is functional.

Also, I think if you could provide me with a specific sample, it would be a great help.

When I extract my phoenix 'node' into a standalone example, as I just suggested above, this will hopefully give you a functional sample of how it all works and relates. Unfortunately that might take some time because of the way it is integrated into the database and it's been a looooong time since i looked at it all carefully... here goes!

Cheers,
Agnieszka.