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
|