Subquery Syntax 3

[ghobbitnz]

Hi

I've only just started to use and learn SQL and think its a great and powerful language. Unfortunately I'm trying to bite off a bit more than I can chew but its something I need to do.

I'm trying to run this query in access but I keep getting an error message and cant see why. The message is 'Invalid Use of '.', '!', () in the expression.

SELECT TEMPLATE.NHI, TEMPLATE.Surname, TEMPLATE.Dept, TEMPLATE.ID, TEMPLATE.[Final Diag]
FROM TEMPLATE
WHERE (((TEMPLATE.Dept)="Cytology") AND ((TEMPLATE.[Final Diag])="LG" OR "Normal"))
(SELECT TEMPLATE.NHI FROM TEMPLATE WHERE ((TEMPLATE.Dept)="Histology") AND ((TEMPLATE.[Final Diag])="HG"));

What I'd like to happen is for the query to find any records in the table where the dept is 'histology' and the final diagnosis is 'HG'. Once its found those, I'd then like it to find any records which have a matching NHI (there can be multiple records with the same NHI in the table) but this time where the Dept is 'Cytology' and the Final Diagnosis is "LG" or "Normal".
At the end I need to know that all the records in front of me are Cytology and either 'LG' or 'Normal' but that in the background (I dont neccessarily need to see them) the same patient had a 'HG' histolgy result.

Running one or other Select statement works fine but I cant seem to get them to play nicely together.

Would someone be able to help me and tell where I'm going wrong

thanks

Steve

 

[mp9]

Something like:

SELECT NHI, Surname, Dept, [ID], [Final Diag]
FROM TEMPLATE
WHERE Dept="Cytology" 
AND ([Final Diag]="LG" OR [Final Diag]="Normal")
AND NHI IN (SELECT NHI FROM TEMPLATE WHERE Dept="Histology" AND [Final Diag]="HG");

Untested

http://www22.brinkster.com/accessory/

 

[r937]

select NHI
     , Surname
     , Dept
     , ID
     , [Final Diag]
  from TEMPLATE as Cyt
 where Dept = 'Cytology'
   and [Final Diag] in ('LG','Normal')
   and exists
      ( select * 
          from TEMPLATE 
         where NHI = Cyt.NHI
           and Dept = 'Histology'
           and [Final Diag] = 'HG' )

r937.com | rudy.ca

 

[ghobbitnz]

Hi

Both of these options gave me no error messages but also no results. I had the fields NHI, Surname, ID [Final Diag] but there were no records when I know there should have been.

Any ideas?

many thanks for your assistance on this

Steve

 

[ghobbitnz]

My sincere apologies - I tried both of these examples again this time double checking that the table its perfroming the query did in deed have data that matches the criteria - always I helps I find.

Anyway both worked many many thanks

regards

steve

 

[PHV]

Yet another way:
SELECT DISTINCT C.NHI, C.Surname, C.Dept, C.ID, C.[Final Diag]
FROM TEMPLATE AS C INNER JOIN TEMPLATE AS H ON C.NHI = H.NHI
WHERE C.Dept = 'Cytology' AND H.Dept = 'Histology'
AND C.[Final Diag] IN ('LG','Normal') AND H.[Final Diag] = 'HG'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ghobbitnz]

Hi Sorry to trouble you again

Now that that is working I would like to add another level of complexity. The Histology results are reported on a certain date and I need to correlate those with the cytology as above but only cytology results which are no more than 6 months previous. So if the histology was reported 04/07/06, then I wouldnt see any Cytology results older 04/01/06. At the moment I see all results going back several years and we dont need to see these.

Is this syntax of what I add to the WHERE clause
EDIT: I did have some code I was working but I'm findin ghtese date functions rather tricky to the point I'm getting quite confused. Any help would be appreciated

regards

steve

 

[ghobbitnz]

Thanks PHV

That does indeed work as well. When you see it written out like that (and the other two options) it all seems straight forward. Obviously have a lot more reading to do

regards

steve

 

[PHV]

SELECT DISTINCT C.NHI, C.Surname, C.Dept, C.ID, C.[Final Diag]
FROM TEMPLATE AS C INNER JOIN TEMPLATE AS H ON C.NHI = H.NHI
WHERE C.Dept = 'Cytology' AND H.Dept = 'Histology'
AND C.[Final Diag] IN ('LG','Normal') AND H.[Final Diag] = 'HG'
AND C.[Date Field] >= DateAdd('m',-6,H.[DateField])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

i think i had my original main query and EXISTS subquery backwards

try this now --

select NHI
     , Surname
     , Dept
     , ID
     , [Final Diag]
  from TEMPLATE as [b]Hist[/b]
 where Dept = [b]'Histology'[/b]
   and [Final Diag] = [b]'HG'[/b]
   and exists
      ( select * 
          from TEMPLATE 
         where NHI = [b]Hist[/b].NHI
           and Dept = [b]'Cytology'[/b]
           and [Final Diag] [b]in ('LG','Normal')[/b]
           [COLOR=red]and results_date >=
                dateadd("m",-6,[b]Hist[/b].result_date)[/color] )

r937.com | rudy.ca

 

[ghobbitnz]

Hi

PHV - This appears to be working but I need to limit what Histology records it looks at. In my table are patient results going back a few years and whilst I need them to be in the table, the actual ones I'm interested in have only been reported in the last month or so

So can I do this

SELECT DISTINCT C.NHI, C.Surname, C.Dept, C.ID, C.[Final Diag]
FROM TEMPLATE AS C INNER JOIN TEMPLATE AS H ON C.NHI = H.NHI
WHERE C.Dept = 'Cytology' AND H.Dept = 'Histology'
AND C.[Final Diag] IN ('LG','Normal') AND H.[Final Diag] = 'HG'
AND H.Reported = DateAdd(March) <- not sure of the syntax here. Could I and would it be better to have a drop down box where the month Reported could be selected?
AND C.[Reported] >= DateAdd('m',-6,H.[Reported])

r937 - thanks for that. Your code worked better the first time but when I add the date function on the end it returned exactly the same number of records. I was wondering if it was because the results_date and Hist.result_date in my table are one and the same field (called Reported)?

Thanks for your help its much appreciated

steve

 

[r937]

yes, well, you didn't say what your date field was called, so i made it up!!

r937.com | rudy.ca

 

[PHV]

Like this ?
SELECT DISTINCT C.NHI, C.Surname, C.Dept, C.ID, C.[Final Diag]
FROM TEMPLATE AS C INNER JOIN TEMPLATE AS H ON C.NHI = H.NHI
WHERE C.Dept = 'Cytology' AND H.Dept = 'Histology'
AND C.[Final Diag] IN ('LG','Normal') AND H.[Final Diag] = 'HG'
AND H.Reported >= DateAdd('m',-4,Date())
AND C.Reported >= DateAdd('m',-6,H.Reported]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ghobbitnz]

Hi PHV

That looks the business thanks very much for your time

Hi r937

Thanks for your help. I appreciate the fact that I hadnt given you the date field - I realised that as soon as I saw your code and thought I needed to substitute in what the actual field was called. All I was saying was that on substituting the name of the field it didnt produce the records I was thinking it might. I was wondering if perhaps it was because maybe your code was thinking they were separate fields when in fact they were one and the same field. Thanks for your time

 

[ghobbitnz]

Hi

One last question and I promise I'll leave you alone

I've gone thorugh your code which works fine for the month of March and from your construct I have been able to see how it works by changing the syntax from -4 to -6 to get January, -5 to get february etc.

I'm going to be adding new records each month but I dont want to have to keep changing the SQL dateadd function - at the end of the day I wont actually be using this database -others will be. All they want to see are any discrepancies for the current year - might be less. So I was trying to have the expression say instead of the month of march or february, how about all records from todays date going back a year

I tried this but cant seem to get it to work - get a wrong number of arguements error

SELECT DISTINCT C.NHI, C.Surname, C.Dept, C.ID, C.[Final Diag]
FROM TEMPLATE AS C INNER JOIN TEMPLATE AS H ON C.NHI=H.NHI
WHERE C.Dept='Cytology' And H.Dept='Histology' And C.[Final Diag] In ('LG','Normal') And H.[Final Diag]='HG' And H.Reported Between (DateAdd(Date()) AND DateAdd('y',-1,Date())) And C.Reported>=DateAdd('m',-6,H.Reported);

regards

Steve

 

[PHV]

You may try this:
SELECT DISTINCT C.NHI, C.Surname, C.Dept, C.ID, C.[Final Diag]
FROM TEMPLATE AS C INNER JOIN TEMPLATE AS H ON C.NHI = H.NHI
WHERE C.Dept = 'Cytology' AND H.Dept = 'Histology'
AND C.[Final Diag] IN ('LG','Normal') AND H.[Final Diag] = 'HG'
AND H.Reported >= DateAdd([!]'yyyy',-1[/!],Date())
AND C.Reported >= DateAdd('m',-6,H.Reported]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ghobbitnz]

Hi

Works a treat - thanks for your all your help.
steve