Find a nth item in a list.

[Shamous]

How would I....

I have a query which produces a list like so:

REP Customer Date sold
--------------------------------
John S Ajax 1/1/05
John S Comet 1/5/05
John S Hotel 1/17/05
John s Miami 1/29/05

.....

John s San jose 3/14/05
John s Morgan Hill 4/1/05



Now I would I build a query to show for John S.


Agent 1st Account 5th Account 10th Account 20th
----------------------------------------------------------
John s 1/1/05 1/29/05 2/14/05 3/14/05


How do I do this?

Thanks

Rusty

 

[rssql]

create an array.
Dim Myarray(100) as variant
Dim x as integer
x = 1

create a recordset
use ado to open the query...
Myarray(1) = "John"
Do
if REP = "John" then
x = x + 1
Myarray(x) = DateSold
endif
movenext 'move to next record
loop until EOF

set recordset = nothing

Now you have all of that data in Myarray().

you just loop thru it and set RecordSource of your controls.
If your app is not compiled, you can use code to create new controls on your report/form.
If your report is compiled, you will have to make sure all controls are already created. You might want to have more than one identical report...depends what your end goal is.

this is kind of quick and dirty......

 

[Shamous]

I'll try it. Thanks