Hi all

I am trying to rebuild a database that was recently lost and we have managed to create all the tables etc again without any problems but we are having problems with our query. This query worked fine in the TOAD software that we used previously but we are now trying to run the query in Access 2007....

Can anyone help the query is below, we have tried a few alternatives by taking away first line and inserting the AS terms in appropriate places our best query to date is below...

SELECT a.Adkey,
a.MediaType,
a.Publication,
a.Region,
a.PublicationGenre,
a.Date,
a.IssueDate,
a.Size,
a.Creative,
a.Section,
a.Insertions,
a.Circulation,
a.GrossCost,
a.AdCost,
a.FinalClientCost,
COALESCE(b.leads,0) AS TotalLeads,
COALESCE(c.tsleads,0) AS TSLeads,
COALESCE(d.mailleads,0) AS MailLeads,
COALESCE(e.enrols,0) AS TotalEnrols,
COALESCE(f.tsenrols,0) AS TSEnrols,
COALESCE(f.tsdeposit,0) AS TSEnrolsDeposit,
COALESCE(f.tsGEV,0) AS TSEnrolsGEV,
COALESCE(g.mailenrols,0) AS MailEnrols,
COALESCE(g.maildeposit,0) AS MailEnrolsDeposit,
COALESCE(g.mailGEV,0) AS MailEnrolsGEV
FROM tbl_campaigns AS a
LEFT JOIN
(SELECT Adkey, COUNT(Adkey) AS leads
FROM tbl_leads GROUP BY Adkey) AS b ON a.Adkey=b.Adkey
LEFT JOIN
(SELECT Adkey, COUNT(Adkey) AS tsleads
FROM tbl_leads AS l
LEFT JOIN tbl_source AS s
ON l.LeadSource = s.SourceCode
WHERE s.Source="TS"
GROUP BY Adkey) AS c ON a.Adkey=c.Adkey
LEFT JOIN
(SELECT Adkey, COUNT(Adkey) AS mailleads
FROM tbl_leads AS l
LEFT JOIN tbl_source AS s
ON l.LeadSource = s.SourceCode
WHERE s.Source="Mail"
GROUP BY Adkey) AS d ON a.Adkey=d.Adkey
LEFT JOIN
(SELECT Adkey, COUNT(Adkey) AS enrols, SUM(Deposit) AS deposit, SUM(GEV) AS GEV
FROM tbl_enrols GROUP BY Adkey) AS e ON a.Adkey=e.Adkey
LEFT JOIN
(SELECT Adkey, COUNT(Adkey) AS tsenrols, SUM(Deposit) AS tsdeposit, SUM(GEV) AS tsGEV
FROM tbl_enrols AS e
LEFT JOIN tbl_source AS s
ON e.EnrolSource = s.SourceCode
WHERE s.Source="TS"
GROUP BY Adkey) AS f ON a.Adkey=f.Adkey
LEFT JOIN
(SELECT Adkey, COUNT(Adkey) AS mailenrols,SUM(Deposit) AS maildeposit, SUM(GEV) AS mailGEV
FROM tbl_enrols AS e
LEFT JOIN tbl_source AS s
ON e.EnrolSource = s.SourceCode
WHERE s.Source="Mail"
GROUP BY Adkey) AS g ON a.Adkey=g.Adkey
GROUP BY a.Adkey


Can anyone spot where the operators are missing or any other glaring errors that we cant see

Thanks in advance

Mills,

Coalesce is a SQL Server function. Exactly what errors are
you getting?

In a purely Access environment, you can change Coalesce to
the Nz function.

Wayne

Hi Wayne, the error message I am getting at the moment is a synatax error and the second LEFT JOIN statement where it says
LEFT JOIN
(SELECT Adkey, COUNT(Adkey) AS tsleads
FROM tbl_leads AS l
LEFT JOIN tbl_source AS s
ON l.LeadSource = s.SourceCode
WHERE s.Source="TS"
GROUP BY Adkey) AS c ON a.Adkey=c.Adkey

is the area that is highlighted. However there doesnt seem to be a problem with any of the coding in this section.

Cheers