Prev: Calculated control event order
Next: Query results in a listbox control
From: Marianne on 1 Feb 2010 22:52
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


From: Marshall Barton on 1 Feb 2010 23:31
Marianne wrote:

>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


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.

--
Marsh
MVP [MS Access]
From: KARL DEWEY on 1 Feb 2010 23:50
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.

--
Build a little, test a little.


"Marianne" wrote:

> 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
>
>
From: Mike Painter on 2 Feb 2010 01:39
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?


Marianne wrote:
> 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


From: Arvin Meyer [MVP] on 2 Feb 2010 15:07
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" <Marianne(a)discussions.microsoft.com> wrote in message
news:2BA6B347-41C4-405E-AD19-10329852E960(a)microsoft.com...
> 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
>
>


 |  Next  |  Last
Pages: 1 2
Prev: Calculated control event order
Next: Query results in a listbox control