2 left joins in access 2003

[Casper Page]

Hi all,

I am having an issue getting a query to work in access 2003. I have some
linked tables, from an sql 2003 server. I have written the T-SQL statement
and it works the way I need it to for the report:

select assigned, ae, createdby, typeofrequest, count(Typeofrequest) as
numberofrequests,
sum(tblSSmartMonthInvestment.Monthinvestment) as total
FROM tblSSmartRequest
LEFT JOIN tblSSmartMonthInvestment ON
tblSSmartMonthInvestment.requestid=tblssmartrequest.id
left join tblSSmartStageHistory on tblSSmartStageHistory.requestid =
tblssmartrequest.id
WHERE tblSSmartStageHistory.stage='Assigned'
and tblSSmartStageHistory.setdate between '20080401' and '20080403'
GROUP BY assigned, AE, Createdby, Typeofrequest
ORDER BY assigned, AE, Createdby, typeofrequest

when i move it into access i change it to the following:
select assigned, ae, createdby, typeofrequest, count(Typeofrequest) as
numberofrequests, sum(total) as total
from(
SELECT assigned, ae, createdby, dbo_tblSSmartMonthInvestment.Monthinvestment
as total, Typeofrequest
FROM dbo_tblssmartStagehistory, tblSSmartRequest
LEFT JOIN dbo_tblSSmartMonthInvestment ON
dbo_tblSSmartMonthInvestment.requestid=tblssmartrequest.id
WHERE dbo_tblSSmartStageHistory.stage='Assigned'
and dbo_tblSSmartStageHistory.setdate between #04/01/2008# and #04/03/2008#
and dbo_tblSSmartStageHistory.requestid = tblssmartrequest.id
) as information
GROUP BY assigned, AE, Createdby, Typeofrequest
ORDER BY assigned, AE, Createdby, typeofrequest

only to add the 'dbo_' since that is how the tables were named on linking,
and the # around the date fields. I get a syntax error on the second left
join. Any idea how to get around this? I tried a Union but couldnt get it to
work correctly.

any help is appreciated.

 

[bcap]

There's only 1 left join in the second query you posted!

To take the 1st query (the T-SQL one), you would need to add some
parentheses to get it to work in Access (the Access query engine isn't as
smart as SQL Server and needs a little help figuring out the joins):

select assigned, ae, createdby, typeofrequest, count(Typeofrequest) as
numberofrequests,
sum(tblSSmartMonthInvestment.Monthinvestment) as total
FROM (tblSSmartRequest
LEFT JOIN tblSSmartMonthInvestment ON
tblSSmartMonthInvestment.requestid=tblssmartrequest.id)
left join tblSSmartStageHistory on tblSSmartStageHistory.requestid =
tblssmartrequest.id
WHERE tblSSmartStageHistory.stage="Assigned"
and tblSSmartStageHistory.setdate between #04/01/2008# and #04/03/2008#
GROUP BY assigned, AE, Createdby, Typeofrequest
ORDER BY assigned, AE, Createdby, typeofrequest

Note also the changes I have made to the WHERE clause. Also, just because
Access prepends the schema name (dbo) when you link the tables, there's no
law that says you can't rename the tables in Access to get rid of it...

 

[Casper Page]

i had made changes to the query and forgot to undo them before posting. i had
tried to put parentheses but must have put them in the wrong place. thank you
very much.

i usually change the name of the tables when i link them, but the person who
had this job before me never did, so i'm slowly making everything standard.