When sub-query is used in the FROM clause, it can be viewed as selecting data from a virtual table because the result set of the sub-query is temporarily stored in memory and then used by the outer query as a table.
The query below is the inner query we just saw which shows a list of distinct values in the Reports To column.
When you select Append, you are prompted to enter the name of the table that you want to insert records: Specify the name of the table from the combo box. The query designer is similar to how you'd create a SELECT query.
You can specify criteria, create expressions, link between multiple tables and queries, etc.
In the query below, we first get all the distinct records in the sub-query (inner query) in the FROM clause for Reports To column.
This should give us a unique list of all the managers in Northwind company.
Syntax error (missing operator) in query expression 'count(distinct Reports To)'.
These code examples are divided by major subject such as queries, visual basic, report programming, etc.
On each of the subject pages we go into further detail about the specific examples contained in that subject.
The field names of the source and target tables do not need to match.
In fact, you can create expressions to combine fields, use VBA functions, etc., to insert new values into the table.