Looping and comparing each record...Access VBA

[fasanay]

Hi Everybody,

I have an access table where the data is oraganized n the following way...

MRNUMBER BIDDERS BIDDERNUM

0001 Jimmy 1
0001 Goro 2
0001 Nexine 3
0001
0002 Kolo 4
0002 Molo 5
0002 Tolo 6
0002
0003 Bell 7
0003 Sprint 8
0003 Dunlop 9
0003
0003 Rogers 10

I want to organise it in the following way......


MRNUMBER BIDDER1 BIDDER2 BIDDER3

0001 Jimmy Goro Nexine
0002 Kolo Molo Tolo
0003 Bell Sprint Dunlop


I tried doing a crosstab on the Bidders Number but there are so many bidders
that it has gone 345 pages sideways.......
So I tried to do it in VBA so that it will go through each record compare the
MRNUMBER and if its the same write the corresponding bidder to the appropiate
Cell in a worksheet......currently I am doing this.....but it is not working any
help will be appreciated.

iRow = 0
cRow = 0

rst.movefirst
Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst.Fields("MR_NUMBER")
mrnewnumber = rst.Fields("MR_NUMBER")
Do Until mrnewnumber = rst.Fields("MR_NUMBER")
mrnewnumber = rst.Fields("MR_NUMBER")
objSht.Cells(cRow, 2).Value = rst.Fields("BIDDER")
cRow = cRow + 1
rst.MoveNext
Loop
rst.MoveNext
iRow = iRow + 1
Loop

 

[Guest]

You may want to try creating a calculated field to return the bidder rank for
a particular MR Number (using DCount() or a correlated subquery). Then use
that field as the header for your crosstab query.

For example:

MRBidderNo: DCount("*",[YourBidderTable],"[MRNUMBER] =" & [MRNUMBER] & " AND
[BIDDERNUM] <=" [BIDDERNUM])

HTH, Ted Allen

 

[Sirocco]

For starters, try reversing the row and column headers. Maybe that will
inspire your final solution.