Query Malfunctioning

[AccessPower]

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:

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.

[rpeare]

Have you got an example database with some garbage data in it that you can simulate the problem?

[aytee111]

This is a very convoluted query. I don't know why you need a subquery, the criteria can all be done in the one query. Go back to basics, get each criteria working one at a time, then add any additional information.

[AccessPower]

This is a very convoluted query. I don't know why you need a subquery, the criteria can all be done in the one query. Go back to basics, get each criteria working one at a time, then add any additional information.

What would you suggest? I can't seem to think of a simpler way to do this.

[AccessPower]

I haven't tested this, but what do you guys think:

SELECT TOP 4 tblLocal.Numbers FROM tblLocal LEFT JOIN qryMPCSwitches5 ON tblLocal.Numbers = qryMPCSwitches5.LOC_NUM 
WHERE 
(SUM(Right([Numbers],Len([Numbers])-1)) / Right([Numbers],Len([Numbers])-1) = 4)
 AND 
((IIf(Right([Numbers],1)=5,1,0) + IIf(Right([Numbers],1)=6,1,0) + IIf(Right([Numbers],1)=7,1,0) + IIf(Right([Numbers],1)=9,1,0))=4);

[aytee111]

Test it!

You specified 3 criteria in your first post. Do each one in a separate query and get it working, then you can put it all together in one query.

[AccessPower]

I am trying to get #1 going, but with no luck.

#2 and #3 are working just fine.

[aytee111]

The query field will be Left(fieldname,Len(fieldname)-1)
The criteria under that field will be the same using the field from the other table with a <>

[AccessPower]

The query field will be Left(fieldname,Len(fieldname)-1)
The criteria under that field will be the same using the field from the other table with a <>

select Left(Numbers,Len(Numbers)-1), count(Left(Numbers,Len(Numbers)-1)) from tblPNumbers
group by Left(Numbers,Len(Numbers)-1)

Here's what I have for #1. It's working now.

[CJ_London]

use a query to get this list of potential prefixes (called qry1)

SELECT Left(Numbers,Len(Numbers)-1) AS Prefix, Count(*) FROM tblLocal WHERE val(Right(numbers,1)) IN (5,6,7,9) GROUP BY Left(Numbers,Len(Numbers)-1) HAVING Count(*)=4

I'm assuming that the last number is not repeated within any prefix group


then join it to your local table call this qry2

SELECT *
FROM tblLocal INNER JOIN qry1 ON Left(tblLocal.Numbers,Len(tblLocal.Numbers)-1)=qry1.prefix

to give you all numbers where there are 4 same prefix

finally left join to your qryMPCSwitches5 and eexclude those that exist in that query

SELECT TOP 4 *
FROM qry2 LEFT JOIN qryMPCSwitches5 ON qry2.number=qryMPCSwitches5.LOC_NUM
WHERE qryMPCSwitches5.LOC_NUM is null

If you want to make it all one query

SELECT TOP 4 *
FROM (SELECT * FROM tblLocal INNER JOIN (SELECT Left(Numbers,Len(Numbers)-1) AS Prefix, Count(*) FROM tblLocal WHERE val(Right(numbers,1)) IN (5,6,7,9) GROUP BY Left(Numbers,Len(Numbers)-1) HAVING Count(*)=4) AS qry1 ON Left(tblLocal.Numbers,Len(tblLocal.Numbers)-1)=qry1.prefix) AS qry2 LEFT JOIN qryMPCSwitches5 ON qry2.number=qryMPCSwitches5.LOC_NUM
WHERE qryMPCSwitches5.LOC_NUM is null
ORDER BY numbers

[AccessPower]

I can get #1 and #2 working, but I can't grab the actual Numbers column or the whole thing won't return any values due to the grouping.

Suggestions?

SELECT Left([Numbers],Len([Numbers])-1) AS [Match]FROM tblPNumbers
WHERE (((Right([tblPNumbers].[Numbers],1))=5)) OR (((Right([Numbers],1))=6)) OR (((Right([Numbers],1))=7)) OR (((Right([Numbers],1))=9))
GROUP BY Left([Numbers],Len([Numbers])-1)
HAVING (((Count(Left([Numbers],Len([Numbers])-1)))>3));

[AccessPower]

use a query to get this list of potential prefixes (called qry1)

SELECT Left(Numbers,Len(Numbers)-1) AS Prefix, Count(*) FROM tblLocal WHERE val(Right(numbers,1)) IN (5,6,7,9) GROUP BY Left(Numbers,Len(Numbers)-1) HAVING Count(*)=4

I'm assuming that the last number is not repeated within any prefix group


then join it to your local table call this qry2

SELECT *
FROM tblLocal INNER JOIN qry1 ON Left(tblLocal.Numbers,Len(tblLocal.Numbers)-1)=qry1.prefix

to give you all numbers where there are 4 same prefix

finally left join to your qryMPCSwitches5 and eexclude those that exist in that query

SELECT TOP 4 *
FROM qry2 LEFT JOIN qryMPCSwitches5 ON qry2.number=qryMPCSwitches5.LOC_NUM
WHERE qryMPCSwitches5.LOC_NUM is null

If you want to make it all one query

This seems to be working. I'll do some testing. I appreciate the breakdown. I see what you did differently there as well.

[AccessPower]

use a query to get this list of potential prefixes (called qry1)

SELECT Left(Numbers,Len(Numbers)-1) AS Prefix, Count(*) FROM tblLocal WHERE val(Right(numbers,1)) IN (5,6,7,9) GROUP BY Left(Numbers,Len(Numbers)-1) HAVING Count(*)=4

I'm assuming that the last number is not repeated within any prefix group


then join it to your local table call this qry2

SELECT *
FROM tblLocal INNER JOIN qry1 ON Left(tblLocal.Numbers,Len(tblLocal.Numbers)-1)=qry1.prefix

to give you all numbers where there are 4 same prefix

finally left join to your qryMPCSwitches5 and eexclude those that exist in that query

SELECT TOP 4 *
FROM qry2 LEFT JOIN qryMPCSwitches5 ON qry2.number=qryMPCSwitches5.LOC_NUM
WHERE qryMPCSwitches5.LOC_NUM is null

If you want to make it all one query

Unfortunately, I am still running into the same issue. The issue is that the query is pulling a sequence of four numbers, and as long as they end in 5,6,7, OR 9 then it meets the criteria. So bad query results like H1885, H1886, H1895, H1896 are possible. In that sequence, if there is no H1887 and H1889, and the next two in the sequence end in any of the allowed numbers, they get pulled. This can't happen.

So how do we perform a "all characters but the last need to be the same" check?

[CJ_London]

if you check my post (#10) you will see I modified the last query - see if that resolves the problem

[AccessPower]

I'll check tomorrow morning. Hopefully that will resolve the issue. The reason this is coming up is that the numbers are not sequential... in other words, there will be instances where the query should fail, but it isn't failing. If the query fails, it has to move onto the next sequence automatically. It isn't that the numbers aren't in order.

Even when put in order, there may be an H15, H16 (H17 and H19 are not in the list at all) and the very next two in the list are H25, and H26. So naturally the query thinks... that is the first four numbers in a row that end in one of the four allowable numbers, but the problem is: all of the other characters aside from the last are not the same.