Finding Numbers in a Range for Duplicate Check 1

[Elysynn]

Hello - I'm just looking for a bit of direction here. I have a table with two fields that looks as follows:

Node     | StationID
Node1    | 12345
Node1    | 12346 
Node1    | 12345-12350
Node1    | 12370
Node1    | 12350

What I need to do, is evaluate all the other stationid's that equal or fall between 12345-12350. So in this example, I would have a return of:

12345
12346
12350

Ranges are always in an increment of +1.

I have a query that breaks out the start and end of the ranges into two separate fields - I'm just not sure what to do with it now

SELECT tblQfinitiBackendExtParsed.Node, Left([Node],5) AS NodeGroup, Left([StationID],5) AS RangeStart, Right([StationID],5) AS RangeEnd
FROM tblQfinitiBackendExtParsed
GROUP BY tblQfinitiBackendExtParsed.Node, Left([Node],5), Left([StationID],5), Right([StationID],5), tblQfinitiBackendExtParsed.StationID
HAVING (((Left([Node],5))="Node1") AND ((Mid([StationID],6,1))="-"));

Any ideas you may have are greatly appreciated. I always learn something from you!

TIA,
Elysynn

 

[Golom]

Something like this?

Select Q.*
From 
(
SELECT Node, 
       Left([Node],5) AS NodeGroup, 
       Left([StationID],5) AS RangeStart, 
       Right([StationID],5) AS RangeEnd

FROM tblQfinitiBackendExtParsed 
WHERE Left([Node],5)='Node1' 
  AND Mid([StationID],6,1)="-" 
) As X
INNER JOIN tblQfinitiBackendExtParsed Q
           ON Q.Node = X.Node

WHERE Q.StationID BETWEEN X.RangeStart AND X.RangeEnd

 

[Elysynn]

Thanks, Golom. I think we were on the same track... this is what I came up with while working on this after I posted:

SELECT qryNode1DupExt_Range1.Node, tblQfinitiBackendExtParsed.StationID
FROM qryNode1DupExt_Range1 INNER JOIN tblQfinitiBackendExtParsed ON qryNode1DupExt_Range1.Node = tblQfinitiBackendExtParsed.Node
WHERE (((tblQfinitiBackendExtParsed.StationID) Between [RangeStart] And [RangeEnd]));

Both your example and the code I just posted produce the same result... The only records that are returned are the ones where there are ranges. For example:

Node1 12345-12350
Node1 23456-23470

Instead of:
Node1 12345
Node1 12346
Node1 12350

I was thinking of creating another table that has the node name, the range and then each number in the range to compare against:

Node     |Range        |StationID
Node1    |12345-12350  |12345
Node1    |12345-12350  |12346
Node1    |12345-12350  |12350

I'm just not sure how to get to this - or if it's even necessary.

Thanks again,
-Elysynn

 

[PHV]

and what about this ?
SELECT qryNode1DupExt_Range1.Node, tblQfinitiBackendExtParsed.StationID
FROM qryNode1DupExt_Range1, tblQfinitiBackendExtParsed
WHERE tblQfinitiBackendExtParsed.StationID Between RangeStart And RangeEnd;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Elysynn]

PHV - That did the trick. Didn't think to try it without the join.

Thanks!
Elysynn