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.

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Impecunious" wrote:

> 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.
>
>

Mabye using a right join with a table of months?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Impecunious" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

Impecunious wrote:
> 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?

--
Joseph Meehan

Dia duit

See a similar discussion at:

http://www.utteraccess.com/forums/sh...Number=1271046

--
HTH
Van T. Dinh
MVP (Access)



"Impecunious" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>