Clinical database, bit muddled

[braduk]

Hello

I have a simple database query which I am sure that Access (or even Excel) can do easily but I can't think of the right way to go about it.

The relevent data is arranged in a single massive table.

There is a series of fields with demographic data, then a series of dates when the lab samples were processed, then a series of fields with text data all of which needs analysis when the query is complete.

Each record is based on a unique lab specimen number so there are lots of lab specimens per patient. Every recored includes the patient number but, as above, there may be many records with the same patient number. In fact, in the example below the sample for blood and the sample for csf will have different lab specimen numbers but the same patient number.

An example:

Lab specimen number
Patient number
Sample type, either blood or csf
Date of result

There are lots of other fields but these are the ones I want to search on.

So, firstly I want:
All patients who have a result for both blood and csf

Secondly I want
All patients who have had a blood result within 24 hours of their csf result.

Can anyone nudge me in the right direction.

Thanks

R

 

[wiklendt]

is this data currently in excel? make a 'table' out of these excel cells and use dropdown options in each header to hide/display relevant fields.

you can create a 'table' in excel by using the 'table' button in the 'insert' ribbon if you're using excel 2010 (i think it's the same in 2007)

 

[braduk]

Thank you

I have it in a Excel table at the moment but can easily get it into Access

I am not sure how to get from the current list of records of lab specimens to one arranged by patient numbers which meet the criteria.

 

[wiklendt]

let's start with basics: what version of excel are you using?

 

[braduk]

Hello

Thanks

Mac OS Lion
Excel for Mac 2011
or
Excel for Windows 2007 running in a Windows XP VM

I am fairly happy using filters from tables in Excel and simple queries in Access but I can't think where to start with this one.

 

[wiklendt]

ok, see this visual guide in Excel 2010 (should be same/similar in 2007). just a quick mockup for you so you can continue in your work.

read it from left to right, top to bottom. this is a great way to filter/sort data i excel without messing up the 'records' (i.e., doesn't mixup rows when sorting a particular column like the old way of sorting in previous versions of excel).

we do many small projects this way (in excel with filters).

 

[braduk]

Hi

It is very very kind of you to go to the trouble of making a guide for me but I think I have not been totally clear in what I need to do.

I have a table. I can select for all of the relevant sample types using the filters in Excel.

But

The data is arranged by lab specimen number not by patient. Each record does have a patient number, however,

Therefore if I do as you suggest in your guide it will merely provide me a list of lab specimen numbers. I need to convert this to a list of patients who have both blood and csf results.

 

[wiklendt]

this is not going to be a problem - all the data in the table will filter along with your selection, just make sure to have ALL your data selected when you make the table.

 

[braduk]

How will the filter provide an answer to:

"show me all the patients who have a result for both csf and blood"

I need each patient in as a row.

 

[wiklendt]

How will the filter provide an answer to:

"show me all the patients who have a result for both csf and blood"

I need each patient in as a row.

your patients should already be in a row...

do you mean you want each patient listed only once? as in, unique values as you would in access? to get a quick-n-dirty, from this excel example, after filtering i'd select the patient column, copy/paste into a new sheet then "remove duplicates" from the data ribbon....

unless you mean something else again? please be specific, as the filter explained does already show you patients (unless your patient details are in a different sheet)?? i assumed all your data was in one sheet, is this an incorrect assumption?

 

[braduk]

No, each row is a lab specimen number.
It is all in one sheet.

 

[wiklendt]

so where are your patient details?

 

[wiklendt]

the other thing you could do, depending on how your excel spreadsheet is used, is link it as a table in access and simply run a query on the relevant fields.

let me know if you need help with this avenue.

 

[braduk]

Thank you again.

I am not sure if I am explaining things properly

Each record is currently a single row in Excel.

The fields in each record are, in simplified form:

Specimen number
Specimen type either blood or csf
Patient number
Date of result
Result information one
Result information two

The specimen number is unique to each row the patient number will occur in many rows because the same patient will have had a number of samples sent for testing.

I know how to import and run queries in Access I just cannot work out what the query is.

What I want is a list of all the patients that have had a blood test and a csf test. I then want the patients who have had a blood and csf tests on the same day, or within 24 hours of each other.

 

[braduk]

Hello

I have made some progress

I have run a report which sorts everything by patient, so I now have a list of patients who have blood and csf results and when they were sent. It runs to nearly 300 pages though.

I need to limit this to patient who have had blood and csf results on the same day.

I first ran a query in specimen type ="blood" OR "csf"
I then made a report of that query using patient number as the highest grouping level and then everything else not grouped.

So, there is a mechanism to sort the patients how I need but I need it to churn out another table rather than report and to be able to run a query on that.

Thanks