Cut and paste query

[firefly2k8]

I want to delete records from one table [Project Table] where there are blanks and add these records to a different existing table [Excluded Trades].

[Excluded Trades] has the same structure as [Project Table].

This query succesfully selects the records to be extracted:

Table [Excluded Trades] UNION ALL SELECT * FROM [SE2 Project Table] WHERE [Type Forward/Spot] = 'Forward' AND ([Forward Outright High] is null OR [Forward Outright Low] is null) AND [Project ID] = 61;

This query successfully deletes the records from [Project Table]

DELETE * FROM [SE2 Project Table] WHERE [Type Forward/Spot] = 'Forward' AND ([Forward Outright High] is null OR [Forward Outright Low] is null) AND [Project ID] = 61;

However the [Excluded Trades] table is not updated? The TABLE .. UNION.. query just acts like a SELECT.

Help much appreciated.

 

[firefly2k8]

Against all odds, I figured it out myself:

INSERT INTO [Excluded Trades]
SELECT *
FROM [SE2 Project Table]
WHERE [Type Forward/Spot]='Forward' And ([Forward Outright High] Is Null Or [Forward Outright Low] Is Null) And [Project ID]=61;

 

[Galaxiom]

Glad you worked it out.
However I would strongly advise you reconsider your field naming practices.

Spaces in fieldnames means they always have to be delimited with the sqaure bracket. Not only does this require more typing, the spaces make them more difficult to read because the eye must locate the brackets to parse the expression. Names without spaces are clearly a singe entity.

Many Access developers use CamelCase.

The use of special characters such as the slash is also a bad naming practice because they are operators. Their appearance in names is very distracting especially when trying to read expressions that involve arithmetic.

I would also recommend you normalize the [Type Forward/Spot] field. Instead of using the long text values, substitute them with integers or a single character that represents the text and use a lookup to display the value on forms and reports. This is much faster for Access than working with long string criteria.

 

[firefly2k8]

Thank you, i'll consider your observations re naming fields.