Creating a date column "out of thin air"

[Impecunious]

I'm trying to query a count of transactions by month, regardless of
whether any transaction activity occurred during the month.
Consequently, I don't want to simply group by date and count the
instances of some other record property, because if there weren't any
transactions in June 2006 then this month won't show up in my results.
I'd prefer to see June with either a zero or null value for the count.

Is it possible to create a series of say, the past 12 month-ends,
without getting into a big nasty sysdate chain? In addition, where
would these dates come "from" - is there a dual equivalent in MS
Access?

Thanks in advance.

 

[Guest]

Dual? Obviously an Oracle dude!

If you display your data in a crosstab query, it's possible to make a column
show up even if there isn't any data for that month by using the Column
Heading property of the crosstab query.

Other than that you could create a table of Months and do a Left or Right
join to bring in this data even when there isn't any matching records in the
other table.

 

[Sylvain Lafontaine]

Mabye using a right join with a table of months?

 

[Joseph Meehan]

I'm trying to query a count of transactions by month, regardless of
whether any transaction activity occurred during the month.
Consequently, I don't want to simply group by date and count the
instances of some other record property, because if there weren't any
transactions in June 2006 then this month won't show up in my results.
I'd prefer to see June with either a zero or null value for the count.

Is it possible to create a series of say, the past 12 month-ends,
without getting into a big nasty sysdate chain? In addition, where
would these dates come "from" - is there a dual equivalent in MS
Access?

Thanks in advance.

Why not just add in 12 dummy records, one for each month and subtract
one from the count number?

 

[Van T. Dinh]

See a similar discussion at:

http://www.utteraccess.com/forums/showthreaded.php?Cat=&Number=1271046