Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 2nd, 2006, 07:24 AM
Registered User
Join Date: Aug 2006
Location: , , United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Append Query issue

Hi all

I have a problem with the above mentioned subject. Basically I have been tasked to create a database of personel records and one of the items that needs to be done is to archive records to another table when that person leaves the company.

After loking around I found out that I should use the append query to push the data to a identical table (just with a different name). However upon creating an apend query it pushes all the data in the main table to the archived one. I only want the particular record i'm viewing to be archived - is this possible, if so would someone be so kind as to put me out of my misery please.:(

Apologies if this seems really basic but I haven't played with Access for years and even then it was more basic databases.

Many thanks in advance

Reply With Quote
  #2 (permalink)  
Old August 2nd, 2006, 10:10 PM
Authorized User
Join Date: Apr 2006
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts

Just add a parameter filter to your append query that identifies only the record you are looking at before you do the append.

Reply With Quote
  #3 (permalink)  
Old August 3rd, 2006, 10:10 AM
pjm pjm is offline
Authorized User
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts

Using my own hastily designed employee table:

INSERT INTO [Employees-Archived]
    ( SSN, [First Name], [Last Name], [Job Title], [Other Stuff] )
SELECT [Employees-Current].SSN,
    [Employees-Current].[First Name],
    [Employees-Current].[Last Name],
    [Employees-Current].[Job Title],
    [Employees-Current].[Other Stuff]
FROM [Employees-Current]
WHERE ((([Employees-Current].SSN)=[SSN?]));

The second part of the task may be to delete the employee you just archived from
the original table:

DELETE [Employees-Current].SSN
FROM [Employees-Current]
WHERE ((([Employees-Current].SSN)=[SSN?]));

You would have to key in the SSN for each query but hopefully you won't have to do
this too often (unless you've got a lot of turnover there).

Reply With Quote
  #4 (permalink)  
Old August 7th, 2006, 10:18 AM
Registered User
Join Date: Aug 2006
Location: , , United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks very much Phil, although I do have a question arising from that solution.

I got the first bit to work (the INSERT INTO bit) however I can't get the DELETE bit to work.

Do you have this as a seperate query or do you place it onto the end of the INSERT INTO SQL statement?

I've tried placing on the end of the INSERT INTO section and I get back an error and I'm not sure what it means:

"Syntax error (missing operator)in query expression......"

Below is what the statement is currently at:

INSERT INTO [Brizlee Archive] ( ID, Name, Initials, First_Name, Rank, Service_No, Trade, DOB, Address, Address_Off_Duty, Telephone1, Telephone2, Mobile, [Spouse/Partner], Children, Interests, Date_updated, Notes, [Last Fitness test], [Fitness Test], [Fitness Exempt], [Remedial Training], [Last CCS], [CCS Booked], [Last GT], [GT Booked], [Posted to Boulmer], [Fire Training], Comment, [Secondary Duties], [Received On], [Handed to Subject], [First RO], [First RO Received], [First RO to Subject], [Second RO], [Second RO Received], [Third RO], [Third RO Received], [Sent To PSF On], [No 1 Style], [No 1 Fit], [Last Inspected], [Other Info], [Image] )
SELECT [Brizlee Wood].ID, [Brizlee Wood].Name, [Brizlee Wood].Initials, [Brizlee Wood].First_Name, [Brizlee Wood].Rank, [Brizlee Wood].Service_No, [Brizlee Wood].Trade, [Brizlee Wood].DOB, [Brizlee Wood].Address, [Brizlee Wood].Address_Off_Duty, [Brizlee Wood].Telephone1, [Brizlee Wood].Telephone2, [Brizlee Wood].Mobile, [Brizlee Wood].[Spouse/Partner], [Brizlee Wood].Children, [Brizlee Wood].Interests, [Brizlee Wood].Date_updated, [Brizlee Wood].Notes, [Brizlee Wood].[Last Fitness test], [Brizlee Wood].[Fitness Test], [Brizlee Wood].[Fitness Exempt], [Brizlee Wood].[Remedial Training], [Brizlee Wood].[Last CCS], [Brizlee Wood].[CCS Booked], [Brizlee Wood].[Last GT], [Brizlee Wood].[GT Booked], [Brizlee Wood].[Posted to Boulmer], [Brizlee Wood].[Fire Training], [Brizlee Wood].Comment, [Brizlee Wood].[Secondary Duties], [Brizlee Wood].[Received On], [Brizlee Wood].[Handed to Subject], [Brizlee Wood].[First RO], [Brizlee Wood].[First RO Received], [Brizlee Wood].[First RO to Subject], [Brizlee Wood].[Second RO], [Brizlee Wood].[Second RO Received], [Brizlee Wood].[Third RO], [Brizlee Wood].[Third RO Received], [Brizlee Wood].[Sent To PSF On], [Brizlee Wood].[No 1 Style], [Brizlee Wood].[No 1 Fit], [Brizlee Wood].[Last Inspected], [Brizlee Wood].[Other Info], [Brizlee Wood].Image
FROM [Brizlee Wood]
WHERE ((([Brizlee Wood].Service_No)=[Enter Serivce Number of Record to be Archived:]))
DELETE from [Brizlee Wood]
WHERE ((([Brizlee Wood].Service_No)=[Enter Serivce Number of Record to be Archived:]));

Apologies for it being sooooooo long (probably not the best way to create a database :( )

Again many thanks for your help

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append Query bright_mulenga Access 1 January 3rd, 2007 01:33 PM
Append Query Cybersurfer Access 1 February 13th, 2006 01:02 PM
append query? bph Access 2 November 23rd, 2004 12:44 PM
Append Query Criteria Ben Access 1 January 21st, 2004 07:24 AM
append query stoneman Access 2 November 12th, 2003 09:17 PM

All times are GMT -4. The time now is 04:35 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.