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
seamus9909Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
First thing you need to do is to use a SQL Pretty Print - I like the following: http://www.dpriver.com/pp/sqlformat.htm

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

Open in new window


Since your SQL statement is pretty extensive, we will just focus on the WHERE clause.  You need to be somewhat careful with the use of LIKE..

Like is good if you want to find portions of phrases.  But, I don't think that is your issue here.

I would initially remove the statusid = 1, = 2, = 5

Start here - I have removed LIKE and have change to "="..
WHERE  ( customers.company = "USAA INSURANCE COMPANY" ) ; 

Open in new window


Then, I would add them back in one at a time..  Looking at the AND and OR usage very carefully..

Think of AND as like a chain..  It has to have the words walt and joe as customer names for example in the statement - which would result in one record.  If you use the word OR: Walt OR Joe - it will return two rows.  One for Walt and one for Joe.

Hopefully, this simplifies things a bit/

HTH,

Kent
0
pdebaetsCommented:
Try this (assuming you want all USAA records that have StatusID equal to 1, 2 or 5):

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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 );
0
seamus9909Author Commented:
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.
0
PortletPaulfreelancerCommented:
>>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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.