Query to copy selective records (and modify a column)

[Bob Howard]

Is it possible to create a query (or maybe just an SQL statement) to copy a
selective set of records?

The conditions are as follows:

The added records are to go in the same table as the original records.

I only want to copy selected records (those where a certain column equals a
specific value).

I want to modify a certain other column to be a different value than in the
original records --- the new value will be the same in all of the new
records.

Here's an example of a table containing 4 columns:

Cat A 12 1
Dog A 14 1
Bear B 16 1
Ox B 17 1

I want to copy all records where Column 2 = "A". I want columns 1, 2 and 3
in the new records to be the same as they were in the old records. I want
column 4 in the new records to be the number 2 (regardless of column 4 in
the old records).

So the resulting table would be:

Cat A 12 1 (old record)
Dog A 14 1 (old record)
Bear B 16 1 (old record)
Ox B 17 1 (old record)
Cat A 12 2 (new record)
Dog A 14 2 (new record)

I know I can write this using DAO and loop thru the entire table --- but
there might be a much cleaner way...

Thanks for any help you can provide....

bob

 

[Marshall Barton]

Is it possible to create a query (or maybe just an SQL statement) to copy a
selective set of records?

The conditions are as follows:

The added records are to go in the same table as the original records.

I only want to copy selected records (those where a certain column equals a
specific value).

I want to modify a certain other column to be a different value than in the
original records --- the new value will be the same in all of the new
records.

Here's an example of a table containing 4 columns:

Cat A 12 1
Dog A 14 1
Bear B 16 1
Ox B 17 1

I want to copy all records where Column 2 = "A". I want columns 1, 2 and 3
in the new records to be the same as they were in the old records. I want
column 4 in the new records to be the number 2 (regardless of column 4 in
the old records).

So the resulting table would be:

Cat A 12 1 (old record)
Dog A 14 1 (old record)
Bear B 16 1 (old record)
Ox B 17 1 (old record)
Cat A 12 2 (new record)
Dog A 14 2 (new record)

Try this kind of thing:

INSERT INTO Critters (Specie, Loc, Num, Age)
SELECT Specie, Loc, Num, Age + 1
FROM [Stupid Phone Log]
WHERE Loc = "A"

 

[Bob Howard]

Thanks --- I'll give it a whirl. I forgot to mention that there's an
additional field which is an autonumber and is the primary key. How do I
refer to that in the SQL??

bob

Is it possible to create a query (or maybe just an SQL statement) to copy
a
selective set of records?

The conditions are as follows:

The added records are to go in the same table as the original records.

I only want to copy selected records (those where a certain column equals
a
specific value).

I want to modify a certain other column to be a different value than in
the
original records --- the new value will be the same in all of the new
records.

Here's an example of a table containing 4 columns:

Cat A 12 1
Dog A 14 1
Bear B 16 1
Ox B 17 1

I want to copy all records where Column 2 = "A". I want columns 1, 2 and
3
in the new records to be the same as they were in the old records. I want
column 4 in the new records to be the number 2 (regardless of column 4 in
the old records).

So the resulting table would be:

Cat A 12 1 (old record)
Dog A 14 1 (old record)
Bear B 16 1 (old record)
Ox B 17 1 (old record)
Cat A 12 2 (new record)
Dog A 14 2 (new record)

Try this kind of thing:

INSERT INTO Critters (Specie, Loc, Num, Age)
SELECT Specie, Loc, Num, Age + 1
FROM [Stupid Phone Log]
WHERE Loc = "A"

 

[Bob Howard]

I handled the primary key (ahtonumber) field by simply omitting it from both
the INSERT and the SELECT statements. It worked on the first shot! Thanks
so much..... bob

Is it possible to create a query (or maybe just an SQL statement) to copy
a
selective set of records?

The conditions are as follows:

The added records are to go in the same table as the original records.

I only want to copy selected records (those where a certain column equals
a
specific value).

I want to modify a certain other column to be a different value than in
the
original records --- the new value will be the same in all of the new
records.

Here's an example of a table containing 4 columns:

Cat A 12 1
Dog A 14 1
Bear B 16 1
Ox B 17 1

I want to copy all records where Column 2 = "A". I want columns 1, 2 and
3
in the new records to be the same as they were in the old records. I want
column 4 in the new records to be the number 2 (regardless of column 4 in
the old records).

So the resulting table would be:

Cat A 12 1 (old record)
Dog A 14 1 (old record)
Bear B 16 1 (old record)
Ox B 17 1 (old record)
Cat A 12 2 (new record)
Dog A 14 2 (new record)

Try this kind of thing:

INSERT INTO Critters (Specie, Loc, Num, Age)
SELECT Specie, Loc, Num, Age + 1
FROM [Stupid Phone Log]
WHERE Loc = "A"