Link to home
Start Free TrialLog in
Avatar of Michael Dean
Michael DeanFlag for United States of America

asked on

Query not filering correctly

I have a query with the following SQL that is not filtering out records that don't have the company name of *USAA Insurance Company"  I have attached the results of the query and as you can see there are records that have a value other than "USAA Insurance Company" being returned.

SELECT DISTINCTROW tblClaims.ServiceID, Customers.Company, tblClaims.StatusID, tblClaims.InvoiceID, tblClaims.CurrentYear, tblClaims.FileNo, tblClaims.Patient, tblClaims.Fname, tblClaims.Lname, tblClaims.PatientPhone, tblClaims.PatientCell, tblClaims.PatientEmail, tblClaims.DOI, tblClaims.[Claim#], tblClaims.[Date/Rec'd], tblClaims.[Date of IME], tblClaims.[$Billed], tblClaims.[$Paid Doctor (cost)], tblClaims.[$Rec'd (income)], tblClaims.[Date Check Deposited], tblClaims.ServiceID, tblClaims.Specialty, tblClaims.State, tblClaims.Reviewer, tblClaims.[Gross Profit], tblClaims.Comments, tblClaims.[Complete Date], tblClaims.EmployeeID, tblClaims.Cases, tblClaims.Description, tblClaims.Calls, tblClaims.DateIMESent, tblClaims.PrePaid, tblClaims.AmountofPrepaid, tblClaims.InvoiceSent, tblClaims.InvoiceSentDate, tblClaims.PaidDoctor, tblClaims.AmountPaidDoc, tblClaims.AdditionAmountDoc, tblClaims.InvoiceReceived, tblClaims.InvoiceRecDate, tblClaims.DateofLoss, tblClaims.Lawyer, tblClaims.DateofService, tblClaims.DoctorID, tblClaims.DateofBirth, tblClaims.TimeofIME, tblClaims.PatientAddress, tblClaims.PatientAddress2, tblClaims.PatientCity, tblClaims.PatientState, tblClaims.PatientZip, tblClaims.ACT6, tblClaims.LegacyInvoicenumber, tblClaims.AddRecordsReceived, tblClaims.DateAdditionalRecordsRcvd, tblClaims.RequesttoSpeaktoReviwer, tblClaims.Insured, tblClaims.InsuranceAgency, tblClaims.ReconRequesttoSpeak, tblClaims.ReconRequest, tblClaims.ReconRequestDate, tblClaims.ReconAdditionalRecords, tblClaims.[AIS-Contact], tblClaims.[Date Rec'd (income)], tblClaims.ACT6InitReview, tblClaims.Act6ReconReview, tblClaims.[Client - Contact]
FROM Customers INNER JOIN tblClaims ON Customers.ID = tblClaims.[Client - Contact]
WHERE (((Customers.Company) Like "USAA INSURANCE COMPANY") AND ((tblClaims.StatusID)=1)) OR (((tblClaims.StatusID)=2)) OR (((tblClaims.StatusID)=5));
qryUSAACLaims.xlsx
SOLUTION
Avatar of Kent Dyer
Kent Dyer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
use of LIKE would normally be associated with a  wildcard e.g.

LIKE "USAA INSURANCE COMPANY%"

and you would do that IF there were companies such as
USAA INSURANCE COMPANY PTY. LTD.
USAA INSURANCE COMPANY LTD.
USAA INSURANCE COMPANY INC.

as that does not appear to be needed then you should use equal as proposed by kdyer
it is also more efficient to use equal (i.e. should be faster)

then as kdyer has also mentioned the other records most probably get included due to the "OR" conditions, so here I'd follow the suggestion by pdebaets

WHERE Customers.Company = "USAA INSURANCE COMPANY"
AND ( tblClaims.StatusID=1 OR tblClaims.StatusID=2 OR tblClaims.StatusID=5 );

note that the parentheses here are vital so that the 3 values of StatusID are treated as one condition.

I'm not an Access user these days so I'm not sure if IN() does work, but if it does, an alternative syntax would be:

WHERE Customers.Company = "USAA INSURANCE COMPANY"
AND tblClaims.StatusID IN (1, 2 ,5 );
Avatar of Michael Dean

ASKER

What I noticed is if I removed the values from the status ID   (1,2,5) then I only get USAA. However when I use the and 1 or 2 or 5 I get more than just USAA.
>>However when I use the and 1 or 2 or 5 I get more than just USAA.
that would be due to the way the OR's are placed within the many parentheses

take the last 3 lines of the question and paste into into http://poorsql.com/

What you see is this:
FROM Customers
INNER JOIN tblClaims ON Customers.ID = tblClaims.[Client - Contact]
WHERE (
		((Customers.Company) LIKE "USAA INSURANCE COMPANY")
		AND ((tblClaims.StatusID) = 1)
		)
	OR (((tblClaims.StatusID) = 2))
	OR (((tblClaims.StatusID) = 5));

Open in new window

What happens here is that only the combination of "USAA..." and the statusID=1 are being considered as a unit; then any records with statusID of either 2 or 5 will be included. What it needs to look like is this:
FROM Customers
INNER JOIN tblClaims ON Customers.ID = tblClaims.[Client - Contact]
WHERE Customers.Company LIKE "USAA INSURANCE COMPANY"
	AND (
		tblClaims.StatusID = 1
		OR tblClaims.StatusID = 2
		OR tblClaims.StatusID = 5
		);

Open in new window

I think it will help if you reduce the frequency of using parentheses.

e.g. this is unecessary: (tblClaims.StatusID)
and so is this: ((tblClaims.StatusID) = 2)

If you work with fewer parentheses then desired grouping of conditions becomes a little easier.