Autonumber (number sequencing) Via Query

[krmcdonald]

I realize this has been covered a billion times, I've read them all and still can't get it. My table looks like this:
DOCUMENT-SER.NO
MC006343 -SST-24569
MC006344 -SST-27209
MC006344 -SST-27210
MC006397 -SST-25019
MC006397 -SST-24562
MC006397 -SST-24562
MC006397 -SST-99173
MC006397 -SST-99173

DOCUMENT-SER.NO.-NEED THIS
MC006343 -SST-24569-0001
MC006344 -SST-27209-0001
MC006344 -SST-27210-0002
MC006397 -SST-25019-0001
MC006397 -SST-24562-0002
MC006397 -SST-24562-0003
MC006397 -SST-99173-0004
MC006397 -SST-99173-0005

At every change in Document Number, start the numbering over.

Using an Update Query, I need to update a field (named LINE) showing the numbers in the "NEED THIS" column above. 0001, 0001, 0002, 0001, 0002, 0003, 0004, 0005...etc.

This would give me a different line number for each document...

I've read all the autonumber and dmax posts, and still can't get it.
NOVICE Access User...can use queries pretty good, but not SQL.

MSAccess 2003, Windows XP Pro...

Thanks in advance!

[mlcottingham]

I'm sure there is a more elegant way to do this, but I've done this by breaking the task into a sequence of queries.

Query 1:
Create Table TEMP_TABLE (
[DOCUMENT-SER-NO] Text(50),
Document Text(50),
rowid AutoIncrement,
sequence integer
);

Query 2:
INSERT INTO TEMP_TABLE ( Document, [DOCUMENT-SER-NO] )
SELECT Left([DOCUMENT-SER-NO],InStr([DOCUMENT-SER-NO]," ")-1) AS doc, BaseTable.[DOCUMENT-SER-NO]
FROM BaseTable
ORDER BY Left([DOCUMENT-SER-NO],InStr([DOCUMENT-SER-NO]," ")-1), BaseTable.[DOCUMENT-SER-NO];

Query 3:
SELECT TEMP_TABLE.Document, Min(TEMP_TABLE.rowid) AS MinOfrowid INTO MinList
FROM TEMP_TABLE
GROUP BY TEMP_TABLE.Document;

Query 4:

UPDATE MinList INNER JOIN TEMP_TABLE ON MinList.Document = TEMP_TABLE.Document SET TEMP_TABLE.sequence = [rowid]-[MinOfRowid]+1;

Now, Table [TEMP_TABLE] looks like:

DOCUMENT-SER-NO Document rowid sequence
MC006343 -SST-24569 MC006343 1 1
MC006344 -SST-27209 MC006344 2 1
MC006344 -SST-27210 MC006344 3 2
MC006397 -SST-24562 MC006397 4 1
MC006397 -SST-24562 MC006397 5 2
MC006397 -SST-25019 MC006397 6 3
MC006397 -SST-99173 MC006397 7 4
MC006397 -SST-99173 MC006397 8 5

Then, you can either do an update query or whatever you need to using this new table. Your new document ID is
Trim([DOCUMENT-SER-No]) & "-" & Format([sequence],"0000")

If you want to add drop table queries to your sequence, they would look like:

drop table [MinList];

drop table [TEMP_TABLE];

[krmcdonald]

Thanks...this works fine.
Thanks for the clairity...I've been reading all the autonumber posts and they were just making me crazy.

Thanks again