Prev: Several dates for a date picker
Next: volare
From: Duncs on 27 Apr 2010 05:43
I have a group query that shows me the date and a count of all
transactions that happened on that date. When there are no
transactions for a particular date, it doesn't show me anything...as
you would probably expect. However what I would like it to do is, if
there are no entries for a date, show a zero instead.

Is it possible?

Duncs
From: ghetto_banjo on 27 Apr 2010 08:44
On Apr 27, 5:43 am, Duncs <true.kilted.s...(a)gmail.com> wrote:
> I have a group query that shows me the date and a count of all
> transactions that happened on that date.  When there are no
> transactions for a particular date, it doesn't show me anything...as
> you would probably expect.  However what I would like it to do is, if
> there are no entries for a date, show a zero instead.
>
> Is it possible?
>
> Duncs

From: ghetto_banjo on 27 Apr 2010 08:46
One way to do this, perhaps not the best way, is to create another
table that has a single field that holds all the date for the range
you are looking for. Then do a Left Join from that table to your
other table on the date fields, and then do a Count.

something like:

SELECT tblDates.myDate, Count(tblTableName.transDate) AS
CountOfTransDate
FROM tblDates LEFT JOIN tblTableName ON tblDates.myDate =
tblTableName.transDate
GROUP BY tblDates.myDate


From: golfinray on 27 Apr 2010 09:09
In your query, you can have an extra field with IIF([your transaction
field]is null,"0",[your transaction field])
--
Milton Purdy
ACCESS
State of Arkansas


"Duncs" wrote:

> I have a group query that shows me the date and a count of all
> transactions that happened on that date. When there are no
> transactions for a particular date, it doesn't show me anything...as
> you would probably expect. However what I would like it to do is, if
> there are no entries for a date, show a zero instead.
>
> Is it possible?
>
> Duncs
> .
>
From: ghetto_banjo on 27 Apr 2010 10:10
Milton,

He wants to show dates that do not exist in that table though. For
example, if he has a transactions on 4/25 and 4/27, he wants to show a
count of 0 for 4/26. The transaction field isn't null in this case,
it simply doesn't exist at all for that date.

 | 
Pages: 1
Prev: Several dates for a date picker
Next: volare