MS SQL Server vs MS Access SQL

Hello:

I have a problem with my query. I build my query with SQL Server and I nned to use this query in Access too, but when I try to run the query in Access an error is showed: "Join expression is not supported"

My query is the following, has a temporaly table and this tables is joinning with other tables with two conditions:

SELECT DISTINCT TM_User.LastName , TM_User.FirstName, TE_ExpenseCapture.Type, TM_Project.Title as ProjectTitle, TM_Project.Code as ProjectCode, TM_Category.DefValue as TeamDesc, SumPeriodCost.PeriodCos
FROM ((((((TE_ExpenseSheet INNER JOIN TE_ExpenseCapture ON TE_ExpenseSheet.ID = TE_ExpenseCapture.ExpenseSheetID)
INNER JOIN TM_Resources ON TE_ExpenseSheet.ResourceID = TM_Resources.ID)
INNER JOIN TM_User ON TM_Resources.UserID = TM_User.ID)
INNER JOIN TM_Project ON TE_ExpenseCapture.ProjectID = TM_Project.ID)
INNER JOIN TM_Category ON TM_Resources.TeamID = TM_Category.ID)
LEFT JOIN (SELECT Sum(TE_ExpenseCapture.Cost) as PeriodCost, TE_ExpenseSheet.ResourceID, TE_ExpenseCapture.ProjectID FROM TE_ExpenseCapture INNER JOIN TE_ExpenseSheet ON TE_ExpenseCapture.ExpenseSheetID = TE_ExpenseSheet.ID WHERE TE_ExpenseSheet.Status = 3 AND TE_ExpenseCapture.Type =1 AND TE_ExpenseCapture.ChargeDate between '1/04/2005' and '04/04/2005' AND TE_ExpenseCapture.CostType = 0 GROUP BY TE_ExpenseSheet.ResourceID, TE_ExpenseCapture.ProjectID) SumPeriodCost ON TE_ExpenseSheet.ResourceID = SumPeriodCost.ResourceID AND TE_ExpenseCapture.ProjectID = SumPeriodCost.ProjectID)

I think that the problem is with the double condition (marqued with red in the query).
Can you help me please? How can I use this double condition (neccesary) in MS Access?

Thanks a lot
Regards,

Elizabeth

Are you going through Access to an SQL server?

You can use a pass-through query.

Create a new query in Access, go to Design View, Right click and select "Query Type" change it to Pass-Through. Then paste your SQL Query.

Also, in design view, you will have to setup the ODBC in the paramaters box (F4).

Thanks for your response, but I need that the same query run in SQL Server and Access, I use the query that I send in the first trhead in SQL Server and run ok, now I need to represent the same in Access with the double relation with the summarized table,.Do you have any idea to to this?

Regards,
Elizabeth

MS Access Jet is different than SQL, but you can use a Pass-Through query to execute the MS SQL query through Access. See my post above.

Thanks other time,
I try that you say in your first reply (The past-througt) , but when I try to run there is where the error that I write in my first thread is showed: "Join expression is not supported" and I think that is for the double condition that I put in the las join of my query that is the Left Join.

Do you know how to manage this double condition in the join in MS Acces? please?

Thanks a lot

Elizabeth