By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Last Months Data

P: 73
I have a Query/Report that I am running a Date Parameter so it only pulls in the information for the End of Month Results for that person. It runs the Avg for everyone for that month also. I was trying to figure out a way to pull in there results from the following month. I found DateSerial(Year(Date),Month(Date)-1,1) that pulled in last month, but how do I pull in Last month Production, calls/hour etc on how the individual did. Is there a way to do that?
Fields:
Date
Name
Production
Calls/hour
Loan Review
Call Monitor
Dec 27 '07 #1
Share this Question
Share on Google+
3 Replies


jaxjagfan
Expert 100+
P: 254
I have a Query/Report that I am running a Date Parameter so it only pulls in the information for the End of Month Results for that person. It runs the Avg for everyone for that month also. I was trying to figure out a way to pull in there results from the following month. I found DateSerial(Year(Date),Month(Date)-1,1) that pulled in last month, but how do I pull in Last month Production, calls/hour etc on how the individual did. Is there a way to do that?
Fields:
Date
Name
Production
Calls/hour
Loan Review
Call Monitor
I normally break my date field into 2 columns in a group by (totals) query in your scenerio. In your case my query columns would be:

Yr: Year([Date])
Mo: Month([Date])
Name
Prod:Sum(Production) - I assumed numeric value. If text change sum to count.

The criteria for Yr and Mo would be:

=Year(Date)
=Month(Date)-1

(Date) is today's date (12/27/2007). The "Month" function will return 12. 12 - 1 = 11 = Last Month.

If you use just a month column it will pull all month 11's regardless of year.

If you don't include the criteria for the Yr column you will get a Yr by Yr results for Month 11 for all years in the table/query.

Try to remember when designing/building tables to refrain from using reserved words and spaces in field names. The Date column would be something like ProdDate. Labels in your forms/reports allow you to display whatever name you want the users to see (such as "Date"). As you will notice there are hard brackets "[]" around "Date" when referencing your "Date" field.
Dec 27 '07 #2

P: 73
I normally break my date field into 2 columns in a group by (totals) query in your scenerio. In your case my query columns would be:

Yr: Year([Date])
Mo: Month([Date])
Name
Prod:Sum(Production) - I assumed numeric value. If text change sum to count.

The criteria for Yr and Mo would be:

=Year(Date)
=Month(Date)-1

(Date) is today's date (12/27/2007). The "Month" function will return 12. 12 - 1 = 11 = Last Month.

If you use just a month column it will pull all month 11's regardless of year.

If you don't include the criteria for the Yr column you will get a Yr by Yr results for Month 11 for all years in the table/query.

Try to remember when designing/building tables to refrain from using reserved words and spaces in field names. The Date column would be something like ProdDate. Labels in your forms/reports allow you to display whatever name you want the users to see (such as "Date"). As you will notice there are hard brackets "[]" around "Date" when referencing your "Date" field.


Thanks for your reply,

Please forgive me, I am not always good a phasing my words correctly. I am running a Query by date already and pulling in for example all of December's production, calls/hour etc. for each person on an individual page in a Report. So, I can give that person there December results. However, I wanted this report to reflect December's results for that person as well as an comparison to how they did last month and a 3/6 month average. Do you know of anyway of doing this?
Dec 27 '07 #3

jaxjagfan
Expert 100+
P: 254
Thanks for your reply,

Please forgive me, I am not always good a phasing my words correctly. I am running a Query by date already and pulling in for example all of December's production, calls/hour etc. for each person on an individual page in a Report. So, I can give that person there December results. However, I wanted this report to reflect December's results for that person as well as an comparison to how they did last month and a 3/6 month average. Do you know of anyway of doing this?
Using the Northwinds database as a source:

Open the Northwinds database (usually found in a folder called Samples in the same folder MS Office is installed). Close any forms that may open. Go to the query tab. Create a new query in design view. Do not select a table and click close. You will see a button on menu bar that says "SQL". Select it and paste for following SQL there:

SELECT Orders.EmployeeID, Year([OrderDate]) AS Yr, Month([OrderDate]) AS Mo, Sum(Orders.Freight) AS TotProduction, Avg(Orders.Freight) AS AvgProduction
FROM Orders
GROUP BY Orders.EmployeeID, Year([OrderDate]), Month([OrderDate])
HAVING (((Month([OrderDate]))>=Month(Date())-1));

Now run/view the query. Is this close to what you're looking for? The Northwinds data is 10 years old so I didn't include criteria for the Yr column.

If I can find it I'll look up some stuff I did to handle the new year coming up or another person may post some help here (The Month(Date())-1 will return 0 next month).
Dec 27 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.