SQL for writing to a table - help requested 1

[teach314]

I have a Query called qZoneData that has 3 columns and about 5500 records. It starts like this:

Z1 Z2 Z3
18 46 92
16 42 87
91 98 3
etc.

I'm sure this is easy, but I'm a bit new to this. I want to know what SQL would let me transfer this data to an existing empty table that has 4 columns.

ID Z1 Z2 Z3
Ca_0001 18 46 92
Ca_0002 16 42 87
Ca_0003 91 98 3
etc..
Ca_5500 19 69 21

My problem is in how to generate the ID values then write everything to the table. The ID values all start 'Ca_' then append a 4 digit number starting with 0001 and increasing by 1 for each record.

Thanks

 

[dhookom]

Apparently you want the records numbered in the new table but you haven't defined the order from your qZoneData. Can you add a unique field to the query that identifies the order?

The ID values are basically a "ranking query" value. If you search on this term in this forum, you should find some examples.

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

The 'Ranking query' idea worked great. Thanks