Avatar of seamus9909
seamus9909 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
Microsoft Access

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
SOLUTION
Kent Dyer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
pdebaets

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

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 );
ASKER
seamus9909

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.
PortletPaul

>>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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck