increment query field

[sfreeman@co.mer]

I need to create a simple query that selects a few fields from a table, and appends them to another table.

No big deal, right?

INSERT INTO Table2 ( FieldOne, FieldTwo )
SELECT Table1.FieldOne, Table1.FieldTwo
FROM Table1;

BUT, I also need to append values into a third field that just number the records in this recordset in order.

For example, if my query selects 10 records, then the values in the third field would be 1,2,3,4,5,6,7,8,9,10.

The results would appear to be something like:

FieldOne FieldTwo RecNo
Ron Smith 1
Bill Jones 2
Don Johnson 3
Dan Hicks 4

etc, etc.

Any help will be appreciated!

Thanks in advance....

 

[meboz]

A simple query wont do it i dont think.

You could try a correlated sub-query. Like creating a running sum sought of except you want to create a running count

OR...

Do it using VBA.

Grab the recordset from the table and loop through each record inserting the values into the table, along with rst.absolute position, which give you the position of the record, but it corresponds to the data you want.

 

[SBM]

How do you create a running count in a query ?

A simple query wont do it i dont think.

You could try a correlated sub-query. Like creating a running sum sought of except you want to create a running count

OR...

Do it using VBA.

Grab the recordset from the table and loop through each record inserting the values into the table, along with rst.absolute position, which give you the position of the record, but it corresponds to the data you want.

 

[sfreeman@co.mer]

Found It - Many Thanks to Duane

INSERT INTO tblHeaderInfo
( CaseNo
, CalendarNo )

SELECT
TodaysCases.DCSS
, (SELECT Count(*) FROM TodaysCases t1 WHERE t1.Docket <= TodaysCases.Docket) AS CalNo

FROM TodaysCases;

Man, Duane, I never would have thought of this...