May be this one reason , public are using SQL Server to store the data instead of MS ACCESS .

Intresting thing to know.

The real thing is not that you can't use derived tables but that you must have a table name in a select statement. The following by itself is invalid:

SELECT 1;

However, if you create a table in your database called OneRow (or for Oracle style, call it DUAL) and put just one row in it, then you can do this:

SELECT 1 FROM OneRow UNION ALL SELECT 2 FROM OneRow;

Now that you have cleared this barrier, you can use derived tables just fine in Access. In the default SQL syntax mode, it is done this way (Number is a reserved word so I used Num):

SELECT T.*
FROM [
   SELECT 1 AS Num FROM OneRow UNION ALL SELECT 2 FROM OneRow
]. AS T;

Note particularly the period after the closing square bracket. Also you can't use square brackets in the embedded query, so you can't use column names that require quoting, but that's a bad idea to have any of those in your database in the first place.

Finally, If you switch your database to use ANSI 92 syntax, then you can use the familiar syntax, though the original syntax will still work:

SELECT T.*
FROM (
   SELECT 1 AS Num FROM OneRow UNION ALL SELECT 2 FROM OneRow
) AS T;

In Access 2007, click on the Home button, select "Access Options", click on "Object Designers" and in the "Query Design" section check the option "SQL Server Compatible Syntax (ANSI 92)". This option can't be changed if you have any queries open, and perhaps in other situations, but if you are having problems you can create a new database, set the option, and import everything from the old database.

ErikEckhardt, I am aware of the points you have given. But the thing about version 2007 is new to me. Thanks for the feedback

Being able to use SQL 92 syntax is not new, it's available in Access 2003 and possibly earlier versions as well.