Assigning Sequential Number

[feebert]

I have a query that baically select quotes from one table and appends records from those quote to another table. Here's an example

Quote 3533, part A100,
Quote 3533, part A111,
Quote 3533, part C852,
Quote 3669, part B552,
Quote 3669, part B663,

So what I want to do, is add a field that increments 1 everytime there's a new part. Something like this:

Quote 3533, part A100, 1
Quote 3533, part A111, 2
Quote 3533, part C852, 3
Quote 3669, part B552, 1
Quote 3669, part B663, 2

I've tried the DCount function and DMax on tables, but I'm not sure how to incorporate this in a query.

 

[Fizzio]

Is there a reason that you have to copy the data from one table to another?

 

[feebert]

Reasons for Appending tp new table

I am reading information from another DB(a) and the table I am reading from does not have a sequential numbering system. The Table I'm appending too, needs a sequential number in order to function properly. The table I am appending too is not an AccessDB, its simply a linked table(b) that does not have an autonumber.

So I need to ppend info from (a) to (b) and create some kind of sequential numbering system.

Is this possible?

 

[Rich]

Why not use an auto number?

 

[feebert]

I can't use an autonumber at this point.

Is what I'm asking possible, if so, I need a little direction.

 

[Jon K]

You can use DCount (or Select Count, though it will make the query non-updatable) to display the sequential numbers in a query.

I have attached a sample DB which contains two queries using DCount.

The first query "qry Sequential Number_A" is based on table "tblQuotePart_A". The query works only if the part numbers for each quote number are entered in the table in ascending order, as shown by your sample data.

The second query "qry Sequential Number_B" is based on table "tblQuotePart_B". This table is similar to table A except for an additional autonumber field. With this autonumber field, the sequential number is not affected by the order in which the part numbers are entered in the table.

If you can't use an autonumber and the part numbers for each quote number are not entered in ascending order, then I think you can only use VBA to loop through the records.


(The attached DB is in Access 97. To open it in Access 2000 or 2002, just choose Convert and save as a new name when the DB is opened for the first time.)