Crosstab Summary for Month & Year

[Catherine]

I am trying to create a report that will give me q total count by
month to date, and then another by year to date. Example:

Discharges By Type Month To Date Year To Date
Voluntary Quit 8 18
Involuntary Quit 2 11
Successful Completion 28 37

The "to date" value is supplied by the user at runtime. I know how to
do each type in seperate subreports that sit side by side, but they
would be autonomous. When one report had a value that the other did
not (for example, if there were no Voluntary Quits in a month, but
there were some for the year), the report rows wouldn't line up. Does
anyone know of a way I could get BOTH values in a single corsstab?

Any direction would be appreciated.

Catherine

[Bob Quintal]

Ca***************@yahoo.com (Catherine) wrote in
news:7e*************************@posting.google.co m:

I am trying to create a report that will give me q total count
by month to date, and then another by year to date. Example:

Discharges By Type Month To Date Year To Date
Voluntary Quit 8 18
Involuntary Quit 2 11
Successful Completion 28 37

The "to date" value is supplied by the user at runtime. I
know how to do each type in seperate subreports that sit side
by side, but they would be autonomous. When one report had a
value that the other did not (for example, if there were no
Voluntary Quits in a month, but there were some for the year),
the report rows wouldn't line up. Does anyone know of a way I
could get BOTH values in a single corsstab?

Any direction would be appreciated.

Catherine

I wouldn't consider using a crosstab. I'd just build two summary
queries, one returning the dischargetypes and yeartodate counts,
and the second for the monthly counts, then create a third query
that left joins the one with more categories to the one with
less, since the month is a subset of the year.

If you have a situation where you have holes in both queries,
create a union of all the categories and left join to each count
query.

--
Bob Quintal

PA is y I've altered my email address.

[Douglas J. Steele]

Do a Right Join (or Left Join, depending on what order to reference the
queries) between the two queries. Obviously YTD will include everything MTD
does plus, probably, more, so you want the option that includes ALL records
from YTD and only those records from MTD where the joined fields are equal.

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

"Catherine" <Ca***************@yahoo.com> wrote in message
news:7e*************************@posting.google.co m...

I am trying to create a report that will give me q total count by
month to date, and then another by year to date. Example:

Discharges By Type Month To Date Year To Date
Voluntary Quit 8 18
Involuntary Quit 2 11
Successful Completion 28 37

The "to date" value is supplied by the user at runtime. I know how to
do each type in seperate subreports that sit side by side, but they
would be autonomous. When one report had a value that the other did
not (for example, if there were no Voluntary Quits in a month, but
there were some for the year), the report rows wouldn't line up. Does
anyone know of a way I could get BOTH values in a single corsstab?

Any direction would be appreciated.

Catherine