Looping and comparing each record...

[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]

I can see a few problems. I'll go on the assumption that you have your data
sorted by MR_Number and possibly bider or biddernum:

Your basic loop (inner) for getting the data is
Do Until mrnewnumber = rst.Fields("MR_NUMBER")

It looks like it should be While mrnewnumber = rst.Fields("MR_NUMBER")
because you want all the bidders for one number on the same row.

You also need to iterate the column, not the row in that inner loop so that
each bider would get entered in objSht.Cells(cRow, cCol).Value =
rst.Fields("BIDDER").

You only need to MoveNext in the inner loop as once you change the Until to
While as noted above, when you move to the next MR_Number, you'll go back to
the outer loop. On the same basis, you don't need to assign your "hold" on
the MR_Number in the inner loop as doing it in the outer loop will happen
when it changes.

When you hit the outer loop, remember to increment the row counter and reset
the column counter to 2.

Hope that the above helps.

 

[Guest]

So, how does one do this w/o using SQL? How would you do this? Would you
create a crosstab query? How would you limit the fields to give you say only
10 of the values w/the row title being bid 1, bid 2, bid 3, etc. with the
bids in the data field below? Thanks! :O)

 

[Guest]

How would someone do this when they aren't VB savvy? Basic access users. If
in a crosstab query, how do you limit the fields.

Thanks!