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 onetomany 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 onetomany 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 onetomany 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 onetomany 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:2BA6B34741C4405EAD1910329852E960(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 onetomany 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 