Bi-monthly report

J

Jason Byrnes

I'm trying to put together a Bi-monthly crosstab query. So far I've figured
ho to get the results I want on a monthly or quartly basis, but I cannot
figure out how to put together the Bi-monthly Query.
For the Monthly query I have:

TRANSFORM Sum(tblInsect_Event.Number) AS SumOfNumber
SELECT Format([DateFound],"mmm") AS Expr2
FROM tblInsect_Event
GROUP BY Month([DateFound]), Format([DateFound],"mmm")
PIVOT Year([DateFound]);

and for the Quartly query:

TRANSFORM Sum(tblInsect_Event.Number) AS SumOfNumber
SELECT "Qtr" & DatePart("q",[DateFound],1,0) AS Expr2
FROM tblInsect_Event
GROUP BY "Qtr" & DatePart("q",[DateFound],1,0)
PIVOT Year([DateFound]);

How can I change this to give me Bi-monthly totals?

Thanks
Jason
--
~~~~~~~~~~
__o
_-\<,_ Jason Byrnes
(_)/ (_)
~~~~~~~~~~
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Wouldn't it be better to have the Year as the Row header and the
Month/Qtr as the Column header? E.g.:

Jan Feb Mar ... Qtr1 Qtr2 Qtr3 ...
2005
2004
2003 ...

That'd mean the PIVOT clause would change like this:

Month:
PIVOT Format(DateFound,"mmm") IN ("Jan", "Feb", "Mar", ...)

Qtr:
PIVOT "Qtr" & DatePart("q", DateFound)

Bi-monthly (1-15 & 16-end of month): === UNTESTED
PIVOT Format(DateFound, "mmm") & IIf(Day(DateFound)<16,"_A", "_B")
IN ("Jan_A", "Jan_B", "Feb_A", "Feb_B", ... )

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgvce4echKqOuFEgEQKfmQCghKchqy94IIQClnYo/EX5FPKqeV0AoKC4
UjszAXXBLOj+O1ab4ftkRAPx
=RAPv
-----END PGP SIGNATURE-----
 
J

Jason Byrnes

Im sorry, I guess I didnt explain myself very well. I'm looking for the
result set to give me the acumulated totals for every 2 months. E.g:
Jan/Feb Mar/April June/July
2005
2004
2003 ...

As far as witch I pivot on, It doesnt realy matter much to me as I am using
this as the basis of a chart.

Thank you
Jason


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Wouldn't it be better to have the Year as the Row header and the
Month/Qtr as the Column header? E.g.:

Jan Feb Mar ... Qtr1 Qtr2 Qtr3 ...
2005
2004
2003 ...

That'd mean the PIVOT clause would change like this:

Month:
PIVOT Format(DateFound,"mmm") IN ("Jan", "Feb", "Mar", ...)

Qtr:
PIVOT "Qtr" & DatePart("q", DateFound)

Bi-monthly (1-15 & 16-end of month): === UNTESTED
PIVOT Format(DateFound, "mmm") & IIf(Day(DateFound)<16,"_A", "_B")
IN ("Jan_A", "Jan_B", "Feb_A", "Feb_B", ... )

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgvce4echKqOuFEgEQKfmQCghKchqy94IIQClnYo/EX5FPKqeV0AoKC4
UjszAXXBLOj+O1ab4ftkRAPx
=RAPv
-----END PGP SIGNATURE-----


Jason said:
I'm trying to put together a Bi-monthly crosstab query. So far I've figured
ho to get the results I want on a monthly or quartly basis, but I cannot
figure out how to put together the Bi-monthly Query.
For the Monthly query I have:

TRANSFORM Sum(tblInsect_Event.Number) AS SumOfNumber
SELECT Format([DateFound],"mmm") AS Expr2
FROM tblInsect_Event
GROUP BY Month([DateFound]), Format([DateFound],"mmm")
PIVOT Year([DateFound]);

and for the Quartly query:

TRANSFORM Sum(tblInsect_Event.Number) AS SumOfNumber
SELECT "Qtr" & DatePart("q",[DateFound],1,0) AS Expr2
FROM tblInsect_Event
GROUP BY "Qtr" & DatePart("q",[DateFound],1,0)
PIVOT Year([DateFound]);

How can I change this to give me Bi-monthly totals?
Click to expand...
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I worried about the correct use of bi-monthly after I sent my answer.
Unfortunately, I was using the 2nd definition of the word: twice a
month; not the 1st definition: every 2 months.

The reason I put the years in rows is I can use the PIVOT clause to
define the columns. Also, you may have more years that will easily fit
on a page. But, that doesn't matter in your case 'cuz of the chart.
Just remember to change the chart's X & Y axes.

Here is an untried PIVOT for every 2 months:

PIVOT Switch(Month(DateFound) IN (1,2), "Jan/Feb",
Month(DateFound) IN (3,4), "Mar/Apr",
Month(DateFound) IN (5,6), "May/Jun",
Month(DateFound) IN (7,8), "Jul/Aug",
Month(DateFound) IN (9,10), "Sep/Oct",
Month(DateFound) IN (11,12), "Nov/Dec")

If "Month(DateFound) IN (x,y)" doesn't work try:

Month(DateFound) = x OR Month(DateFound) = y

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgz7q4echKqOuFEgEQLnAgCg0cFaU7e0UFcYsxfZ8Xnc/5yKGgAAn3id
bXLcB0TSne7HKEjQOGvJezpn
=ihEo
-----END PGP SIGNATURE-----

Jason said:
Im sorry, I guess I didnt explain myself very well. I'm looking for the
result set to give me the acumulated totals for every 2 months. E.g:
Jan/Feb Mar/April June/July
2005
2004
2003 ...

As far as witch I pivot on, It doesnt realy matter much to me as I am using
this as the basis of a chart.

Thank you
Jason


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Wouldn't it be better to have the Year as the Row header and the
Month/Qtr as the Column header? E.g.:

Jan Feb Mar ... Qtr1 Qtr2 Qtr3 ...
2005
2004
2003 ...

That'd mean the PIVOT clause would change like this:

Month:
PIVOT Format(DateFound,"mmm") IN ("Jan", "Feb", "Mar", ...)

Qtr:
PIVOT "Qtr" & DatePart("q", DateFound)

Bi-monthly (1-15 & 16-end of month): === UNTESTED
PIVOT Format(DateFound, "mmm") & IIf(Day(DateFound)<16,"_A", "_B")
IN ("Jan_A", "Jan_B", "Feb_A", "Feb_B", ... )

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgvce4echKqOuFEgEQKfmQCghKchqy94IIQClnYo/EX5FPKqeV0AoKC4
UjszAXXBLOj+O1ab4ftkRAPx
=RAPv
-----END PGP SIGNATURE-----


Jason said:
I'm trying to put together a Bi-monthly crosstab query. So far I've
Click to expand...
figured
ho to get the results I want on a monthly or quartly basis, but I cannot
figure out how to put together the Bi-monthly Query.
For the Monthly query I have:

TRANSFORM Sum(tblInsect_Event.Number) AS SumOfNumber
SELECT Format([DateFound],"mmm") AS Expr2
FROM tblInsect_Event
GROUP BY Month([DateFound]), Format([DateFound],"mmm")
PIVOT Year([DateFound]);

and for the Quartly query:

TRANSFORM Sum(tblInsect_Event.Number) AS SumOfNumber
SELECT "Qtr" & DatePart("q",[DateFound],1,0) AS Expr2
FROM tblInsect_Event
GROUP BY "Qtr" & DatePart("q",[DateFound],1,0)
PIVOT Year([DateFound]);

How can I change this to give me Bi-monthly totals?
Click to expand...
Click to expand...
Click to expand...
 
J

Jason Byrnes

That worked a treat, Thank You!! The only problem that solution ran into is
that the Month columns where out of order so I added an In cluase to the end
that defined the order of the columns The final result is:

PIVOT Switch(Month(DateFound) IN (1,2), "Jan/Feb",
Month(DateFound) IN (3,4), "Mar/Apr",
Month(DateFound) IN (5,6), "May/Jun",
Month(DateFound) IN (7,8), "Jul/Aug",
Month(DateFound) IN (9,10), "Sep/Oct",
Month(DateFound) IN (11,12), "Nov/Dec") IN(
"Jan/Feb", "Mar/Apr", "May/Jun", "Jul/Aug",
"Sep/Oct", "Nov/Dec");

Thanks Again
Jason


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I worried about the correct use of bi-monthly after I sent my answer.
Unfortunately, I was using the 2nd definition of the word: twice a
month; not the 1st definition: every 2 months.

The reason I put the years in rows is I can use the PIVOT clause to
define the columns. Also, you may have more years that will easily fit
on a page. But, that doesn't matter in your case 'cuz of the chart.
Just remember to change the chart's X & Y axes.

Here is an untried PIVOT for every 2 months:

PIVOT Switch(Month(DateFound) IN (1,2), "Jan/Feb",
Month(DateFound) IN (3,4), "Mar/Apr",
Month(DateFound) IN (5,6), "May/Jun",
Month(DateFound) IN (7,8), "Jul/Aug",
Month(DateFound) IN (9,10), "Sep/Oct",
Month(DateFound) IN (11,12), "Nov/Dec")

If "Month(DateFound) IN (x,y)" doesn't work try:

Month(DateFound) = x OR Month(DateFound) = y

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgz7q4echKqOuFEgEQLnAgCg0cFaU7e0UFcYsxfZ8Xnc/5yKGgAAn3id
bXLcB0TSne7HKEjQOGvJezpn
=ihEo
-----END PGP SIGNATURE-----

Jason said:
Im sorry, I guess I didnt explain myself very well. I'm looking for the
result set to give me the acumulated totals for every 2 months. E.g:
Jan/Feb Mar/April June/July
2005
2004
2003 ...

As far as witch I pivot on, It doesnt realy matter much to me as I am using
this as the basis of a chart.

Thank you
Jason


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Wouldn't it be better to have the Year as the Row header and the
Month/Qtr as the Column header? E.g.:

Jan Feb Mar ... Qtr1 Qtr2 Qtr3 ...
2005
2004
2003 ...

That'd mean the PIVOT clause would change like this:

Month:
PIVOT Format(DateFound,"mmm") IN ("Jan", "Feb", "Mar", ...)

Qtr:
PIVOT "Qtr" & DatePart("q", DateFound)

Bi-monthly (1-15 & 16-end of month): === UNTESTED
PIVOT Format(DateFound, "mmm") & IIf(Day(DateFound)<16,"_A", "_B")
IN ("Jan_A", "Jan_B", "Feb_A", "Feb_B", ... )

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgvce4echKqOuFEgEQKfmQCghKchqy94IIQClnYo/EX5FPKqeV0AoKC4
UjszAXXBLOj+O1ab4ftkRAPx
=RAPv
-----END PGP SIGNATURE-----


Jason Byrnes wrote:

I'm trying to put together a Bi-monthly crosstab query. So far I've
figured

ho to get the results I want on a monthly or quartly basis, but I cannot
figure out how to put together the Bi-monthly Query.
For the Monthly query I have:

TRANSFORM Sum(tblInsect_Event.Number) AS SumOfNumber
SELECT Format([DateFound],"mmm") AS Expr2
FROM tblInsect_Event
GROUP BY Month([DateFound]), Format([DateFound],"mmm")
PIVOT Year([DateFound]);

and for the Quartly query:

TRANSFORM Sum(tblInsect_Event.Number) AS SumOfNumber
SELECT "Qtr" & DatePart("q",[DateFound],1,0) AS Expr2
FROM tblInsect_Event
GROUP BY "Qtr" & DatePart("q",[DateFound],1,0)
PIVOT Year([DateFound]);

How can I change this to give me Bi-monthly totals?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Totalling Bi-Monthly 3
Bi-monthly totalling query 2
Include empty values in crosstab query 5
Crosstab, Quarter Sum isn't working 6
Crosstab - What am I doing wrong? 10
Parameter Error for Year 2010 7
CrossTab Query Format 2
Crosstab returns blank fields 6

Top