Place sequential numbers into groups

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day everyone,
I have a table that has 10 fields, but for this question, I think I only
need two.


tblFixes
---------
ID...FixNo
1....1
2....2
3....3
4....4
5....1
6....2
7....3
8....4
9....1
10...2
11...3
12...4
13...5
14...6
15...1
16...1
17...1 and so on.

I am trying to get the following in a query or script, ( I have good
knowledge with queries and how they work, not so good with VB code.

tblFixes
---------
ID...FixNo....FixGroup
1....1..........1
2....2..........1
3....3..........1
4....4..........1
5....1..........2
6....2..........2
7....3..........2
8....4..........2
9....1..........3
10...2.........3
11...3.........3
12...4.........3
13...5.........3
14...6.........3
15...1.........4
16...1.........5
17...1.........6

I tried a vareity of things inluding DLookup expressions and other methods.
No luck so far. Thanks in advance for any suggestions, comments or Ideas.
Note: there are about 70,000 records all distinguishable by the ID which is
an autonumber.

Eskimo
 
I'm not sure this UNTESTED SQL will work, but you might try

SELECT ID, FixNo,
(SELECT Count(*)
FROM tblFixes as Fa
WHERE Fa.ID < (
SELECT Min(ID) as SeqEnd
FROM tblFixes as Fb
WHERE Fb.FixNo = 1
And Fb.ID >Fa.ID)
And Fa.ID >= F.ID
And FA.FixNo <= F.FixNo) as Rank
FROM tblFixes as F
 
Hi John,

It seems I have set a trigger with your query that my computer dont like.

When I try and run the query, it show the hourglass indicating that it is
trying to run the query, but no activity from the computer at all. all it
does is show hourglass.

I'll let it sit and think for a while, see what it does. Meanwhile, I was
able to make progress, very little progress mind you, by doing a sort by
fixno first and then ID.

This gave me all the FixNo = 1's first then by ID. I was just then able to
copy and paste the numbers 1-15726 into that sorted table'a fix column.

When I go and re-sort by ID Again, I was able to get the following.

tblFixes
---------
ID...FixNo....FixGroup
1....1..........1
2....2
3....3
4....4
5....1..........2
6....2
7....3
8....4
9....1..........3
10...2
11...3
12...4
13...5
14...6
15...1.........4
16...1.........5
17...1.........6

I just need to fill in the blanks now somehow. thanks

Eskimo
 
If my solution worked it would be slow, especially if there are not indexes
on the ID and FixNo.

If this was a one-time effort, I might be tempted to use some VBA and an
ordered recordset. UNTESTED VBA follows.

Dim lngGroup as Long
Dim rst as DAO.Recordset
Dim dbAny as DAO.Database

Set dbAny = Currentdb()
Set rst = dbAny.OpenRecordset ("SELECT ID, FixNo, FixGroup FROM SomeTable
ORDER BY Id, FixNo, FixGroup)

lngGroup= 0
With rst
.moveFirst
While Not .eof
If .Fields("fixNo") = 1 Then lngGroup= lngGroup+ 1
.Edit
.Fields("FixGroup") = lngGroup
.Update
.movenext
Wend
End with
 
Hi John,

The code worked perfectly. Its got it all grouped together nicely. Excellent
work.

I did have to divide my table into 8 seperate ones. This was due to the
error with the maxlocksperfile exceeded. I tried upping the number in the
registry, but it still would come up. So I just reduced the number of rows to
about 8900 for each table and made simple changes to your code to fit each
one and it all worked out well.

Thank you very much.

Eskimo
 
Back
Top