Include zero values in below query!

[daffykyle]

Hello all,

I'm busy doing some querying for a project I'm working on and need to include zero values in this query I've been beavering away on.

In summary the query brings up Emails, Phone Calls and Clicks for some clients based on their name, however in instances where the Email count is zero it isn't bringing up their stats even if they have a click count and a phone call count.

Can anybody help please? (and Thanks in advance) Query wording below:

SELECT DISTINCT JanuaryClients.Supplier, Nz(Count(JanuaryEmail.Supplier),0) AS Emails, Nz(JanuaryPhone.[Answered Calls],'0') AS Phone, Nz(DSum("Clicks","JanuaryClicks","[Action] like '*" & JanuaryClients.Tracking & "*'"),0) AS SumOfClicks
FROM JanuaryEmail INNER JOIN (JanuaryPhone INNER JOIN JanuaryClients ON JanuaryPhone.Number = JanuaryClients.Premium) ON JanuaryEmail.Supplier = JanuaryClients.Supplier
GROUP BY JanuaryClients.Supplier, JanuaryPhone.[Answered Calls], JanuaryClients.Tracking;

[hertfordkc]

Hello all,

I'm busy doing some querying for a project I'm working on and need to include zero values in this query I've been beavering away on.

In summary the query brings up Emails, Phone Calls and Clicks for some clients based on their name, however in instances where the Email count is zero it isn't bringing up their stats even if they have a click count and a phone call count.

Can anybody help please? (and Thanks in advance) Query wording below:

SELECT DISTINCT JanuaryClients.Supplier, Nz(Count(JanuaryEmail.Supplier),0) AS
count(Nz(JanuaryEmail.Supplier),0) AS
Emails, Nz(JanuaryPhone.[Answered Calls],'0') AS Phone, Nz(DSum("Clicks","JanuaryClicks","[Action] like '*" & JanuaryClients.Tracking &
The NZ(DSum needs to be switched also
"*'"),0) AS SumOfClicks
FROM JanuaryEmail INNER JOIN (JanuaryPhone INNER JOIN JanuaryClients ON JanuaryPhone.Number = JanuaryClients.Premium) ON JanuaryEmail.Supplier = JanuaryClients.Supplier
GROUP BY JanuaryClients.Supplier, JanuaryPhone.[Answered Calls], JanuaryClients.Tracking;

You have to resolve the nulls before you do anything else.

[daffykyle]

Hi Hertford, unfortunately this didnt seem to work.

Access suggests that not enough arguments were in the statement, so i changed the Nz to be in front of count again and the query ran, just didnt allocate the 0 to the counts where it couldn't find that supplier name.

[hertfordkc]

with each of the fields replaced by a NZ calculated field. After you get it working, go to SQL view and get the SQL.