Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011

    Unanswered: Muddled with Access


    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.

    For the sake of argument I have:

    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.

    The problem is that the data is in rows based on lab specimen number rather than patient number.

    Can anyone nudge me in the right direction.



  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    not knowing the table structure its a little hard to comment

    presumably you must have some way of matching a patient to a lab sample (and no doubt there could be more than one sample for a patient.
    to find results within a time span look at the date time functions

    select my,comma,separated,column,list from mytable
    join anothertable on anothertable.thiscolumn = mytable.thatcolumn
    where <insert your shere clause>
    order by some,column sequence
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2011
    Thank you very much for your reply.

    The data is arranged in a single massive table.

    There is a series of fields with demographic data, then a series of dates when the samples where 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 original the sample for blood and the sample for csf will have different lab specimen numbers but the same patient number.

    Does this help explain things a little?

  4. #4
    Join Date
    Dec 2011

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts