Showing 0's in a group query

[Duncs]

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

 

[ghetto_banjo]

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

 

[ghetto_banjo]

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

 

[golfinray]

In your query, you can have an extra field with IIF([your transaction
field]is null,"0",[your transaction field])

 

[ghetto_banjo]

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.