Increment field in query 2

[sfreeman]

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....

 

[dhookom]

The third field value would need to be created/calculated based on other values in the table. Do you have a suggestion?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

Each time you run the query do you want it to number the inserted records starting at 1? Do you care which record is inserted first?

 

[sfreeman]

No other values in the table come to mind as having any relationship to a generic sequence generation.

Yes, each time the query is run the third field should start the sequence at 1. It does not matter which record is inserted first.

Thx for your reply. Any ideas???

 

[dhookom]

You could try something like:
INSERT INTO Table2 ( FieldOne, FieldTwo, FieldThree )
SELECT Table1.FieldOne, Table1.FieldTwo, (SELECT Count(*) FROM Table1 t1 WHERE t1.FieldOne & t1.FieldTwo >= Table1.FieldOne & Table1.FieldTwo)
FROM Table1;

You may need to play with the ">=". Also, this assumes there are no duplicates of FieldOne & FieldTwo in Table1.



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

To prevent problems in situations like this...

Field1 Field2
121 11
12 111

You can add a delimiter between the two fields. True duplicates would still be a problem.

 

[sfreeman]

Thanks guys, both suggestions were alot of help. Working great now.

MUCH APPRECIATED.