Hello Everyone,
Background:
I have an interesting query that grabs a series of numbers that are in 1 table (a list of supposed available local numbers) and compares them to a master list that is in a sql server database.
Here is the query:
Code:
SELECT TOP 4 tblLocal.Numbers FROM tblLocal LEFT JOIN qryMPCSwitches5 ON tblLocal.Numbers = qryMPCSwitches5.LOC_NUM WHERE (((Left([Numbers],Len([Numbers])-1)) In (SELECT Left([Numbers],Len([Numbers])-1) AS Prefix FROM tblLocal GROUP BY Left([Numbers],Len([Numbers])-1) HAVING (((Sum(IIf(Right([Numbers],1)=5,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=6,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=7,1,0)))>0) AND ((Sum(IIf(Right([Numbers],1)=9,1,0)))>0)))) AND ((Right([Numbers],1)) In (5,6,7,9)));
The code above searches for the first four numbers that meet the following criteria:
1) The numbers contain the same characters except for the last character (e.g. F28835, F28836, F28837, F28839).
2) The numbers end in the sequence 5, 6, 7, 9 (e.g. See example for #1).
3) The numbers are found in tblLocal but not in qryMPCSwitches5.
The problem:
There have been a few instances where the query has neither met requirements 1 nor 2.
Specific instance last week:
G0889
G0895
G0897
G0899
I don't really see why this would happen. Someone here is bound to have a better eye than I have.
Thank you in advance.