hello experts I'm going to push

[teach314]

hello experts

I'm going to push my luck a bit and ask 2 different questions in one day!

thanks to help from this forum I produce tables like TblA below. My final problem is how to eliminate some of the ID's in TblA based on values found in TblB.

TblA                                    TblB
ID	TheVal	CountOfID             TheVal    CantFollow   
1	34	1                       ......     ......
1	21	2
1	19	3                       18           4
1	15	4                       18          11
1	12	5                       18          16  
1	8	6                       18          43 
2	77	1                       19           0
2	29	2                       19          14
2	18	3                       19          16
2	11	4                       19          31
2	10	5                       20           7
2	9	6                       20          19
5	99	1                       20          45
5	54	2                       20          54
5	45	3                       ......     ......
5	33	4
5	12	5
5	1	6

now, TblB shows 4 values that CAN'T IMMEDIATELY FOLLOW each value of TheVal. for example, TheVal = 18 in TblA cannot be immediately followed by TheVal = 4, 11, 16 or 43. But TheVal = 18 CAN be followed immediately by any other values, just not these four restricted ones. Because TblA has a violation where 11 immediately follows 18, then ALL of the ID=2 values must be rejected. As long as there are no other violations, the final query output would look like:

TblA
ID	TheVal	CountOfID
1	34	1
1	21	2
1	19	3
1	15	4 
1	12	5 
1	8	6
5	99	1
5	54	2
5	45	3
5	33	4
5	12	5
5	1	6

much thanks for any clues. I can actually do this in VBA, but I'm gradually realizing how powerful and efficient SQL can be.

TQ, Terry W.

 

[teach314]

hi gurus

Well, I think I've been able to work out a solution. It may be a bit ugly (3 layers of subqueries), but it seems to work well. I'd be very interested in seeing a more refined solution!

starting with TblA and TblB as in the original posting, the following code will show all the records from TblA except where (TblA.TheVal = TblB.TheVal), AND the IMMEDIATELY FOLLOWING TblA.TheVal = TblB.CantFollow). In such cases ALL instances of the offending TblA.ID are removed.

in the original example, TblA has TheVal = 18 followed by TheVal = 11. This is not allowed as per TblB. the 'offense' occurred when ID = 2, so ALL records having ID = 2 are removed from TblA.

SELECT TA.ID, TA.CountOfID, TA.TheVal
FROM TblA AS TA
WHERE TA.ID NOT IN 	
   (SELECT Q2.ID 	
    FROM  		
        (SELECT Q1.ID 		 
	FROM  			
	   (SELECT TA1.ID, TA1.CountOfID, TA1.TheVal, TA2.TheVal AS ValNext 		    FROM TblA AS TA1 INNER JOIN TblA AS TA2 
		ON (TA1.CountOfID = TA2.CountOfID-1) AND (TA1.ID = TA2.ID) 		    ORDER BY TA1.ID, TA2.CountOfID
	    ) AS Q1
	INNER JOIN TblB ON (Q1.TheVal = TblB.TheVal) AND (Q1.ValNext = TblB.CantFollow)
	) AS Q2
    )
ORDER BY TA.ID, TA.CountOfID;

If there is a nicer way to do this, I'd be very interested in seeing it!