query or script

[nofear]

I have a table with 2 fields where one has duplicates and the other
does not

something like this:

ID field1 field2
1 A A
2 A B
3 A C
4 A D
5 B E
6 B F
7 B G
8 B H

I'd like to select 2 records where field1 is A and 2 records where
field1 is B and insert it into a new table and also delete the
selected records from the table
I'm sure if this can be accomplished with a query or combination of
queries or I need to write a VBA script

any help appreciated

[Salad]

nofear wrote:

I have a table with 2 fields where one has duplicates and the other
does not

something like this:

ID field1 field2
1 A A
2 A B
3 A C
4 A D
5 B E
6 B F
7 B G
8 B H

I'd like to select 2 records where field1 is A and 2 records where
field1 is B and insert it into a new table and also delete the
selected records from the table
I'm sure if this can be accomplished with a query or combination of
queries or I need to write a VBA script

any help appreciated

You could do something like
Select * From Table Where ID In (2,3,6,7)
and from that make MakeTable/Delete queries.

If this is a one time thing, that's what I'd do. If not, I figure you
can make some queries to do what you want but I'd go with code using
queries...maybe a form.

[Bob Quintal]

nofear <eu*******@hotmail.comwrote in
news:ea01d8bd-f8f7-4138-ba7d-df8a63cc2173@
25g2000hsx.googlegroups.com
:

I have a table with 2 fields where one has duplicates and the
other does not

something like this:

ID field1 field2
1 A A
2 A B
3 A C
4 A D
5 B E
6 B F
7 B G
8 B H

I'd like to select 2 records where field1 is A and 2 records where
field1 is B and insert it into a new table and also delete the
selected records from the table
I'm sure if this can be accomplished with a query or combination
of queries or I need to write a VBA script

any help appreciated

What should be done with the data in field2?
If you do not want it included in the new table, then use a query
with the distinct property set.

INSERT INTO newtable (ID,Field1) values (
SELECT DISTINCT ID, Field1 FROM oldtable);

If you need one value from field2, we'll need to know how to choose
the right one before making suggestions.

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[nofear]

The field1 really matters
I want to select the whole record (row) if the value in field1 is the
same but not all of them
simply I want to divide the data to 2 tables
if I have 4 records where the value in field1 is the same I want to
select 2 records and inser them into a new table
I fid it dificult to do with SQL


On Jul 20, 8:41*am, Bob Quintal <[e-mail]:

nofear <[e-mail] in
news:ea01d8bd-f8f7-4138-ba7d-df8a63cc2173@
25g2000hsx.googlegroups.com
:

I have a table with 2 fields where one has duplicates and the
other does not

something like this:

ID * * field1 * * field2
1 * * *A * * * * * *A
2 * * *A * * * * * *B
3 * * *A * * * * * *C
4 * * *A * * * * * *D
5 * * *B * * * * * *E
6 * * *B * * * * * *F
7 * * *B * * * * * *G
8 * * *B * * * * * H

I'd like to select 2 records where field1 is A and 2 records where
field1 is B and insert it into a new table and also delete the
selected records from the table
I'm sure if this can be accomplished with a query or combination
of queries or I need to write a VBA script

any help appreciated

What should be done with the data in field2?
If you do not want it included in the new table, then use a query
with the distinct property set.

INSERT INTO newtable (ID,Field1) values (
SELECT DISTINCT ID, Field1 FROM oldtable);

If you need one value from field2, we'll need to know how to choose
the right one before making suggestions.

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

[Bob Quintal]

nofear <eu*******@hotmail.comwrote in news:d8343373-3a94-484b-
af***************@26g2000hsk.googlegroups.com:

The field1 really matters
I want to select the whole record (row) if the value in field1 is

the

same but not all of them
simply I want to divide the data to 2 tables
if I have 4 records where the value in field1 is the same I want

to

select 2 records and inser them into a new table
I fid it dificult to do with SQL

The easiest way is to copy the table structure to a new table, using
the save as dialog, with no records, then creating a query that
selects only the two important fields, with the distinct property
(or keyword). Convert that into an Append (Insert) query, into the
new table.

To get data from the other fields, if necessary, use an update query
that usesv dlookup() functions.

>

On Jul 20, 8:41*am, Bob Quintal <[e-mail]:

>nofear <[e-mail] in
news:ea01d8bd-f8f7-4138-ba7d-df8a63cc2173@
25g2000hsx.googlegroups.com
:

I have a table with 2 fields where one has duplicates and the
other does not

something like this:

ID * * field1 * * field2
1 * * *A * * * * * *A
2 * * *A * * * * * *B
3 * * *A * * * * * *C
4 * * *A * * * * * *D
5 * * *B * * * * * *E
6 * * *B * * * * * *F
7 * * *B * * * * * *G
8 * * *B * * * * * H

I'd like to select 2 records where field1 is A and 2 records

where

field1 is B and insert it into a new table and also delete the
selected records from the table
I'm sure if this can be accomplished with a query or

combination

of queries or I need to write a VBA script

any help appreciated

What should be done with the data in field2?
If you do not want it included in the new table, then use a query
with the distinct property set.

INSERT INTO newtable (ID,Field1) values (
SELECT DISTINCT ID, Field1 FROM oldtable);

If you need one value from field2, we'll need to know how to

choose

>the right one before making suggestions.

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[nofear]

On Jul 21, 10:05*pm, Bob Quintal <[e-mail]:

nofear <[e-mail] in news:d8343373-3a94-484b-
[e-mail]:

The field1 really matters
I want to select the whole record (row) if the value in field1 is

the

same but not all of them
simply I want to divide the data to 2 tables
if I have 4 records where the value in field1 is the same I want

to

select 2 records and inser them into a new table
I fid it dificult to do with SQL

The easiest way is to copy the table structure to a new table, using
the save as dialog, with no records, then creating a query that
selects only the two important fields, with the distinct property
(or keyword). Convert that into an Append (Insert) query, into the
new table.

To get data from the other fields, if necessary, use an update query
that usesv dlookup() functions.

On Jul 20, 8:41*am, Bob Quintal <[e-mail]:

nofear <[e-mail] in
news:ea01d8bd-f8f7-4138-ba7d-df8a63cc2173@
25g2000hsx.googlegroups.com
:

I have a table with 2 fields where one has duplicates and the
other does not

something like this:

ID * * field1 * * field2
1 * * *A * * * * * *A
2 * * *A * * * * * *B
3 * * *A * * * * * *C
4 * * *A * * * * * *D
5 * * *B * * * * * *E
6 * * *B * * * * * *F
7 * * *B * * * * * *G
8 * * *B * * * * * H

I'd like to select 2 records where field1 is A and 2 records

where

field1 is B and insert it into a new table and also delete the
selected records from the table
I'm sure if this can be accomplished with a query or

combination

of queries or I need to write a VBA script

any help appreciated

What should be done with the data in field2?
If you do not want it included in the new table, then use a query
with the distinct property set.

INSERT INTO newtable (ID,Field1) values (
SELECT DISTINCT ID, Field1 FROM oldtable);

If you need one value from field2, we'll need to know how to

choose

the right one before making suggestions.

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

I see
I would have to rerun this query with different property
I think I use this technique but I have to wite a VBA script that will
change the property and run the query till EOF

[Bob Quintal]

nofear <eu*******@hotmail.comwrote in
news:952384f0-70e2-4b28-a46e-464bc13277b7@
56g2000hsm.googlegroups.com
:

On Jul 21, 10:05*pm, Bob Quintal <[e-mail]:

>nofear <[e-mail] in news:d8343373-3a94-484b-
[e-mail]:

The field1 really matters
I want to select the whole record (row) if the value in field1
is

the

same but not all of them
simply I want to divide the data to 2 tables
if I have 4 records where the value in field1 is the same I
want

to

select 2 records and inser them into a new table
I fid it dificult to do with SQL

The easiest way is to copy the table structure to a new table,
using the save as dialog, with no records, then creating a query
that selects only the two important fields, with the distinct
property (or keyword). Convert that into an Append (Insert)
query, into the new table.

To get data from the other fields, if necessary, use an update
query that usesv dlookup() functions.

On Jul 20, 8:41*am, Bob Quintal <[e-mail]:
nofear <[e-mail] in
news:ea01d8bd-f8f7-4138-ba7d-df8a63cc2173@
25g2000hsx.googlegroups.com
:

I have a table with 2 fields where one has duplicates and
the other does not

something like this:

ID * * field1 * * field2
1 * * *A * * * * * *A
2 * * *A * * * * * *B
3 * * *A * * * * * *C
4 * * *A * * * * * *D
5 * * *B * * * * * *E
6 * * *B * * * * * *F
7 * * *B * * * * * *G
8 * * *B * * * * * H

I'd like to select 2 records where field1 is A and 2 records

where

field1 is B and insert it into a new table and also delete
the selected records from the table
I'm sure if this can be accomplished with a query or

combination

of queries or I need to write a VBA script

any help appreciated

>What should be done with the data in field2?
If you do not want it included in the new table, then use a
query with the distinct property set.

>INSERT INTO newtable (ID,Field1) values (
SELECT DISTINCT ID, Field1 FROM oldtable);

>If you need one value from field2, we'll need to know how to

choose

>the right one before making suggestions.

>--
Bob Quintal

>PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

I see
I would have to rerun this query with different property
I think I use this technique but I have to wite a VBA script that
will change the property and run the query till EOF

SQL is a powerful language. You can do this in one query, with the
properties selected by the query itself. However, if you are more
confident using a recordset, that is acceptable.
--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[nofear]

then I gues I have to seek for help in a SQL group
On Jul 22, 9:25*am, Bob Quintal <[e-mail]:

nofear <[e-mail] in
news:952384f0-70e2-4b28-a46e-464bc13277b7@
56g2000hsm.googlegroups.com
:

On Jul 21, 10:05*pm, Bob Quintal <[e-mail]:

nofear <[e-mail] in news:d8343373-3a94-484b-
[e-mail]:

The field1 really matters
I want to select the whole record (row) if the value in field1
is
the
same but not all of them
simply I want to divide the data to 2 tables
if I have 4 records where the value in field1 is the same I
want
to
select 2 records and inser them into a new table
I fid it dificult to do with SQL

The easiest way is to copy the table structure to a new table,
using the save as dialog, with no records, then creating a query
that selects only the two important fields, with the distinct
property (or keyword). Convert that into an Append (Insert)
query, into the new table.

To get data from the other fields, if necessary, use an update
query that usesv dlookup() functions.

On Jul 20, 8:41*am, Bob Quintal <[e-mail]:
nofear <[e-mail] in
news:ea01d8bd-f8f7-4138-ba7d-df8a63cc2173@
25g2000hsx.googlegroups.com
:

I have a table with 2 fields where one has duplicates and
the other does not

something like this:

ID * * field1 * * field2
1 * * *A * * * * * *A
2 * * *A * * * * * *B
3 * * *A * * * * * *C
4 * * *A * * * * * *D
5 * * *B * * * * * *E
6 * * *B * * * * * *F
7 * * *B * * * * * *G
8 * * *B * * * * * H

I'd like to select 2 records where field1 is A and 2 records
where
field1 is B and insert it into a new table and also delete
the selected records from the table
I'm sure if this can be accomplished with a query or
combination
of queries or I need to write a VBA script

any help appreciated

What should be done with the data in field2?
If you do not want it included in the new table, then use a
query with the distinct property set.

INSERT INTO newtable (ID,Field1) values (
SELECT DISTINCT ID, Field1 FROM oldtable);

If you need one value from field2, we'll need to know how to
choose
the right one before making suggestions.

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

I see
I would have to rerun this query with different property
I think I use this technique but I have to wite a VBA script that
will change the property and run the query till EOF

SQL is a powerful language. You can do this in one query, with the
properties selected by the query itself. However, if you are more
confident using a recordset, that is acceptable.

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**