generating first records

[cdsmumbaii]

Hi I am new to programming. Iam using access 2003 . My table is something like this :

sr n1 positions

1 12 1
1 23 2
1 35 3
2 16 1
2 19 2
2 25 3
3 8 1
3 17 2
3 20 3

I want to create programme to display first two position of each sr and my result would be :

sr n1 position
1 12 1
1 23 2
2 16 1
2 19 2
3 8 1
3 17 2

to achieve the above result, I started with the following progrem. Please help to move forward

Sub CLIFF()

Dim db As dao.Database
Dim rs As dao.Recordset
Dim varprev As Integer
Dim varnext As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from numbers11")
i = 0
rs.MoveFirst
varprev = rs!sr
varnext = rs!sr

For i = 1 To 2
Debug.Print rs!sr, rs!n1, i
rs.MoveNext
Next i

End Sub

it gives me the following result

sr n1 i
1 12 1
1 23 2

Now I want help to generate the following from 2 recordset and so on i.e., two records from each sr
2 16 1
2 19 2
3 8 1
3 17 2


please guide me

thanks

 

[plog]

Is there a reason you are trying to accomplish this programmatically instead of with a query? This SQL will produce what you want from what I've gleaned:

SELECT * 
FROM numbers11
WHERE position<3
ORDER BY sr, position;

 

[cdsmumbaii]

Hi thanks for reply. Actually I want to generate a recordset containing 6 numbers, with a condition that new record should have more than 2 records from each sr and not more than 3 n1 should repeat in new record. In that case my result will be something like this
nsr oldsr n1 posi
101 1 12 1
101 1 23 2
101 2 16 1
101 2 19 2
101 3 8 1
101 3 17 2
102 1 23 2
102 1 35 3
102 2 19 2
102 2 25 2
102 3 8 1
102 3 20 3

 

[VilaRestal]

Is this going to go into a table?

If so then you're not trying to "generate a recordset", you're trying to append to that table with these values. You may well need to use recordsets to do that, but generating a recordset is not what you're trying to achieve.

And even if you are actually trying to create a recordset in memory with these values in it I think the simplest way might be to create a temporary table and then open that as recordset. In which case the last step is trivial, and we're back to filling a table with records.

The other alternative is to create an array instead of a recordset. All the fields are numbers after all.

 

[cdsmumbaii]

hI thanks for reply. How do I create array out of it. I have no nothing about arrays. Please guide me.

 

[VilaRestal]

If you're going to be putting all this into a table then you don't need an array (or to create a single recordset containing them all). You will run a series of insert sql commands in a loop.

Is that what you're trying to do?

 

[cdsmumbaii]

Hi, thanks for your reply.How do I create series of loop to ensure that not more than 2 n1 from each sr in every new nsr .


thanks in advance