Query to copy selective records (and modify a column)

  • Thread starter Thread starter Bob Howard
  • Start date Start date
B

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
 
Bob said:
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)
Click to expand...


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

Marshall Barton said:
Bob said:
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)
Click to expand...


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"
Click to expand...
 
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



Marshall Barton said:
Bob said:
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)
Click to expand...


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"
Click to expand...
 
Back
Top