How do I shorten this query?

[mdgibbs88]

Getting error that says the ORs part is too long for the query builder. This is the only way I know how to do it. Can someone give me an idea?

SELECT DISTINCT dbo_SJMPatientNames.PatLastName, dbo_SJMPatientNames.PatFirstName, Step4CombineBillingsAndPayments.Histno, Step4CombineBillingsAndPayments.Patcom, Step4CombineBillingsAndPayments.Payor, dbo_lkHSCRCPAYOR.PaymentSource, Step4CombineBillingsAndPayments.FromDate, Step4CombineBillingsAndPayments.ThruDate, Step4CombineBillingsAndPayments.ServDate, Step4CombineBillingsAndPayments.RPC, Step4CombineBillingsAndPayments.RPCDescription, Step4CombineBillingsAndPayments.BillCharge, Step4CombineBillingsAndPayments.BillUnits, Step4CombineBillingsAndPayments.Totchg, Step4CombineBillingsAndPayments.Payment, Step4CombineBillingsAndPayments.Balance, dbo_lkSJMINSURER.SJMPHSINS INTO [Step5FinalReport?]
FROM ((Step4CombineBillingsAndPayments INNER JOIN dbo_SJMPatientNames ON Step4CombineBillingsAndPayments.Histno = dbo_SJMPatientNames.HISTNO) INNER JOIN dbo_lkHSCRCPAYOR ON Step4CombineBillingsAndPayments.Payor = dbo_lkHSCRCPAYOR.HSCRCPAYOR) INNER JOIN dbo_lkSJMINSURER ON dbo_lkHSCRCPAYOR.HSCRCPAYOR = dbo_lkSJMINSURER.HSCRCPayor
WHERE (((dbo_lkSJMINSURER.SJMPHSINS)="M11M" Or (dbo_lkSJMINSURER.SJMPHSINS)="M94T" Or (dbo_lkSJMINSURER.SJMPHSINS)="M20H" Or (dbo_lkSJMINSURER.SJMPHSINS)="M22H" Or (dbo_lkSJMINSURER.SJMPHSINS)="M19H" Or (dbo_lkSJMINSURER.SJMPHSINS)="M14H" Or (dbo_lkSJMINSURER.SJMPHSINS)="M13H" Or (dbo_lkSJMINSURER.SJMPHSINS)="M53T" Or (dbo_lkSJMINSURER.SJMPHSINS)="M54T" Or (dbo_lkSJMINSURER.SJMPHSINS)=“M66M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M67M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M16M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M17M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M18M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M24M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M25M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M26M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M27M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M28M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M29M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M31M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M32M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M33M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M34M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M35M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M36M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M50T“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M51T“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M52T“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M64M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M65M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M03H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M26H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M18H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M23H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=”M24H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M25H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M27H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M28H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A11H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“E25H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“V06H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“MTHM“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“MC11“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“MC12“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“MC13“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“MC14“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“MC15“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“MC16“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“MC21“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“MC22“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M101“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M100“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U56H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A23H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A10H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“C06H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“E04H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U55H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“K14H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“N01H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“T10H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U36H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U34H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U35H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U40H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U39H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U37H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U17C“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A03M“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U03H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U20H” Or (dbo_lkSJMINSURER.SJMPHSINS)=“A30H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“H25H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“G60H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“AFMA“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“TOAP“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“G85H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A07H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A09H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A04H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A06H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A08H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“H24H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“J10H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M68H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“P67H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“P64H” Or (dbo_lkSJMINSURER.SJMPHSINS)=“P22H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“P27H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“P66H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“P65H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“P26H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“P25H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“P17H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“D14M” Or (dbo_lkSJMINSURER.SJMPHSINS)=“U06H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U10H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“H01H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“C35H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M11H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“D13H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“M01P“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“P26P“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A07P“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“J10P“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U08P“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“A11H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“V06P“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“C14G“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“T34C“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“T04C“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“T33H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“T35H“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“T35C“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“T05C“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“T03C“ Or (dbo_lkSJMINSURER.SJMPHSINS)=“U26H”));

 

[pbaldy]

Try

WHERE dbo_lkSJMINSURER.SJMPHSINS IN("T03C", "U26H", ...)

 

[mdgibbs88]

Thanks Paul,
When I try that I get a Type Mismatch error and I know that I have a 4 character text field in the database.
I tried to shorten to just 2 instead of the 55 that I was looking for in query builder and the syntax looked like this:
WHERE (((dbo_lkSJMINSURER.SJMPHSINS)="M11M" Or (dbo_lkSJMINSURER.SJMPHSINS)="M94T"

and it ran ok. I was hoping for an IN statement as well, but I just can't make it work. Do you know of a way that I can put the OR selections that I need into a table and compare the table against the field in the other table?

 

[pbaldy]

You shouldn't get a mismatch. You took out the ellipsis, didn't you? For two it would look like:

WHERE dbo_lkSJMINSURER.SJMPHSINS IN("T03C", "U26H")

Another method would be to have all those values as records in a table, and join against that table.

 

[mdgibbs88]

I think I am going to try the Table idea. This is an odd one for sure. Thanks again! I will let you know how it goes.

 

[arichins]

Just highlight half of it and hit delete. Just kidding!

In your from clause, you can create aliases for the table, so that the whole thing doesn't need to be in there. For example.

Select S4CBP.Patcom
FROM Step4CombineBillingsAndPayments S4CBP

Paste parts of your query into notepad and do a find/replace to quickly update it. That will shed a tremendous amount of characters off your query.