Skip to main content
Database Administrators

Return to Question

deleted 213 characters in body
Source Link
Juan Velez
Juan Velez
  • 3.3k
  • 18
  • 55
  • 74

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below:

  SELECT * 
    FROM 
    --MyOtherTable is a "real" table
    MyOtherTable INNER JOIN
    --MyTable is a "real" table. MyDerivedTable is a "pseudo" table derived from MyTable
    (SELECT * FROM MyTable WHERE MyID >10) AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.

I found this link which seems to suggest that it is not possible....

http://www.access-programmers.co.uk/forums/showthread.php?t=78833

Can someone please confirm? Thanks in advance.

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below:

  SELECT * 
    FROM 
    --MyOtherTable is a "real" table
    MyOtherTable INNER JOIN
    --MyTable is a "real" table. MyDerivedTable is a "pseudo" table derived from MyTable
    (SELECT * FROM MyTable WHERE MyID >10) AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below:

  SELECT * 
    FROM 
    --MyOtherTable is a "real" table
    MyOtherTable INNER JOIN
    --MyTable is a "real" table. MyDerivedTable is a "pseudo" table derived from MyTable
    (SELECT * FROM MyTable WHERE MyID >10) AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.

I found this link which seems to suggest that it is not possible....

http://www.access-programmers.co.uk/forums/showthread.php?t=78833

Can someone please confirm? Thanks in advance.

deleted 213 characters in body
Source Link
Juan Velez
Juan Velez
  • 3.3k
  • 18
  • 55
  • 74

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008?

For example in SQL I am able to write and execute the below query which joins a derived table named dervtbl1 tohave an actual table named tblCust. I then use the result of my inner query to create a second derived table named MainDervTable which is where My main SELECT statement pulls the data fromexample below:

select  SELECT * from

(    FROM 
Select recID,name,city,state from tblCust where--MyOtherTable stateis =a 'FL'
inner"real" join
(table
select * from tblEmp where stateMyOtherTable =INNER 'FL'JOIN
) as dervtbl1 on tblCust.recID--MyTable =is dervtbl1a "real" table.recID 
 MyDerivedTable is a "pseudo" table derived from MyTable
    (SELECT * FROM MyTable WHERE MyID >10)
  AS MyDerivedTable ON
as MainDervTable   MyOtherTable.ID = MyDerivedTable.ID

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008?

For example in SQL I am able to write and execute the below query which joins a derived table named dervtbl1 to an actual table named tblCust. I then use the result of my inner query to create a second derived table named MainDervTable which is where My main SELECT statement pulls the data from:

select * from

(
Select recID,name,city,state from tblCust where state = 'FL'
inner join
(
select * from tblEmp where state = 'FL'
) as dervtbl1 on tblCust.recID = dervtbl1.recID 
 
 )
 
as MainDervTable

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below:

  SELECT * 
    FROM 
    --MyOtherTable is a "real" table
    MyOtherTable INNER JOIN
    --MyTable is a "real" table. MyDerivedTable is a "pseudo" table derived from MyTable
    (SELECT * FROM MyTable WHERE MyID >10) AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.

Source Link
Juan Velez
Juan Velez
  • 3.3k
  • 18
  • 55
  • 74

Is it possible to join derived tables to actual in Access 2007

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008?

For example in SQL I am able to write and execute the below query which joins a derived table named dervtbl1 to an actual table named tblCust. I then use the result of my inner query to create a second derived table named MainDervTable which is where My main SELECT statement pulls the data from:

select * from

(
Select recID,name,city,state from tblCust where state = 'FL'
inner join
(
select * from tblEmp where state = 'FL'
) as dervtbl1 on tblCust.recID = dervtbl1.recID 

 )

as MainDervTable

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.