Incremental Numbering

  • Thread starter Thread starter Marianne
  • Start date Start date
M

Marianne

Can this task be accomplished through an update query?

I want to number the lines for an existing database. There are 13K+ records
in table A.

I have a query using the count function which returns the correct number of
lines for each InvID, but how do I update each line with the correct number,
1,2,3 etc?

Table A has a one-to-many relationship with Table B. The primary key fields
are Table A.InvID and Table B.DetInvID I have added a field [LineNum] to
Table B. [LineNum] should increment starting at 1 for each InvID.

TableB.InvID TableB.LineNum

555 1

555 2

555 3

556 1

557 1

557 2



Assistance is greatly appreciated!

Marianne
 
Marianne said:
Can this task be accomplished through an update query?

I want to number the lines for an existing database. There are 13K+ records
in table A.

I have a query using the count function which returns the correct number of
lines for each InvID, but how do I update each line with the correct number,
1,2,3 etc?

Table A has a one-to-many relationship with Table B. The primary key fields
are Table A.InvID and Table B.DetInvID I have added a field [LineNum] to
Table B. [LineNum] should increment starting at 1 for each InvID.

TableB.InvID TableB.LineNum

555 1

555 2

555 3

556 1

557 1

557 2
Click to expand...


Based on your example (i.e. without more information), there
is no way for you or anyone else to determine "the correct
number" for any record.

Since tables are like a bag of data, the records must
contain values that can be used to uniquely sort them in the
order that you want to number them. This is something that
would normally be done using VBA code at teh time the
records were created, either by calculating the next higher
number or by having some additional some data in each record
so a query or a report can calculate the number.
 
UNTESTED UNTESTED

I can think of a possible way.
Use your query that you have for numbering the line numbers and add the
table B primary key and make a temp table.

Then join temp table with table B to update the linenumber field.
 
Is this only going to be done once?
Do they have to have a line number?
What happens if a report is needed that sorts those items in another order?
Adding line numbers at report time is fairly easy and requires no coding.

Is there a chance that a new Invoice might be added?
 
It's a lot easier to do in VBA code while you are creating the records.

It can still be done. but I don't see how a query would do it. In code,
you'd build a recordset sorting on the InvID, then walk through the
recordset, adding 1 to LinNum, until InvID changes, then start with 1 again.
 
Thanks to everyone for all your info and suggestions. I think I shall try the
vb code approach and see if I can make that work.

Arvin Meyer said:
It's a lot easier to do in VBA code while you are creating the records.

It can still be done. but I don't see how a query would do it. In code,
you'd build a recordset sorting on the InvID, then walk through the
recordset, adding 1 to LinNum, until InvID changes, then start with 1 again.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Marianne said:
Can this task be accomplished through an update query?

I want to number the lines for an existing database. There are 13K+
records
in table A.

I have a query using the count function which returns the correct number
of
lines for each InvID, but how do I update each line with the correct
number,
1,2,3 etc?

Table A has a one-to-many relationship with Table B. The primary key
fields
are Table A.InvID and Table B.DetInvID I have added a field [LineNum] to
Table B. [LineNum] should increment starting at 1 for each InvID.

TableB.InvID TableB.LineNum

555 1

555 2

555 3

556 1

557 1

557 2



Assistance is greatly appreciated!

Marianne
Click to expand...


.
Click to expand...
 
Back
Top