Monthly Totals SQL

[ablatnik via AccessMonster.com]

A little help please. I have a Master Report that totals all reports for a
full year. Below is the SQL statement written for the month of January...

SELECT Department.Department, (SELECT COUNT([Work Orders].ID_Numbers) FROM
[Work Orders] WHERE [Work Orders].Department = Department.Department_ID AND
Year([Date Opened]) = Year(Now()) And DatePart("m", [Date Opened]) = 1) AS
[Issued for Year], (SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders]
WHERE [Work Orders].Department = Department.Department_ID AND Year([Date
Opened]) = Year(Now()) And DatePart("m", [Date Opened]) = 1) AS [Issued for
January], (SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders] WHERE
[Work Orders].Department = Department.Department_ID AND [Work Orders].Status
IN (1,5,6,8) AND Year([Date Opened]) = Year(Now()) And DatePart("m", [Date
Opened]) = 1) AS [Total Active W/Requests]
FROM Department;

This Statement works for January only. I can't just change the month cause
come February, I need the column "Issued for Year" to have the total work
orders for January & February...then March will be added to the list...then
April etc...

Can anybody assist me with this?

 

[MGFoster]

A little help please. I have a Master Report that totals all reports for a
full year. Below is the SQL statement written for the month of January...

SELECT Department.Department, (SELECT COUNT([Work Orders].ID_Numbers) FROM
[Work Orders] WHERE [Work Orders].Department = Department.Department_ID AND
Year([Date Opened]) = Year(Now()) And DatePart("m", [Date Opened]) = 1) AS
[Issued for Year], (SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders]
WHERE [Work Orders].Department = Department.Department_ID AND Year([Date
Opened]) = Year(Now()) And DatePart("m", [Date Opened]) = 1) AS [Issued for
January], (SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders] WHERE
[Work Orders].Department = Department.Department_ID AND [Work Orders].Status
IN (1,5,6,8) AND Year([Date Opened]) = Year(Now()) And DatePart("m", [Date
Opened]) = 1) AS [Total Active W/Requests]
FROM Department;

This Statement works for January only. I can't just change the month cause
come February, I need the column "Issued for Year" to have the total work
orders for January & February...then March will be added to the list...then
April etc...

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

You could have put the [Work Orders] table in the FROM clause and made
the query simpler by using the IIf() function:

PARAMETERS EndMonth Byte;

SELECT D.Department,

COUNT(IIf(Month(W.[Date Opened]) BETWEEN 1 And EndMonth, W.ID_Numbers,
NULL)) As [Issued for Year],

COUNT(IIf(Month(W.[Date Opened]) = 1, W.ID_Numbers, NULL)) AS [Issued
for January],

COUNT(IIf(W.Status IN (1,5,6,8), W.ID_Numbers, NULL)) AS [Total Active
W/Requests]

FROM Department As D INNER JOIN [Work Orders] As W
ON D.Department = W.Department_ID

WHERE Year(W.[Date Opened]) = Year(Date())
AND Month([Date Opened]) BETWEEN 1 And EndMonth

The PARAMETERS clause will cause a pop-up dialog box to appear when you
run the query asking for the ending month - enter a number 1 thru 12.

The NULL in the COUNT(IIf(...)) means the COUNT() won't count that row
(record) when the IIf() criteria is NOT met.

The criteria in the IIf() function in the Issued for Year column and the
criteria pull all the rows you want to work with. The IIf() function in
the COUNT() functions filters the count for each COUNT column.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSbB2roechKqOuFEgEQLIQQCgsx1Z3YobFT6xG/fFOVa03nls0EUAoKsW
RvSkE2x66xhi+NjpaonVlOkw
=GF99
-----END PGP SIGNATURE-----

 

[ablatnik via AccessMonster.com]

Two questions on this response.

1. What????

2. I am confused as to what I am to do. When it comes to programmabiltiy, I
am as novice as they come. I currently have 60 lines in a macro to create
the report I use now, but it has flaws and as more work orders are entered,
the slower it goes. Come the end of the year, I could run the report, have 2
cups of coffee and finish before the report is done. With multiple emails to
a friend was I able to come up with the SQL statement, but I am getting the
results I need in the Total for the year.

Basically, I don't know anything about IF statements. What you wrote below
just confused me.

A little help please. I have a Master Report that totals all reports for a
full year. Below is the SQL statement written for the month of January...

[quoted text clipped - 15 lines.

orders for January & February...then March will be added to the list...then
April etc...

You could have put the [Work Orders] table in the FROM clause and made
the query simpler by using the IIf() function:

PARAMETERS EndMonth Byte;

SELECT D.Department,

COUNT(IIf(Month(W.[Date Opened]) BETWEEN 1 And EndMonth, W.ID_Numbers,
NULL)) As [Issued for Year],

COUNT(IIf(Month(W.[Date Opened]) = 1, W.ID_Numbers, NULL)) AS [Issued
for January],

COUNT(IIf(W.Status IN (1,5,6,8), W.ID_Numbers, NULL)) AS [Total Active
W/Requests]

FROM Department As D INNER JOIN [Work Orders] As W
ON D.Department = W.Department_ID

WHERE Year(W.[Date Opened]) = Year(Date())
AND Month([Date Opened]) BETWEEN 1 And EndMonth

The PARAMETERS clause will cause a pop-up dialog box to appear when you
run the query asking for the ending month - enter a number 1 thru 12.

The NULL in the COUNT(IIf(...)) means the COUNT() won't count that row
(record) when the IIf() criteria is NOT met.

The criteria in the IIf() function in the Issued for Year column and the
criteria pull all the rows you want to work with. The IIf() function in
the COUNT() functions filters the count for each COUNT column.

 

[MGFoster]

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

How to find out about Access built-in functions: RTFM ;-)

For VBA functions, etc. [IIf() is a VBA function]:

1. Open the Debug window (hit the Ctrl-G key combination).
2. Type in the function name.
Put the cursor on the name and hit the F1 key.
The Help article for that function will appear.

I re-wrote your query so it would be faster and allow it to run for all
months in the year. Have you tried it yet? Hint: you put the SQL into
a Query definition's SQL View and hit the run button - exclamation point
in tool bar.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSbC5yYechKqOuFEgEQLdsACgiRcMMFTE1VBV3/eQSgfEUzJrKnoAmwdc
wA0V/AiiQrGk1v6xmXWIIg3a
=Z9gj
-----END PGP SIGNATURE-----

Two questions on this response.

1. What????

2. I am confused as to what I am to do. When it comes to programmabiltiy, I
am as novice as they come. I currently have 60 lines in a macro to create
the report I use now, but it has flaws and as more work orders are entered,
the slower it goes. Come the end of the year, I could run the report, have 2
cups of coffee and finish before the report is done. With multiple emails to
a friend was I able to come up with the SQL statement, but I am getting the
results I need in the Total for the year.

Basically, I don't know anything about IF statements. What you wrote below
just confused me.

A little help please. I have a Master Report that totals all reports for a
full year. Below is the SQL statement written for the month of January...

[quoted text clipped - 15 lines.

orders for January & February...then March will be added to the list...then
April etc...

You could have put the [Work Orders] table in the FROM clause and made
the query simpler by using the IIf() function:

PARAMETERS EndMonth Byte;

SELECT D.Department,

COUNT(IIf(Month(W.[Date Opened]) BETWEEN 1 And EndMonth, W.ID_Numbers,
NULL)) As [Issued for Year],

COUNT(IIf(Month(W.[Date Opened]) = 1, W.ID_Numbers, NULL)) AS [Issued
for January],

COUNT(IIf(W.Status IN (1,5,6,8), W.ID_Numbers, NULL)) AS [Total Active
W/Requests]

FROM Department As D INNER JOIN [Work Orders] As W
ON D.Department = W.Department_ID

WHERE Year(W.[Date Opened]) = Year(Date())
AND Month([Date Opened]) BETWEEN 1 And EndMonth

The PARAMETERS clause will cause a pop-up dialog box to appear when you
run the query asking for the ending month - enter a number 1 thru 12.

The NULL in the COUNT(IIf(...)) means the COUNT() won't count that row
(record) when the IIf() criteria is NOT met.

The criteria in the IIf() function in the Issued for Year column and the
criteria pull all the rows you want to work with. The IIf() function in
the COUNT() functions filters the count for each COUNT column.

 

[John W. Vinson]

This Statement works for January only. I can't just change the month cause
come February, I need the column "Issued for Year" to have the total work
orders for January & February...then March will be added to the list...then
April etc...

Well, let me suggest several changes. Rather than using Year(Now()) for
criteria - which will not benefit from any indexes - use a range of dates; and
change the subqueries used to calculate the month to date and year to date
sums:

SELECT Department.Department,
(SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders] WHERE [Work
Orders].Department = Department.Department_ID AND [Date Opened] >=
DateSerial(Year(Date()), 1, 1) )
AS [Issued for This Year],
(SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders]
WHERE [Work Orders].Department = Department.Department_ID
AND [Date Opened] >= DateSerial(Year(Date()), Month(Date()), 1))
AS [Issued for This Month],
(SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders] WHERE
[Work Orders].Department = Department.Department_ID
AND [Work Orders].Status IN (1,5,6,8)
AND [Date Opened] >= DateSerial(Year(Date()), 1, 1) )
AS [Total Active W/Requests]
FROM Department;

This assumes that the Date Opened will not be in the future, or that you want
to include such dates in the count; if that's not the case, add a criteiron

AND [Date Opened] <= Date()

 

[ablatnik via AccessMonster.com]

I did not see your new re-write. The old one is still the only one listed.
Instead of trying to describe what the report looks like, I am going to input
the format below. The numbers are actual numbers from the report I use now.

J A N U A R Y F E B R U A R Y
Dept IFY IFM Tot Active IFY IFM Tot Active
Carpenters 75 75 9 171 96 37
Custodial 26 26 0 54 28 11
Dorms (MR) 225 225 1 432 207 12
Electricans 151 151 6 326 175 55
Grounds 50 50 1 118 68 10
Locksmith 101 101 16 170 69 29
Mechanical 81 81 6 173 92 11
Painters 16 16 6 35 19 14
UEM 149 149 0 386 237 0

In this report, the January numbers will be every work order for January.
February IFY is a total of all work orders for January and February belonging
to their respective department. IFM will only be the work orders issued in
February only. And Total Active is the same, only for the current month. As
work orders are closed, the numbers will change respectively, but they will
have to remain in their current month on a master report.

How to find out about Access built-in functions: RTFM ;-)

For VBA functions, etc. [IIf() is a VBA function]:

1. Open the Debug window (hit the Ctrl-G key combination).
2. Type in the function name.
Put the cursor on the name and hit the F1 key.
The Help article for that function will appear.

I re-wrote your query so it would be faster and allow it to run for all
months in the year. Have you tried it yet? Hint: you put the SQL into
a Query definition's SQL View and hit the run button - exclamation point
in tool bar.

Two questions on this response.

[quoted text clipped - 47 lines]

 

[MGFoster]

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

Here is my re-write. John Vinson's suggestion of using dates instead of
Year() & Month() functions is a good suggestion. Using functions on the
table's columns in the WHERE criteria slows down the query 'cuz it has
to scan the whole table to determine if the result of the function on
the indicated column yields the criteria requirements.

PARAMETERS EndDate Date;
SELECT D.Department,
COUNT(IIf(Month(W.[Date Opened]) BETWEEN 1 And Month(EndDate),
W.ID_Numbers, NULL)) As [Issued for Year],
COUNT(IIf(Month(W.[Date Opened]) = 1, W.ID_Numbers, NULL)) AS [Issued
for January],
COUNT(IIf(W.Status IN (1,5,6,8), W.ID_Numbers, NULL)) AS [Total Active
W/Requests]
FROM Department As D INNER JOIN [Work Orders] As W
ON D.Department = W.Department_ID
WHERE W.[Date Opened] BETWEEN DateSerial(Year(EndDate), 1, 1) And
DateSerial(Year(EndDate), Month(EndDate)+1, 0)

The DateSerial() function in the WHERE clause after the Between operator
will be evaluated only once. The query will use any index on W.[Date
Opened], which will speed up the query run.

The WHERE criteria selects rows (records) from the beginning of the year
of the EndDate parameter to the last date in the EndDate's month.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSbGjW4echKqOuFEgEQKfjwCfbSW+TD1qNglktSiAckdi+PbOE4QAoPZk
ZwI/s6KjLPbDHiBwXGlEwz31
=YKjf
-----END PGP SIGNATURE-----

I did not see your new re-write. The old one is still the only one listed.
Instead of trying to describe what the report looks like, I am going to input
the format below. The numbers are actual numbers from the report I use now.

J A N U A R Y F E B R U A R Y
Dept IFY IFM Tot Active IFY IFM Tot Active
Carpenters 75 75 9 171 96 37
Custodial 26 26 0 54 28 11
Dorms (MR) 225 225 1 432 207 12
Electricans 151 151 6 326 175 55
Grounds 50 50 1 118 68 10
Locksmith 101 101 16 170 69 29
Mechanical 81 81 6 173 92 11
Painters 16 16 6 35 19 14
UEM 149 149 0 386 237 0

In this report, the January numbers will be every work order for January.
February IFY is a total of all work orders for January and February belonging
to their respective department. IFM will only be the work orders issued in
February only. And Total Active is the same, only for the current month. As
work orders are closed, the numbers will change respectively, but they will
have to remain in their current month on a master report.

How to find out about Access built-in functions: RTFM ;-)

For VBA functions, etc. [IIf() is a VBA function]:

1. Open the Debug window (hit the Ctrl-G key combination).
2. Type in the function name.
Put the cursor on the name and hit the F1 key.
The Help article for that function will appear.

I re-wrote your query so it would be faster and allow it to run for all
months in the year. Have you tried it yet? Hint: you put the SQL into
a Query definition's SQL View and hit the run button - exclamation point
in tool bar.

Two questions on this response.

[quoted text clipped - 47 lines]

criteria pull all the rows you want to work with. The IIf() function in
the COUNT() functions filters the count for each COUNT column.

 

[ablatnik via AccessMonster.com]

Thank you. I created a new report to incorporate this leaving my old one
alone for now.

This Statement works for January only. I can't just change the month cause
come February, I need the column "Issued for Year" to have the total work
orders for January & February...then March will be added to the list...then
April etc...

Well, let me suggest several changes. Rather than using Year(Now()) for
criteria - which will not benefit from any indexes - use a range of dates; and
change the subqueries used to calculate the month to date and year to date
sums:

SELECT Department.Department,
(SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders] WHERE [Work
Orders].Department = Department.Department_ID AND [Date Opened] >=
DateSerial(Year(Date()), 1, 1) )
AS [Issued for This Year],
(SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders]
WHERE [Work Orders].Department = Department.Department_ID
AND [Date Opened] >= DateSerial(Year(Date()), Month(Date()), 1))
AS [Issued for This Month],
(SELECT COUNT([Work Orders].ID_Numbers) FROM [Work Orders] WHERE
[Work Orders].Department = Department.Department_ID
AND [Work Orders].Status IN (1,5,6,8)
AND [Date Opened] >= DateSerial(Year(Date()), 1, 1) )
AS [Total Active W/Requests]
FROM Department;

This assumes that the Date Opened will not be in the future, or that you want
to include such dates in the count; if that's not the case, add a criteiron

AND [Date Opened] <= Date()