IpbWiki Core is not installed yet. ipbwiki class not installed, sorry auto-authentication is not possible... Action Queries - Access wiki - Access Help and How-to - Microsoft Office
  Main Page | Recent changes | Edit this page | Page history

Printable version | Disclaimers | Privacy policy | Current revision

Not logged in
Log in | Help
 

Action Queries

Revision as of 22:59, 18 January 2015; view current revision
←Older revision | Newer revision→

Action Queries

 <td style="background-color:Gray; width: 5px; height: 50px;"</td>
This page is a stub. Please help us by expanding or merging it with applicable content
 <td style="background-color:Blue; width: 5px; height: 50px;"</td>
This page is under consideration for merging with: Calling Action Queries, RunSQL vs Execute

Related Content:
    RunSQL vs Execute
    Calling Action Queries


Contents

Basic Examples



Delete Queries


ACE Syntax [sql]DELETE * FROM tablename WHERE criteria[/sql]

ANSI Standard Syntax [sql]DELETE Field1, Field2 FROM tablename WHERE criteria[/sql]
You can delete all the data from a given table by supplying all (*) fields and no criteria. This is useful for temporary tables: [sql]DELETE * FROM tablename[/sql]

Note that the non-standard syntax allow you to join several tables and therefore specify which rows of single table to be deleted. Only one table's content can be deleted per a delete query: [sql]DELETE o.* FROM tblOrders AS o INNER JOIN tblCustomers AS c WHERE c.Inactive <> 0;[/sql] The query will delete all orders for inactive customers but will not delete the data about those inactive customers.

To achieve similar objective using standard syntax, which does not allow joining on a delete query, would be: [sql]DELETE FROM tblOrders WHERE EXISTS (

  SELECT NULL
  FROM tblCustomers
  WHERE Inactive <> 0 AND CustomerID = tblOrders.CustomerID

);[/sql]

Append Queries


There are two distinct syntaxes: a single record append query and a multiple record append query.

An example of a single record append query would be: [sql]INSERT INTO Tablename (Field1, Field2) VALUES (Value1, Value2)[/sql]

An example of a multiple record append query would be: [sql]INSERT INTO TargetTablename ("Field1, field2") SELECT FieldA, FieldB FROM SourceTablename[/sql]

Note that the SELECT portion of the multiple record append query can be any valid SQL statement.

Update Queries


[sql]UPDATE Tablename SET Field1=Value1, Field2=Value2 WHERE criteria[/sql]


Make Table Queries


[SQL]SELECT Tablename.Field1, Tablename.Field2, Tablename.Field3 INTO Newtable FROM Tablename;[/SQL]

Calling Methods

RunSQL vs Execute


[Main Page]

Edit this page
Discuss this page
Page history
What links here
Related changes

Special pages
Bug reports