Query Help Filtering Multiple Records

  • Thread starter Thread starter BMC
  • Start date Start date
B

BMC

Hi

I have a query that lists records from three tables.

The query lists all patient visits to a doctor’s office.

Each record has a diagnosis code (i.e. D001, D002).

What I need to do is:-

If a Patient has a particular diagnosis code carried out (i.e. D001) then
none of those patients’ records appear in the list.

Any help would be appreciate.

Regards
Barry
 
BMC said:
I have a query that lists records from three tables.

The query lists all patient visits to a doctor’s office.

Each record has a diagnosis code (i.e. D001, D002).

What I need to do is:-

If a Patient has a particular diagnosis code carried out (i.e. D001) then
none of those patients’ records appear in the list.
Click to expand...


Try this kind of where condition

Not Exists(SELECT patientID FROM sometable
WHERE diagcode = "D001")
 
Hi Marshall

Would you please look at this SQL and advise where I should make changes.

You help would be much appreciated.

If a patient has had Code "Q133A" ([Bill Items].Code) carried out I then
need to remove all records for that Patient (Bills.[Patient ID]).

SQL code:-

SELECT Demographic.[File #], Demographic.[Name Last], Demographic.[Name
First], Demographic.[Name Initial], Demographic.[Search Key],
Demographic.Sex, Demographic.[Birth Date], Demographic.[Home Phone],
Demographic.[Home Address], Demographic.[Home Address (c)], Demographic.[Home
Country], Demographic.[Home Region], Demographic.[Home City],
Demographic.[Home Postal], Bills.[Patient ID], Bills.[Bill Date],
Bills.[Entry Date] AS [Bills_Entry Date], Bills.[Invoicing Date], Bills.[Last
Invoicing Date], [Bill Items].Code, [Bill Items].[Code Description]
FROM (Demographic INNER JOIN Bills ON Demographic.[System ID] =
Bills.[Patient ID]) INNER JOIN [Bill Items] ON Bills.[Invoice #] = [Bill
Items].[Invoice #]
WHERE (((Demographic.[Search Key]) Like "R*"))
ORDER BY Demographic.[Name Last], Demographic.[Name First];


Regards
Barry
 
You never did say which table contains the diagcode field of
how it is related to the tables in your query's FROM clause.

The general idea could be like:

WHERE Demographic.[Search Key] Like "R*"
And Not Exists(SELECT patientID FROM sometable
WHERE table.PatientID = Demographic.[System ID]
And diagcode = "Q133A")
--
Marsh
MVP [MS Access]

Would you please look at this SQL and advise where I should make changes.

You help would be much appreciated.

If a patient has had Code "Q133A" ([Bill Items].Code) carried out I then
need to remove all records for that Patient (Bills.[Patient ID]).

SQL code:-

SELECT Demographic.[File #], Demographic.[Name Last], Demographic.[Name
First], Demographic.[Name Initial], Demographic.[Search Key],
Demographic.Sex, Demographic.[Birth Date], Demographic.[Home Phone],
Demographic.[Home Address], Demographic.[Home Address (c)], Demographic.[Home
Country], Demographic.[Home Region], Demographic.[Home City],
Demographic.[Home Postal], Bills.[Patient ID], Bills.[Bill Date],
Bills.[Entry Date] AS [Bills_Entry Date], Bills.[Invoicing Date], Bills.[Last
Invoicing Date], [Bill Items].Code, [Bill Items].[Code Description]
FROM (Demographic INNER JOIN Bills ON Demographic.[System ID] =
Bills.[Patient ID]) INNER JOIN [Bill Items] ON Bills.[Invoice #] = [Bill
Items].[Invoice #]
WHERE (((Demographic.[Search Key]) Like "R*"))
ORDER BY Demographic.[Name Last], Demographic.[Name First];


Marshall Barton said:
Try this kind of where condition

Not Exists(SELECT patientID FROM sometable
WHERE diagcode = "D001")
Click to expand...
Click to expand...
 
Back
Top