By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,808 Members | 1,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,808 IT Pros & Developers. It's quick & easy.

convert query from sql Server to A2K

P: n/a
These queries work in SQL Server but not in Access. Can anyone see how I can
modify these so they will work in A2K

SELECT A.AdId,
COUNT(DISTINCT I.ImpressionId) AS Impressions,
COUNT(DISTINCT C.ClickId) AS Clicks
FROM tblAds A
LEFT JOIN tblImpressions I ON I.AdId = A.AdId
LEFT JOIN tblClicks C ON C.AdId = A.AdId
GROUP BY A.AdId

I understand Access doesn't have Count Distinct functionality but what about
the next query.

select
a.AdId
, isnull (i.Impressions, 0) Impressions
, isnull (c.Clicks, 0) Clicks
from
tblAds a
left join
(
select
AdId
, count (*) as Impressions
from
tblImpressions
group by
AdId
) i on i.AdId = a.AdId
left join
(
select
AdId
, count (*) as Clicks
from
tblClicks
group by
AdId
) c on c.AdId = a.AdId

Thanks to anyone who could help
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Ed
Terri:

Try this query:

SELECT a.AdId, nz([c].[NumClicks],0) AS Clicks, nz([i].[NumImpressions],0)
AS Impressions
FROM (tblAds AS a LEFT JOIN (SELECT tblClicks.AdId, Count(tblClicks.ClickID)
AS NumClicks
FROM tblClicks
GROUP BY tblClicks.AdId) AS c ON a.AdId = c.AdId) LEFT JOIN (SELECT
tblImpressions.AdId, Count(tblImpressions.ImpressionID) AS NumImpressions
FROM tblImpressions
GROUP BY tblImpressions.AdId) AS i ON a.AdId = i.AdId;

Note that the tablename prefixes for the fieldnames needed to be included in
the subqueries. Also, I used a different alias for the subqueries than you
did.

Good luck,
Ed

"Terri" <te***@cybernets.com> wrote in message
news:cl**********@reader2.nmix.net...
These queries work in SQL Server but not in Access. Can anyone see how I can modify these so they will work in A2K

SELECT A.AdId,
COUNT(DISTINCT I.ImpressionId) AS Impressions,
COUNT(DISTINCT C.ClickId) AS Clicks
FROM tblAds A
LEFT JOIN tblImpressions I ON I.AdId = A.AdId
LEFT JOIN tblClicks C ON C.AdId = A.AdId
GROUP BY A.AdId

I understand Access doesn't have Count Distinct functionality but what about the next query.

select
a.AdId
, isnull (i.Impressions, 0) Impressions
, isnull (c.Clicks, 0) Clicks
from
tblAds a
left join
(
select
AdId
, count (*) as Impressions
from
tblImpressions
group by
AdId
) i on i.AdId = a.AdId
left join
(
select
AdId
, count (*) as Clicks
from
tblClicks
group by
AdId
) c on c.AdId = a.AdId

Thanks to anyone who could help

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.