Prev: Count if Text is Equal in two Fields
Next: using dates
From: Ck1 on 24 Mar 2010 18:19
I have a table that lists business summary data by day.
Not every business has information on each day.
However I need to pull in the business along with zero values when this
happens.
I created a table listing all of the business names and did a join, however
it is still not pulling in the businesses without any information on that day.

Here is my query - any help would be appreciated.

SELECT [Outbound Business Names].[Business Name], [Dialer Summary
results].RowDate
FROM [Dialer Summary results] RIGHT JOIN [Outbound Business Names] ON [
Dialer Summary results].Business = [Outbound Business Names].[Business Name]
GROUP BY [Outbound Business Names].[Business Name], [Outbound Business
Names].MetricsBusinessName, [Eden Prairie Dialer Summary results by
business].RowDate
HAVING ((([Dialer Summary results].RowDate)="03/20/2010"));
From: Douglas J. Steele on 24 Mar 2010 18:30
If there's no data in [Dialer Summary results] for a given business, the
Right Join is going to return Null for RowDate.

See whether this works:

HAVING Nz([Dialer Summary results].RowDate, "03/20/2010")="03/20/2010"

Of course, if RowDate is actually a date field and not a text field, that
should be

HAVING Nz([Dialer Summary results].RowDate, #03/20/2010#)=#03/20/2010#

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Ck1" <Ck1(a)discussions.microsoft.com> wrote in message
news:E284AA57-3BB5-4149-AA82-4C9E72622BE4(a)microsoft.com...
>I have a table that lists business summary data by day.
> Not every business has information on each day.
> However I need to pull in the business along with zero values when this
> happens.
> I created a table listing all of the business names and did a join,
> however
> it is still not pulling in the businesses without any information on that
> day.
>
> Here is my query - any help would be appreciated.
>
> SELECT [Outbound Business Names].[Business Name], [Dialer Summary
> results].RowDate
> FROM [Dialer Summary results] RIGHT JOIN [Outbound Business Names] ON [
> Dialer Summary results].Business = [Outbound Business Names].[Business
> Name]
> GROUP BY [Outbound Business Names].[Business Name], [Outbound Business
> Names].MetricsBusinessName, [Eden Prairie Dialer Summary results by
> business].RowDate
> HAVING ((([Dialer Summary results].RowDate)="03/20/2010"));


From: KARL DEWEY on 24 Mar 2010 19:02
Try this --
HAVING ((([Dialer Summary results].RowDate)="03/20/2010")) OR ([Dialer
Summary results].RowDate) Is Null;

--
Build a little, test a little.


"Ck1" wrote:

> I have a table that lists business summary data by day.
> Not every business has information on each day.
> However I need to pull in the business along with zero values when this
> happens.
> I created a table listing all of the business names and did a join, however
> it is still not pulling in the businesses without any information on that day.
>
> Here is my query - any help would be appreciated.
>
> SELECT [Outbound Business Names].[Business Name], [Dialer Summary
> results].RowDate
> FROM [Dialer Summary results] RIGHT JOIN [Outbound Business Names] ON [
> Dialer Summary results].Business = [Outbound Business Names].[Business Name]
> GROUP BY [Outbound Business Names].[Business Name], [Outbound Business
> Names].MetricsBusinessName, [Eden Prairie Dialer Summary results by
> business].RowDate
> HAVING ((([Dialer Summary results].RowDate)="03/20/2010"));
From: John Spencer on 25 Mar 2010 08:53
The problem is that you are negating the right join by applying criteria to
the left table.

Your table and field names mean that you are going to have to nest queries to
get the desired results. By the way I don't see how the query as posted would
work at all. You are grouping by a table
[Eden Prairie Dialer Summary results by business]
that does not exist in the FROM clause. It is always a good idea to copy the
SQL statement of the real query and post that.

First query would look something like the following.
SELECT Business, [Dialer Summary results].RowDate
FROM [Dialer Summary results]
WHERE [Dialer Summary results].RowDate="03/20/2010"

The quotes around the Date value look suspicious also. If the field RowDate
is a date field then the expression should be .RowDate = #03/20/2010#

The Second query would use the first query and your OutBound Business Names table

SELECT DISTINCT [Outbound Business Names].[Business Name]
, [QueryOne].RowDate
FROM [QueryOne] RIGHT JOIN [Outbound Business Names]
ON [QueryOne].Business = [Outbound Business Names].[Business Name]
WHERE((([Dialer Summary results].RowDate)="03/20/2010"));


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ck1 wrote:
> I have a table that lists business summary data by day.
> Not every business has information on each day.
> However I need to pull in the business along with zero values when this
> happens.
> I created a table listing all of the business names and did a join, however
> it is still not pulling in the businesses without any information on that day.
>
> Here is my query - any help would be appreciated.
>
> SELECT [Outbound Business Names].[Business Name], [Dialer Summary
> results].RowDate
> FROM [Dialer Summary results] RIGHT JOIN [Outbound Business Names] ON [
> Dialer Summary results].Business = [Outbound Business Names].[Business Name]
> GROUP BY [Outbound Business Names].[Business Name], [Outbound Business
> Names].MetricsBusinessName, [Eden Prairie Dialer Summary results by
> business].RowDate
> HAVING ((([Dialer Summary results].RowDate)="03/20/2010"));
 | 
Pages: 1
Prev: Count if Text is Equal in two Fields
Next: using dates