WHERE clause help needed 2

[teach314]

hi - I have Table1 like this. The PK is composite (ID, Order). There is a new (increased) ID every 7 rows. Order always runs 1 to 7 in increasing order.

T_Input    Id      Order      DataVal
           23        1         354
           23        2         327
           23        3         325
           23        4         297
           23        5         211
           23        6         114
           23        7          26
           31        1         234
           31        2         214
           31        3         199
           31        4         183
           31        5         155
           31        6          52
           31        7           9
           37        1         474
           37        2         311
           et cetera..

I need to write a function ProcessData(n AS Integer) that will work with the 7 values of DataVal having the nth ID.

For example, ProcessData(2) would work with the 7 values of DataVal having the 2nd ID, namely 31. My function is already written except for this part... (WHERE ID = nth Distinct value of ID). any clues as to how to write this clause? I'd also need to know if there are no ID values left.

thanks in advance!

 

[PHV]

My function is already written
Well, which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[teach314]

hi PHV - I'm not sure how the function's code is necessary here. I just need to know, if I Call ProcessData(2), for example, how to word the WHERE clause so that it becomes WHERE ID = 31.

thank you for any assistance.

 

[Andrzejek]

Can you, insted of passing ProcessData(2), pass ProcessData([red]31[/red]) to your Function?

You can always so something like:
[tt]
Select Distinct ID
From Table1
Order By ID
[/tt]
And if you want ID = 31, that will be [red]2[/red]nd record in your case.

Have fun.

---- Andy

 

[teach314]

thanks Andy for responding. I ended up using code much like you suggested along with some VBA so that I could Call ProcessData(2) while passing 31 to the function as you'd suggested.

Still, I'm sure there must be an SQL-only solution that is beyond me. Here's the root of the problem. Given table 'fragment' Tbl_Input shown below, how can I generate the query output, also shown below. 'Order' starts at 1 and increases by 1 every time ID changes.

Tbl_Input                       Qry_Output  

Id   Other fields...            ID    Order          
23                              23      1                            
23                              23      1
23                              23      1
23                              23      1
23                              23      1
23                              23      1
23                              23      1
31                              31      2   
31                              31      2
31                              31      2
31                              31      2
31                              31      2
31                              31      2
31                              31      2
47                              47      3    
47                              47      3  
56                              56      4
99                              99      5
99                              99      5
99                              99      5
99                              99      5
99                              99      5
et cetera..

many thanks for any clues.

 

[PHV]

A starting point:

SELECT A.Id,Count(*) AS [Order]
FROM (
SELECT DISTINCT Id FROM Tbl_Input
) A INNER JOIN (
SELECT DISTINCT Id FROM Tbl_Input
) B ON A.Id>=B.Id
GROUP BY A.Id

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[teach314]

hey PHV... that works PERFECTLY! When I Left Join your query results to the original table, I get exactly what I needed to move on. thanks