Query Design for Monthly Report

[Guest]

Hi there,I am a bit at a loss as to where to start with the query for a
report I need, was hoping you could help- I have two tables, 04PMs and PMLog.
The '04 log is seperate because since the use of our database, much more info
is recorded. Both tables have these fields in common: PMOrderID, StartDate
and FinishDate. The 04PMs table's equipment information is located in the
Notes field, whereas in the PMLog the equipment information is located in the
EquipID field. Based on the information in these two tables (it is ok if they
remain seperated into 2004 and 2005) I need to make a report(s) that can give
me the following information (for each if they remain seperate):
I need to know how many PM's were scheduled in a month and how many were
completed within that month. (for every month past, then to use afterwards
for the monthly report as a single month)
I don't even have an idea of what kind of query I should be using. I really
appreciate any help you can offer.

--Aaron Regular, Dissent Records
www.nbtnc.com

 

[Guest]

Hi,

Everything should be based on the current date?

 

[Guest]

Well, not necessarily- for example, I guess I want it to give this info:
August 04 had 63 PM's scheduled, 45 were completed on or before August 31,
04.
September 04 had 76 Pm's scheduled, 68 were completed on or before September
30, 04.
October...etc.
THEN, after I know all that, I want a report to tell me that as of the
current date, how many PM's are scheduled for the month and how many have
been completed to date.
Does this help? Thanks for your response!

 

[Guest]

This sql gives you an example on how to work it out.

SELECT Year([Project_StartDate]) AS QueryYear, Month([Project_StartDate]) AS
QueryMonth, Sum(IIf(Year([Project_StartDate])=[Year] And
Month([Project_StartDate])=CInt([Month]),1,0)) AS Started,
Sum(IIf([Project_StartDate]<DateSerial([Year],[Month]+1,1),1,0)) AS Ended
FROM Project
GROUP BY Year([Project_StartDate]), Month([Project_StartDate]);

 

[Guest]

Thanks so much for your help-I will try this and post back to you!
--
Aaron Regular, Dissent Records
www.nbtnc.com

This sql gives you an example on how to work it out.

SELECT Year([Project_StartDate]) AS QueryYear, Month([Project_StartDate]) AS
QueryMonth, Sum(IIf(Year([Project_StartDate])=[Year] And
Month([Project_StartDate])=CInt([Month]),1,0)) AS Started,
Sum(IIf([Project_StartDate]<DateSerial([Year],[Month]+1,1),1,0)) AS Ended
FROM Project
GROUP BY Year([Project_StartDate]), Month([Project_StartDate]);

--
- Raoul Jacobs

The nature of developping is sharing knowledge.

Well, not necessarily- for example, I guess I want it to give this info:
August 04 had 63 PM's scheduled, 45 were completed on or before August 31,
04.
September 04 had 76 Pm's scheduled, 68 were completed on or before September
30, 04.
October...etc.
THEN, after I know all that, I want a report to tell me that as of the
current date, how many PM's are scheduled for the month and how many have
been completed to date.
Does this help? Thanks for your response!