Query Criteria

  • Thread starter Thread starter red skelton via AccessMonster.com
  • Start date Start date
R

red skelton via AccessMonster.com

Hi, was wondering if someone could take a look at my SQL statement and tell
me why my query isn't working. I thought this query would be straight
forward, but it doesn't work. What I'm trying to do is run the query so that
the invoices from Bourne Hall Clinic, NewCampe Medical LTD and Cox
Orthopedics are filtered out of the query results. Here is my SQL statement:

SELECT DISTINCTROW [Patient Info].[LAST NAME], [Patient Info].[FIRST NAME],
Invoices.[APPT DATE], [Patient Info].DOB, Invoices.FACILITY, Invoices.
[CONSULTANT NAME], Invoices.[RESULTS REC'D], Authorization.COMMENTS, Invoices.
[INVOICE COMMENT]
FROM [Patient Info] LEFT JOIN ([Authorization] LEFT JOIN Invoices ON
Authorization.[AUTHORIZATION NUMBER] = Invoices.[AUTHORIZATION NUMBER]) ON
[Patient Info].[PT ID] = Authorization.[PT ID]
WHERE (((Invoices.FACILITY)<>"BOURNE HALL CLINIC" Or (Invoices.FACILITY)
<>"NEWCAMPE MEDICAL LTD" Or (Invoices.FACILITY)<>"COX ORTHOPEDICS"));

Any help would be greatly appreciated.

VR,
Red
 
I assume that you get all the records so the where statement doesn't work.
You need to change the Or to And

SELECT DISTINCTROW [Patient Info].[LAST NAME], [Patient Info].[FIRST NAME],
Invoices.[APPT DATE], [Patient Info].DOB, Invoices.FACILITY, Invoices.
[CONSULTANT NAME], Invoices.[RESULTS REC'D], Authorization.COMMENTS,
Invoices.
[INVOICE COMMENT]
FROM [Patient Info] LEFT JOIN ([Authorization] LEFT JOIN Invoices ON
Authorization.[AUTHORIZATION NUMBER] = Invoices.[AUTHORIZATION NUMBER]) ON
[Patient Info].[PT ID] = Authorization.[PT ID]
WHERE (((Invoices.FACILITY)<>"BOURNE HALL CLINIC" And (Invoices.FACILITY)
<>"NEWCAMPE MEDICAL LTD" And (Invoices.FACILITY)<>"COX ORTHOPEDICS"));
 
Ofer,
Many thanks it worked as advertised. It is amazing how changing one word
works.
Thanks again,
Red
I assume that you get all the records so the where statement doesn't work.
You need to change the Or to And

SELECT DISTINCTROW [Patient Info].[LAST NAME], [Patient Info].[FIRST NAME],
Invoices.[APPT DATE], [Patient Info].DOB, Invoices.FACILITY, Invoices.
[CONSULTANT NAME], Invoices.[RESULTS REC'D], Authorization.COMMENTS,
Invoices.
[INVOICE COMMENT]
FROM [Patient Info] LEFT JOIN ([Authorization] LEFT JOIN Invoices ON
Authorization.[AUTHORIZATION NUMBER] = Invoices.[AUTHORIZATION NUMBER]) ON
[Patient Info].[PT ID] = Authorization.[PT ID]
WHERE (((Invoices.FACILITY)<>"BOURNE HALL CLINIC" And (Invoices.FACILITY)
Hi, was wondering if someone could take a look at my SQL statement and tell
me why my query isn't working. I thought this query would be straight
Click to expand...
[quoted text clipped - 16 lines]
VR,
Red
Click to expand...
Click to expand...
 
Back
Top