Moving data from one table to another

  • Thread starter Thread starter ZippyV
  • Start date Start date
Z

ZippyV

Hello everybody,
I need to create a query that (after it asked for the parameter "Insert
year") selects the right records from the original table, inserts them into
the archive table and deletes those records from the original table. My
problem is Access doesn't want to save the query because it got 2 statements
(Insert and Delete). Can I split this query into multiples without asking
twice for the 'Year' parameter?

So far I got this:

PARAMETERS [Which year?] Short;

INSERT INTO tblArchive(albumID, date)
SELECT albumID, date
FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));

DELETE FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));
 
Create an unbound form with a text box named txtYear and its Format property
set to General Number (so only numbers are accepted.)

Use it as a parameter in your query statements, e.g.:
PARAMETERS [Forms].[Form1].[txtYear] Short;

If you are automating this (running the queries by code), you might want to
wrap them in a transaction so you get an all or nothing result. Details in
article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
If you do that, you can probably build the SQL statements dynamically
(concatenating the year into the string) so you don't even need the saved
action queries.
 
Thanks for the article.
I assume it is not possible to do it using only sql queries?


Allen Browne said:
Create an unbound form with a text box named txtYear and its Format
property set to General Number (so only numbers are accepted.)

Use it as a parameter in your query statements, e.g.:
PARAMETERS [Forms].[Form1].[txtYear] Short;

If you are automating this (running the queries by code), you might want
to wrap them in a transaction so you get an all or nothing result. Details
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
If you do that, you can probably build the SQL statements dynamically
(concatenating the year into the string) so you don't even need the saved
action queries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ZippyV said:
Hello everybody,
I need to create a query that (after it asked for the parameter "Insert
year") selects the right records from the original table, inserts them
into the archive table and deletes those records from the original table.
My problem is Access doesn't want to save the query because it got 2
statements (Insert and Delete). Can I split this query into multiples
without asking twice for the 'Year' parameter?

So far I got this:

PARAMETERS [Which year?] Short;

INSERT INTO tblArchive(albumID, date)
SELECT albumID, date
FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));

DELETE FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));
Click to expand...
Click to expand...
 
You can do it with 2 queries, but you will have to enter the parameter twice
unless they both read it from the same place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ZippyV said:
Thanks for the article.
I assume it is not possible to do it using only sql queries?


Allen Browne said:
Create an unbound form with a text box named txtYear and its Format
property set to General Number (so only numbers are accepted.)

Use it as a parameter in your query statements, e.g.:
PARAMETERS [Forms].[Form1].[txtYear] Short;

If you are automating this (running the queries by code), you might want
to wrap them in a transaction so you get an all or nothing result.
Details in article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
If you do that, you can probably build the SQL statements dynamically
(concatenating the year into the string) so you don't even need the saved
action queries.


ZippyV said:
Hello everybody,
I need to create a query that (after it asked for the parameter "Insert
year") selects the right records from the original table, inserts them
into the archive table and deletes those records from the original
table. My problem is Access doesn't want to save the query because it
got 2 statements (Insert and Delete). Can I split this query into
multiples without asking twice for the 'Year' parameter?

So far I got this:

PARAMETERS [Which year?] Short;

INSERT INTO tblArchive(albumID, date)
SELECT albumID, date
FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));

DELETE FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));
Click to expand...
Click to expand...
Click to expand...
 
I believe I understand what you are trying to do, and Allen offers excellent
suggestions for that.

Now, I'm curious "why" you want to do that. As in "why are you removing
records from one table and adding them to another?" I get the notion of
"archiving", but it may not be necessary to move the records at all.

For example, if your table had a date/time field (named, perhaps,
[DateArchived]), you could effectively archive a given record by:

1. putting a date (or date/time) in that new field
2. modifying your "active record" query to exclude records that have a
value in [DateArchived]

This would also make looking up "old" records much easier, as well as
comparing counts/averages/etc. over time.

Have you run into a size/growth issue?
 
Why? It's part of a project I'm making for school. I'm only trying to solve
the exercises they gave me. It's not going to be a database used in a
production environment.
If it was up to me I would never archive records like this unless the table
would be VERY big.

I do appreciate all the help I get from the MVP's on the MS newsgroups.
There is no other place to get decent and fast responses for my Access
questions/problems.


Jeff Boyce said:
I believe I understand what you are trying to do, and Allen offers
excellent
suggestions for that.

Now, I'm curious "why" you want to do that. As in "why are you removing
records from one table and adding them to another?" I get the notion of
"archiving", but it may not be necessary to move the records at all.

For example, if your table had a date/time field (named, perhaps,
[DateArchived]), you could effectively archive a given record by:

1. putting a date (or date/time) in that new field
2. modifying your "active record" query to exclude records that have a
value in [DateArchived]

This would also make looking up "old" records much easier, as well as
comparing counts/averages/etc. over time.

Have you run into a size/growth issue?

--
Regards

Jeff Boyce
<Office/Access MVP>

ZippyV said:
Hello everybody,
I need to create a query that (after it asked for the parameter "Insert
year") selects the right records from the original table, inserts them into
the archive table and deletes those records from the original table. My
problem is Access doesn't want to save the query because it got 2 statements
(Insert and Delete). Can I split this query into multiples without asking
twice for the 'Year' parameter?

So far I got this:

PARAMETERS [Which year?] Short;

INSERT INTO tblArchive(albumID, date)
SELECT albumID, date
FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));

DELETE FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));
Click to expand...
Click to expand...
 
School exercises generally have (should have) a real-world purpose. If your
instructor can accept questions without feeling threatened, consider asking
why a record would need to be archived in a separate table. Of course, only
consider doing that after completing your assignment <g>.

--
Regards

Jeff Boyce
<Office/Access MVP>

ZippyV said:
Why? It's part of a project I'm making for school. I'm only trying to solve
the exercises they gave me. It's not going to be a database used in a
production environment.
If it was up to me I would never archive records like this unless the table
would be VERY big.

I do appreciate all the help I get from the MVP's on the MS newsgroups.
There is no other place to get decent and fast responses for my Access
questions/problems.


"Jeff Boyce" <[email protected]_HYPHEN_TO_END> schreef in bericht
I believe I understand what you are trying to do, and Allen offers
excellent
suggestions for that.

Now, I'm curious "why" you want to do that. As in "why are you removing
records from one table and adding them to another?" I get the notion of
"archiving", but it may not be necessary to move the records at all.

For example, if your table had a date/time field (named, perhaps,
[DateArchived]), you could effectively archive a given record by:

1. putting a date (or date/time) in that new field
2. modifying your "active record" query to exclude records that have a
value in [DateArchived]

This would also make looking up "old" records much easier, as well as
comparing counts/averages/etc. over time.

Have you run into a size/growth issue?

--
Regards

Jeff Boyce
<Office/Access MVP>

ZippyV said:
Hello everybody,
I need to create a query that (after it asked for the parameter "Insert
year") selects the right records from the original table, inserts them into
the archive table and deletes those records from the original table. My
problem is Access doesn't want to save the query because it got 2 statements
(Insert and Delete). Can I split this query into multiples without asking
twice for the 'Year' parameter?

So far I got this:

PARAMETERS [Which year?] Short;

INSERT INTO tblArchive(albumID, date)
SELECT albumID, date
FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));

DELETE FROM tblOriginal
WHERE ((tblOriginal.date)=Year([Which year?]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top