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

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

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-

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