Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009

    Unanswered: Complex query/earlest date


    I have an access database that holds fairly complex data, and I'm at the point where I cannot do what I want in the design view of access and need to use ?sql. I have a list of patients who had many scans each and I need to choose the earliest scan for each patient that satisfies certain quality control criteria. At the same time I have other tables with details of diagnosis, etc. I have done a query in the design view of access that gets me everything I want but gives me all "good quality" scans for each patient. I was wondering if you could help me modify this in SQL to give me only the earliest scan that satisfies criteria.

    This is what it looks like:

    SELECT Registration.[Hospital number_Hospital], Registration.Name, Registration.DOB, Registration.Hospital, MRI.followup, MRI.[Site of tumour], MRI.[Disease outside primary], MRI.[Number of sites affected], MRI.[Which sites], MRI.Stage, MRI.[CSF result checked], Diagnosis.[Unusual features], Diagnosis.[Final diagnosis to use], Diagnosis.[Consencus diagnosis], Diagnosis.[Consencus grade], Diagnosis.[Diagnosis comments], Diagnosis.Episode, [MRS data].[Pre-treatment], [MRS data].[Date of MRS], [MRS data].[Time of MRS], [MRS data].[Type of MRS], [MRS data].[Voxel location], [MRS data].[Voxel location comments], [MRS data].[Quality of MRS_ICH WATER], [MRS data].[Quality of MRS_ICH WEDDY_tick if need to use WEDDY], [MRS data].[Quality of MRS comments], [MRS data].[Data Missing], [MRS data].[Data missing_comments], [MRS data].[Possibility of MRS on NBO]
    FROM ((Registration INNER JOIN Diagnosis ON Registration.[Hospital number_Hospital] = Diagnosis.[Hospital number_Hospital]) INNER JOIN MRI ON Registration.[Hospital number_Hospital] = MRI.[Hospital number_Hospital]) INNER JOIN [MRS data] ON Registration.[Hospital number_Hospital] = [MRS data].[Hospital number_Hospital]
    WHERE (((MRI.followup)=False) AND ((Diagnosis.[Final diagnosis to use])=True) AND ((Diagnosis.[Consencus diagnosis])="diffuse astrocytoma 9400/3" Or (Diagnosis.[Consencus diagnosis])="diffuse astrocytoma fibrillary 9420/3" Or (Diagnosis.[Consencus diagnosis])="diffuse astrocytoma gemistocytic 9411/3" Or (Diagnosis.[Consencus diagnosis])="dysembryoplastic neuroepithelial tumour 9413/0" Or (Diagnosis.[Consencus diagnosis])="ganglioglioma 9505/1" Or (Diagnosis.[Consencus diagnosis])="NF1 UBO" Or (Diagnosis.[Consencus diagnosis])="pilocytic astrocytoma 9421/1" Or (Diagnosis.[Consencus diagnosis])="pilomyxoid astrocytoma 9425/3" Or (Diagnosis.[Consencus diagnosis])="unbiopsied DNET" Or (Diagnosis.[Consencus diagnosis])="unbiopsied optic pathway glioma" Or (Diagnosis.[Consencus diagnosis])="unbiopsied tectal plate glioma" Or (Diagnosis.[Consencus diagnosis])="pilocytic astrocytoma metastatic at presentation" Or (Diagnosis.[Consencus diagnosis])="rosette forming glioneuronal tumour" Or (Diagnosis.[Consencus diagnosis])="unbiopsied low grade glioma") AND (([MRS data].[Pre-treatment])="Y") AND (([MRS data].[Type of MRS])="SVS short") AND (([MRS data].[Voxel location])="acceptable") AND (([MRS data].[Quality of MRS_ICH WATER])="acceptable") AND (([MRS data].[Data Missing])=False));

    [MRS data].[Date of MRS] is the field I need to choose earliest from but for EACH patient- patients have unique ID which is Registration.[Hospital number_Hospital] field.

    Thank you very very much.


  2. #2
    Join Date
    Dec 2007
    You might be better asking in an Access forum. Oracle's 'flavour' of SQL is not the same as MS Access's 'flavour'

Posting Permissions

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