p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: MOVING records from one table to another


Message #1 by wesley.fitzpatrick@s... on Thu, 27 Jun 2002 12:31:31 +0200
Hi All,

Is there anyway of MOVING a set of records (based on criteria) from one
table to another without doing a 'cut & paste' job of inserting and
deleting?

Thanks,

Wes.

----- 
Wesley Fitzpatrick
IS Helpdesk
Syngenta Agro AG
Chemiestrasse 1, Dielsdorf, CH-8157, Switzerland
Tel: +41 (0)1 8558 411
Fax: +41 (0)1 8558 708
wesley.fitzpatrick@s...
Message #2 by joe.dunn@c... on Thu, 27 Jun 2002 11:53:30 +0000
<<Is there anyway of MOVING a set of records (based on criteria) from one
table to another without doing a 'cut & paste' job of inserting and
deleting?>>

Create an append query and set the criteria to select the records you
require. Append the selected records to the target table.



Must be same structure in both tables

Could be issues with duplicate keys in target table!


*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************
Message #3 by wesley.fitzpatrick@s... on Thu, 27 Jun 2002 13:52:24 +0200
Thanks,

I was really looking for a piece of code that would move the records- what I
need to do is append the records to one table then delete them off the
origional table. I wanted to be lazy but I guess I'll end up doing it that
way anyway!

Wes.

 ~-----Original Message-----
 ~From: joe.dunn@c... [mailto:joe.dunn@c...]
 ~Sent: Donnerstag, 27. Juni 2002 13:54
 ~To: Access
 ~Subject: [access] Re: MOVING records from one table to another
 ~
 ~
 ~
 ~<<Is there anyway of MOVING a set of records (based on 
 ~criteria) from one
 ~table to another without doing a 'cut & paste' job of inserting and
 ~deleting?>>
 ~
 ~Create an append query and set the criteria to select the records you
 ~require. Append the selected records to the target table.
 ~
 ~
 ~
 ~Must be same structure in both tables
 ~
 ~Could be issues with duplicate keys in target table!
 ~
 ~
 ~**************************************************************
 ~***********
 ~
 ~This e-mail may contain confidential information or be 
 ~privileged. It is intended to be read and used only by the 
 ~named recipient(s). If you are not the intended recipient(s) 
 ~please notify us immediately so that we can make arrangements 
 ~for its return: you should not disclose the contents of this 
 ~e-mail to any other person, or take any copies. Unless stated 
 ~otherwise by an authorised individual, nothing contained in 
 ~this e-mail is intended to create binding legal obligations 
 ~between us and opinions expressed are those of the individual author.
 ~
 ~The CIS marketing group, which is regulated for Investment 
 ~Business by the Financial Services Authority, includes:
 ~Co-operative Insurance Society Limited Registered in England 
 ~number 3615R - for life assurance and pensions
 ~CIS Unit Managers Limited Registered in England and Wales 
 ~number 2369965  - for unit trusts and PEPs
 ~CIS Policyholder Services Limited Registered in England and 
 ~Wales number 3390839 - for ISAs and investment products 
 ~bearing the CIS name
 ~Registered offices: Miller Street, Manchester M60 0AL   
 ~Telephone  0161-832-8686   Internet  http://www.cis.co.uk   
 ~E-mail cis@c...
 ~
 ~CIS Deposit and Instant Access Savings Accounts are held with 
 ~The Co-operative Bank p.l.c., registered in England and Wales 
 ~number 990937, P.O. Box 101, 1 Balloon Street, Manchester M60 
 ~4EP, and administered by CIS Policyholder Services Limited as 
 ~agent of the Bank.
 ~
 ~CIS is a member of the General Insurance Standards Council
 ~
 ~CIS & the CIS logo (R) Co-operative Insurance Society Limited
 ~
 ~**************************************************************
******************
 ~
 ~
Message #4 by "Amy Wyatt" <amyw@c...> on Thu, 27 Jun 2002 13:19:40
Hate to tell you this but you are thinking graphically. Even when a move 
is done in a program the underlying code has to append and then delete. It 
is an illusion that it is being picked up and moved. 

Sorry,

Amy

> Thanks,

I was really looking for a piece of code that would move the records- what 
I
need to do is append the records to one table then delete them off the
origional table. I wanted to be lazy but I guess I'll end up doing it that
way anyway!

Wes.

 ~-----Original Message-----
 ~From: joe.dunn@c... [mailto:joe.dunn@c...]
 ~Sent: Donnerstag, 27. Juni 2002 13:54
 ~To: Access
 ~Subject: [access] Re: MOVING records from one table to another
 ~
 ~
 ~
 ~<<Is there anyway of MOVING a set of records (based on 
 ~criteria) from one
 ~table to another without doing a 'cut & paste' job of inserting and
 ~deleting?>>
 ~
 ~Create an append query and set the criteria to select the records you
 ~require. Append the selected records to the target table.
 ~
 ~
 ~
 ~Must be same structure in both tables
 ~
 ~Could be issues with duplicate keys in target table!
 ~
 ~
 ~**************************************************************
 ~***********
 ~
 ~This e-mail may contain confidential information or be 
 ~privileged. It is intended to be read and used only by the 
 ~named recipient(s). If you are not the intended recipient(s) 
 ~please notify us immediately so that we can make arrangements 
 ~for its return: you should not disclose the contents of this 
 ~e-mail to any other person, or take any copies. Unless stated 
 ~otherwise by an authorised individual, nothing contained in 
 ~this e-mail is intended to create binding legal obligations 
 ~between us and opinions expressed are those of the individual author.
 ~
 ~The CIS marketing group, which is regulated for Investment 
 ~Business by the Financial Services Authority, includes:
 ~Co-operative Insurance Society Limited Registered in England 
 ~number 3615R - for life assurance and pensions
 ~CIS Unit Managers Limited Registered in England and Wales 
 ~number 2369965  - for unit trusts and PEPs
 ~CIS Policyholder Services Limited Registered in England and 
 ~Wales number 3390839 - for ISAs and investment products 
 ~bearing the CIS name
 ~Registered offices: Miller Street, Manchester M60 0AL   
 ~Telephone  0161-832-8686   Internet  http://www.cis.co.uk   
 ~E-mail cis@c...
 ~
 ~CIS Deposit and Instant Access Savings Accounts are held with 
 ~The Co-operative Bank p.l.c., registered in England and Wales 
 ~number 990937, P.O. Box 101, 1 Balloon Street, Manchester M60 
 ~4EP, and administered by CIS Policyholder Services Limited as 
 ~agent of the Bank.
 ~
 ~CIS is a member of the General Insurance Standards Council
 ~
 ~CIS & the CIS logo (R) Co-operative Insurance Society Limited
 ~
 ~**************************************************************
******************
 ~
 ~
Message #5 by "Kenny Alligood" <kennyalligood@h...> on Thu, 27 Jun 2002 10:20:56 -0400
------=_NextPart_001_0000_01C21DC4.52D547E0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Create an Append Query to add the records to the new table and then chang
e it to a Delete Query to remove the records from the original table.

Kenny

----- Original Message -----
From: wesley.fitzpatrick@s...
Sent: Thursday, June 27, 2002 6:47 AM
To: Access
Subject: [access] MOVING records from one table to another

Hi All,

Is there anyway of MOVING a set of records (based on criteria) from one
table to another without doing a 'cut & paste' job of inserting and
deleting?

Thanks,

Wes.

-----
Wesley Fitzpatrick
IS Helpdesk
Syngenta Agro AG
Chemiestrasse 1, Dielsdorf, CH-8157, Switzerland
Tel: +41 (0)1 8558 411
Fax: +41 (0)1 8558 708
wesley.fitzpatrick@s...

to unsubscribe send a blank email to G
et more from the Web.  FREE MSN Explorer download : http://explorer.msn.c
om

Message #6 by "Arvelo, Robert" <ARVELO@T...> on Thu, 27 Jun 2002 10:14:37 -0400
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C21DE4.F804FA60
Content-Type: text/plain;
	charset="iso-8859-1"

 
 
-----Original Message-----
From: Kenny Alligood [mailto:kennyalligood@h...]
Sent: Thursday, June 27, 2002 10:21 AM
To: Access
Subject: [access] Re: MOVING records from one table to another
 
Create an Append Query to add the records to the new table and then change
it to a Delete Query to remove the records from the original table.
 
Kenny
 
----- Original Message -----
From: wesley.fitzpatrick@s...
Sent: Thursday, June 27, 2002 6:47 AM
To: Access
Subject: [access] MOVING records from one table to another
 
Hi All,

Is there anyway of MOVING a set of records (based on criteria) from one
table to another without doing a 'cut & paste' job of inserting and
deleting?

Thanks,

Wes.

-----
Wesley Fitzpatrick
IS Helpdesk
Syngenta Agro AG
Chemiestrasse 1, Dielsdorf, CH-8157, Switzerland
Tel: +41 (0)1 8558 411
Fax: +41 (0)1 8558 708
wesley.fitzpatrick@s...

--- Change your mail options at http://p2p.wrox.com/manager.asp or to
unsubscribe send a blank email to  

  _____  

Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
<http://explorer.msn.com> 


Message #7 by "Leo Scott" <leoscott@c...> on Thu, 27 Jun 2002 08:26:18 -0700
Still use two queries.  Link the tables on a unique/key field with an outer
join where you select all records from the table you want to move them from.
Have a criteria where the joined field in the table the records are going to
are null.  Then from that base query make it an append query to append into
the receiving table.  Make another query that deletes the same records from
the table you are moving them from.  You can then take the SQL from both
queries and paste it into code to automate the process in the future.  Much
faster than writing loops.

|-----Original Message-----
|From: wesley.fitzpatrick@s...
|[mailto:wesley.fitzpatrick@s...]
|Sent: Thursday, June 27, 2002 4:52 AM
|To: Access
|Subject: [access] Re: MOVING records from one table to another
|
|
|Thanks,
|
|I was really looking for a piece of code that would move the
|records- what I
|need to do is append the records to one table then delete them off the
|origional table. I wanted to be lazy but I guess I'll end up doing it that
|way anyway!
|
|Wes.
|
| ~-----Original Message-----
| ~From: joe.dunn@c... [mailto:joe.dunn@c...]
| ~Sent: Donnerstag, 27. Juni 2002 13:54
| ~To: Access
| ~Subject: [access] Re: MOVING records from one table to another
| ~
| ~
| ~
| ~<<Is there anyway of MOVING a set of records (based on
| ~criteria) from one
| ~table to another without doing a 'cut & paste' job of inserting and
| ~deleting?>>
| ~
| ~Create an append query and set the criteria to select the records you
| ~require. Append the selected records to the target table.
| ~
| ~
| ~
| ~Must be same structure in both tables
| ~
| ~Could be issues with duplicate keys in target table!
| ~
| ~
| ~**************************************************************
| ~***********
| ~
| ~This e-mail may contain confidential information or be
| ~privileged. It is intended to be read and used only by the
| ~named recipient(s). If you are not the intended recipient(s)
| ~please notify us immediately so that we can make arrangements
| ~for its return: you should not disclose the contents of this
| ~e-mail to any other person, or take any copies. Unless stated
| ~otherwise by an authorised individual, nothing contained in
| ~this e-mail is intended to create binding legal obligations
| ~between us and opinions expressed are those of the individual author.
| ~
| ~The CIS marketing group, which is regulated for Investment
| ~Business by the Financial Services Authority, includes:
| ~Co-operative Insurance Society Limited Registered in England
| ~number 3615R - for life assurance and pensions
| ~CIS Unit Managers Limited Registered in England and Wales
| ~number 2369965  - for unit trusts and PEPs
| ~CIS Policyholder Services Limited Registered in England and
| ~Wales number 3390839 - for ISAs and investment products
| ~bearing the CIS name
| ~Registered offices: Miller Street, Manchester M60 0AL
| ~Telephone  0161-832-8686   Internet  http://www.cis.co.uk
| ~E-mail cis@c...
| ~
| ~CIS Deposit and Instant Access Savings Accounts are held with
| ~The Co-operative Bank p.l.c., registered in England and Wales
| ~number 990937, P.O. Box 101, 1 Balloon Street, Manchester M60
| ~4EP, and administered by CIS Policyholder Services Limited as
| ~agent of the Bank.
| ~
| ~CIS is a member of the General Insurance Standards Council
| ~
| ~CIS & the CIS logo (R) Co-operative Insurance Society Limited
| ~
| ~**************************************************************
|******************
| ~
| ~
|

Message #8 by wesley.fitzpatrick@s... on Fri, 28 Jun 2002 08:42:01 +0200
Good Idea, thanks.

 ~-----Original Message-----
 ~From: Leo Scott [mailto:leoscott@c...]
 ~Sent: Donnerstag, 27. Juni 2002 17:26
 ~To: Access
 ~Subject: [access] Re: MOVING records from one table to another
 ~
 ~
 ~Still use two queries.  Link the tables on a unique/key field 
 ~with an outer
 ~join where you select all records from the table you want to 
 ~move them from.
 ~Have a criteria where the joined field in the table the 
 ~records are going to
 ~are null.  Then from that base query make it an append query 
 ~to append into
 ~the receiving table.  Make another query that deletes the 
 ~same records from
 ~the table you are moving them from.  You can then take the 
 ~SQL from both
 ~queries and paste it into code to automate the process in the 
 ~future.  Much
 ~faster than writing loops.
 ~
 ~|-----Original Message-----
 ~|From: wesley.fitzpatrick@s...
 ~|[mailto:wesley.fitzpatrick@s...]
 ~|Sent: Thursday, June 27, 2002 4:52 AM
 ~|To: Access
 ~|Subject: [access] Re: MOVING records from one table to another
 ~|
 ~|
 ~|Thanks,
 ~|
 ~|I was really looking for a piece of code that would move the
 ~|records- what I
 ~|need to do is append the records to one table then delete 
 ~them off the
 ~|origional table. I wanted to be lazy but I guess I'll end up 
 ~doing it that
 ~|way anyway!
 ~|
 ~|Wes.
 ~|
 ~| ~-----Original Message-----
 ~| ~From: joe.dunn@c... [mailto:joe.dunn@c...]
 ~| ~Sent: Donnerstag, 27. Juni 2002 13:54
 ~| ~To: Access
 ~| ~Subject: [access] Re: MOVING records from one table to another
 ~| ~
 ~| ~
 ~| ~
 ~| ~<<Is there anyway of MOVING a set of records (based on
 ~| ~criteria) from one
 ~| ~table to another without doing a 'cut & paste' job of inserting and
 ~| ~deleting?>>
 ~| ~
 ~| ~Create an append query and set the criteria to select the 
 ~records you
 ~| ~require. Append the selected records to the target table.
 ~| ~
 ~| ~
 ~| ~
 ~| ~Must be same structure in both tables
 ~| ~
 ~| ~Could be issues with duplicate keys in target table!
 ~| ~
 ~| ~
 ~| ~**************************************************************
 ~| ~***********
 ~| ~
 ~| ~This e-mail may contain confidential information or be
 ~| ~privileged. It is intended to be read and used only by the
 ~| ~named recipient(s). If you are not the intended recipient(s)
 ~| ~please notify us immediately so that we can make arrangements
 ~| ~for its return: you should not disclose the contents of this
 ~| ~e-mail to any other person, or take any copies. Unless stated
 ~| ~otherwise by an authorised individual, nothing contained in
 ~| ~this e-mail is intended to create binding legal obligations
 ~| ~between us and opinions expressed are those of the 
 ~individual author.
 ~| ~
 ~| ~The CIS marketing group, which is regulated for Investment
 ~| ~Business by the Financial Services Authority, includes:
 ~| ~Co-operative Insurance Society Limited Registered in England
 ~| ~number 3615R - for life assurance and pensions
 ~| ~CIS Unit Managers Limited Registered in England and Wales
 ~| ~number 2369965  - for unit trusts and PEPs
 ~| ~CIS Policyholder Services Limited Registered in England and
 ~| ~Wales number 3390839 - for ISAs and investment products
 ~| ~bearing the CIS name
 ~| ~Registered offices: Miller Street, Manchester M60 0AL
 ~| ~Telephone  0161-832-8686   Internet  http://www.cis.co.uk
 ~| ~E-mail cis@c...
 ~| ~
 ~| ~CIS Deposit and Instant Access Savings Accounts are held with
 ~| ~The Co-operative Bank p.l.c., registered in England and Wales
 ~| ~number 990937, P.O. Box 101, 1 Balloon Street, Manchester M60
 ~| ~4EP, and administered by CIS Policyholder Services Limited as
 ~| ~agent of the Bank.
 ~| ~
 ~| ~CIS is a member of the General Insurance Standards Council
 ~| ~
 ~| ~CIS & the CIS logo (R) Co-operative Insurance Society Limited
 ~| ~
 ~| ~**************************************************************
 ~|******************
 ~| ~
 ~| ~
 ~|
 ~
 ~
 ~

  Return to Index