Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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

  #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
Default

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

  #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
Default

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).


-Phil-
  #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
Default

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



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





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