Create query to sort data and autoincrement associated column

[mael.iosa@gmail.com]

Hi, I'm new to this group and fairly new to Access. I have a bunch of
data, and after several other queries, I generate the following query
which has two fields:

Bin, Time
20 3.5
20 3.9
20 4.6
40 2.4
40 2.7
40 3.4
40 5.6

except the real query has about 120 rows, with the first field
(probability bin) taking the values of {20,40,60,80,10 0} and the second
field (time) taking any real positive number.

For each bin (20 and 40 in the above sample) I want to generate a new
set of data. This data set should consist of the second field (time)
sorted in ascending order, and then an additional field which
increments from 0 to the number of the bin. For example, using the
above sample, I would get the following data:

(For bin=20)
3.5 0
3.9 10
4.6 20

(for bin=40)
2.4 0
2.7 13.3
3.4 26.6
5.6 40


I've had a lot of trouble thinking of some way to do this. Of course
the first step can be to create an intermediate query which only picks
out the numbers from a desired bin, but then I'm still left with
sorting the times and autoincrementin g that second field. Thanks for
any tips or suggestions,
-mael

[Ron2005]

Re: Create query to sort data and autoincrement associated column

Simplest to understand (maybe)

In VBA module
1) create query to give you the BIN and # records in each BIN

2) bounce thu that query and
a) create query to select from the first from the first query
ranged down to the BIN you are sitting on
b) bounce through that query and
i. create record with the time from the record you are on
and the BIN / (# of records for the BIN less 1) times (the count of
the record for that BIN that you are sitting on less 1). For the first
and last record use different logic {I do not know what you want if
there is only 1 time}.
ii. Also on the last one of them you want to put the BIN
number there instead of the computed figure, otherwise if you have 3
records, for example, the last one will not equal the BIN. also except
for the condition of only 1 record.
iii for the first record use 0 except for the only 1
condition which I do not know what you want to be loaded.

Ron

[mael.iosa@gmail.com]

Re: Create query to sort data and autoincrement associated column

Thanks Ron,
Success! I mostly followed what you said.

Using DoCmd.RunSQL I did CREATE TABLE for each of the 5 "sets" I
wanted, then used INSERT INTO to pull of the times and put them into
these tables. Then I used a While loop to go through each RecordSet of
each table, and increment the column from 0 on up. Thanks again,
-mael