Query most recent date and associated field data into form control (1 Viewer)

[majordoc]

Hello all

Any help gratefully appreciated. I'm not an Access professional - just a mere physician trying to setup a regional level database to help care for people with a specific serious rare disorder.

I have developed a database with a single large table. These patients have numerous investigations - results dates and values. For example, the following NT-ProBNP blood test:

Field: Date_NT-ProBNP_1 Field: Result_NT-ProBNP_1
Field: Date_NT-ProBNP_2 Field: Result_NT-ProBNP_2
Field: Date_NT-ProBNP_3 Field: Result_NT-ProBNP_3
Field: Date_NT-ProBNP_4 Field: Result_NT-ProBNP_4
Field: Date_NT-ProBNP_5 Field: Result_NT-ProBNP_5

I want a form with a control that displays:
- the most recent test result, both date and result (numerical value)
- the test result (date and value) with highest and lowest result

I've managed to design the rest of this database OK just using macros as don't know anything about VBA.
Again any help would be very much appreciated - will make a difference to care of these patients.

Regards

 

[theDBguy]

If it's not too late, I would recommend you change your table structure and break down your single large table into smaller related tables to manage your data more easily.

What you want can probably be done using macros, but if you want to try using VBA, take a look at this.

Microsoft Access tips: MaxOfList() and MinOfList() functions

Code for selecting the minimum and maximum values of a list of values in a Microsoft Access database. Demonstrates ParamArray.

www.allenbrowne.com

 

[moke123]

I have developed a database with a single large table.

That could be a problem.

At the very least you should have a table of patients, and then one or more additional tables concerning tests, etc.

edit: As always DBG beat me to it.

 

[CJ_London]

not enough information to provide specific guidance, just to point you in the right direction

First, a single large table does not sound right - I would expect as a minimum separate tables for patient, consultant, appointment, test results. A single table is the way excel does things, databases work in the opposite way.

You can't have one control to display all this different bits of data, you will need a control for each one

Suggest provide some example data as laid out in your table and the result required from that example data as would be laid out in the form.

 

[majordoc]

Cheers folks. No not too late - I want to get this right as is likely to be used a lot and for a long time!
On basis of your advice I will break single large table into three: 1) patients; 2) investigations; 3) treatments.
That will make three relatively small tables. I will do that know then come back an look. I think I could figure out how to query the oldest date but not another field beside it - I know Date_NT-ProBNP_11 is associated with the adjacent Result_NT-ProBNP_11 but how does Access know!
Will split tables and get back.

Thank you
E

 

[majordoc]

And 4) - appointments.
Thanks again.
E

 

[Gasman]

You would have a table for Treatments/tests.
In that you would have the ID of the patient as a foreign key (FK)
Then you would likely have a field holding the ID of the Treatment also as a FK.
Now a field for the result and another for date, plus anything else for that record, nurse FK etc?

Then you can just look for max date for a particular patient and treatment.

HTH

 

[theDBguy]

...as is likely to be used a lot and for a long time!

I just want to raise this point in case it applies. If you're in the U.S. and storing patient information, you might consider making sure you are complying with HIPAA requirements, just in case.

 

[CJ_London]

think I could figure out how to query the oldest date but not another field beside it - I know Date_NT-ProBNP_11 is associated with the adjacent Result_NT-ProBNP_11 but how does Access know!

it doesn't - you need to tell it - you should have one table for this data with perhaps 4 columns

PK autonumber
PatientFK - link to patient table
testDate
testResult

there may be other relevant information - nature of test or sample taken for example. Depends on whether you are using this table to instigate a test, or just recording them as and when they come in and are not managing a test schedule in any way

 

[majordoc]

Thanks again for all your responses.
First, theDBGuy no I'm not in States. This project is in UK and fully sanctioned following all usual legal norms here.
I can see now I've been approaching this stupidly from a spreadsheet perspective.
Putting in Result_1, Result_2....Result_20 is crazily stupid.
I have divided the original table into 4 - 1) Patients and Referrals; 2) Investigations; 3) Treatment; 4) Appointments.
There are actually > 20 different investigations, not just the above blood test NT-ProBNP. Each test will have a date associated with it.
Obviously I could set up 20 odd tables for each investigation which is inefficient to say the least. Is there any problem setting up one investigation table, each record has one "Date_Investigation" field followed by several different investigations to input results - NT-ProBNP, echo, RHC, ECG etc etc. These investigations are rarely performed on the same date so most of these fields will be blank in any given completed record.
Any problem with this or is there a better way to do this?

Thanks
E

 

[moke123]

Obviously I could set up 20 odd tables for each investigation which is inefficient to say the least. Is there any problem setting up one investigation table, each record has one "Date_Investigation" field followed by several different investigations to input results - NT-ProBNP, echo, RHC, ECG etc etc.

You can set up what is often called a lookup Table for the investigations. It would have a primary key and an investigation type. This way if you had to add a new type of investigation it is simple to add. You are just adding a record and not altering a table. You then have a table to record the patient FK, the date, the investigation type FK , and the results. Tables should be tall and thin.

 

[majordoc]

Thanks - will google furiously now. Is a Lookup table massively different from a normal table?

 

[moke123]

No. A lookup table is just a table. Dont confuse it with a lookup FIELD in a table. Those are bad!

heres a simple example

 

[CJ_London]

Any problem with this or is there a better way to do this?

probably not, but without a clear definition of the 'rules' surrounding what your app is required to do, can't be certain. It is akin to asking 'is it OK to put petrol in my car?'. Probably OK unless your car runs on diesel.

Just to illustrate....

A similar operation unrelated to your requirement might be around stock movement. I favour a single transactions table where if it is a purchase one set of fields within a record are completed, if a sale a different set of fields are completed. Beyond the obvious (stockID, tranDate, quantity) there may be other fields that can be used for both types of transactions..This simplifies mapping stock movements.

Others may prefer to have separate tables for purchases and sales which then need to be combined to map stock movements - but perhaps that is not important to the application.

My way means you cannot maintain referential integrity, the other way means you can. The importance or not of being able to maintain referential integrity is down to the rules.

 

[Pat Hartman]

If you can post the database with sensitive data obfuscated, we can provide more specific suggestion.