WTD, MTD, and YTD Queries

I'm trying to create a query that can total week-to-date, month-to-date, and 
year-to-date stats.  I think a separate query for each would be easist?  
Also, prompting for each query would be preferred.

An example of each record would be:
Business Date 11/01/07
Calls Received 10
Calls Answered 8

Business Date 11/02/07
Calls Received 20
Calls Answered 17

When I use the Between () And () function, it doesn't sum the Calls Received 
and Calls Answered together (MTD), but sums them for each day.

Thanks in advance!
0
Utf
11/1/2007 4:46:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1860 Views

Similar Articles

[PageSpeed] 40

Try this ---
SELECT [Enter start] AS [Begining Period], [Enter end] AS [End of Period], 
Sum(IIf([Business Date] Between Date() And 
DateAdd("d",-DatePart("y",Date()),Date()),[Calls Received],0)) AS [YTD Calls 
Received], Sum(IIf([Business Date] Between Date() And 
DateAdd("d",-DatePart("y",Date()),Date()),[Calls Answered],0)) AS [YTD Calls 
Answered], Sum(IIf([Business Date] Between Date() And 
DateAdd("d",-DatePart("d",Date()),Date())+1,[Calls Received],0)) AS [MTD 
Calls Received], Sum(IIf([Business Date] Between Date() And 
DateAdd("d",-DatePart("d",Date())+1,Date()),[Calls Answered],0)) AS [MTD 
Calls Answered], Sum(IIf([Business Date] Between Date() And 
DateAdd("d",-DatePart("w",Date()),Date())+1,[Calls Received],0)) AS [WTD 
Calls Received], Sum(IIf([Business Date] Between Date() And 
DateAdd("d",-DatePart("w",Date())+1,Date()),[Calls Answered],0)) AS [WTD 
Calls Answered]
FROM Ben
WHERE (((Ben.[Business Date]) Between [Enter start] And [Enter end]))
GROUP BY [Enter start], [Enter end];

It might need some tweaking to make sure that the XTD is not one day off.
-- 
KARL DEWEY
Build a little - Test a little


"Ben" wrote:

> I'm trying to create a query that can total week-to-date, month-to-date, and 
> year-to-date stats.  I think a separate query for each would be easist?  
> Also, prompting for each query would be preferred.
> 
> An example of each record would be:
> Business Date 11/01/07
> Calls Received 10
> Calls Answered 8
> 
> Business Date 11/02/07
> Calls Received 20
> Calls Answered 17
> 
> When I use the Between () And () function, it doesn't sum the Calls Received 
> and Calls Answered together (MTD), but sums them for each day.
> 
> Thanks in advance!
0
Utf
11/1/2007 6:40:01 PM
You cannot group by the dates.  If you want to get a specific range of dates 
change GROUP BY to WHERE in your query.

SELECT Sum([Calls Received]) as RecievedCount
, Sum ([Calls Answered]) as AnswerCount
FROM YourTable
WHERE [Business Date] Between () and ()



-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Ben" <Ben@discussions.microsoft.com> wrote in message 
news:E97DDADC-1735-429B-BB70-3F187144C914@microsoft.com...
> I'm trying to create a query that can total week-to-date, month-to-date, 
> and
> year-to-date stats.  I think a separate query for each would be easist?
> Also, prompting for each query would be preferred.
>
> An example of each record would be:
> Business Date 11/01/07
> Calls Received 10
> Calls Answered 8
>
> Business Date 11/02/07
> Calls Received 20
> Calls Answered 17
>
> When I use the Between () And () function, it doesn't sum the Calls 
> Received
> and Calls Answered together (MTD), but sums them for each day.
>
> Thanks in advance! 


0
John
11/1/2007 6:52:30 PM
SELECT a.BusinessDate, LAST(a.received), LAST(a.answered),
    SUM(b.received) AS wtd,  SUM(c.received) AS mtd, SUM(d.received) AS ytd

FROM ((myTable As a INNER JOIN myTable AS b
                ON a.businessDate >= b.businessDate
                    AND  0= DateDiff("ww", a.businessDate, b.businessDate)
            )
                                INNER JOIN myTable As c
                ON a.businessDate >= c.businessDate
                    AND 0 = DateDiff("m", a.businessDate, c.businessDate)
            )
                                INNER JOIN myTable AS d
                ON a.businessDate >= d.businessDate
                    AND 0 = DateDiff("yyyy", a.businessDate, d.businessDate)

GROUP BY a.businessDate




That can be very slow, though. You can add the SUM(b.answered), 
SUM(c.answered) and SUM(d.answered) too. That won't add much to the running 
time. I didn't include them to not obscure the query. The main point is the 
FROM clause which clearly deals with date before the actual grouped date, on 
the same week (aliased as  b), month (aliased as  c) or year (aliased as d)




Hoping it may help
Vanderghast, Access MVP


"Ben" <Ben@discussions.microsoft.com> wrote in message 
news:E97DDADC-1735-429B-BB70-3F187144C914@microsoft.com...
> I'm trying to create a query that can total week-to-date, month-to-date, 
> and
> year-to-date stats.  I think a separate query for each would be easist?
> Also, prompting for each query would be preferred.
>
> An example of each record would be:
> Business Date 11/01/07
> Calls Received 10
> Calls Answered 8
>
> Business Date 11/02/07
> Calls Received 20
> Calls Answered 17
>
> When I use the Between () And () function, it doesn't sum the Calls 
> Received
> and Calls Answered together (MTD), but sums them for each day.
>
> Thanks in advance! 


0
Michel
11/1/2007 7:58:56 PM
Forget that ugly join.  Does not seem to work, and is definitively too slow. 
No, instead, try:

========================
SELECT a.stamp, LAST(a.amount) As acutal, SUM(b.amount) as running, p.period
FROM (History AS a INNER JOIN  History AS b ON a.Stamp>=b.Stamp) ,  Periods 
AS p
WHERE 0=DateDiff(p.period, a.stamp, b.stamp)
GROUP BY a.stamp, p.period
ORDER BY p.period, a.stamp
========================

where History is your actual table;
        stamp is your businessDate field
        amount  is either your number of answered calls or of your received 
call
            Periods is a new table, one field, period, 3 records, with 
values  "ww", "m" and "yyyy".



As example, with data:

  History Stamp Amount
      2007.01.01 1
      2007.01.02 2
      2007.01.03 3
      2007.02.01 4
      2007.02.08 5
      2007.02.09 6
      2007.03.03 7
      2007.04.04 8
      2007.04.06 9
      2007.04.11 10


and

  Periods period
      m
      ww
      yyyy



I got, as result:


  Query3 stamp acutal running period
      2007.01.01 1 1 m
      2007.01.02 2 3 m
      2007.01.03 3 6 m
      2007.02.01 4 4 m
      2007.02.08 5 9 m
      2007.02.09 6 15 m
      2007.03.03 7 7 m
      2007.04.04 8 8 m
      2007.04.06 9 17 m
      2007.04.11 10 27 m
      2007.01.01 1 1 ww
      2007.01.02 2 3 ww
      2007.01.03 3 6 ww
      2007.02.01 4 4 ww
      2007.02.08 5 5 ww
      2007.02.09 6 11 ww
      2007.03.03 7 7 ww
      2007.04.04 8 8 ww
      2007.04.06 9 17 ww
      2007.04.11 10 10 ww
      2007.01.01 1 1 yyyy
      2007.01.02 2 3 yyyy
      2007.01.03 3 6 yyyy
      2007.02.01 4 10 yyyy
      2007.02.08 5 15 yyyy
      2007.02.09 6 21 yyyy
      2007.03.03 7 28 yyyy
      2007.04.04 8 36 yyyy
      2007.04.06 9 45 yyyy
      2007.04.11 10 55 yyyy



where the last column supplies the period for which the running sum is 
computed.


Vanderghast, Access MVP 


0
Michel
11/2/2007 6:25:09 PM
Reply:

Similar Artilces:

Xpath expressions and queries? How do I do this?
Does anyone have any ideas on how to implement what has been suggested for my 'rules engine'? thx, -hazz the following are rules in the sql server table, one row for each rule. Table Column op value score Buy Budget > 500000 10 Buy Budget < 500000 5 Sell In6mths = Yes 10 Sell In6mths = No 5 STEP 1 (SUGGESTION) Each rule consists of an XPath expression for the operand, and operator, the value you compare against, and the score. If I have an object to evaluate a...

action query blocked by disabled mode
I am trying to run a make table query and it doesn't run. I get an error message saying "This action was blocked by disabled mode". Help! What version of Access? If 2007 you may need to go up to where is says to Enable Content just under the toolbars. Also check out the following: http://office.microsoft.com/en-us/access/HA101085051033.aspx -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Laura" wrote: > I am trying to run a make table query and it doesn't run. I get an error > message sayin...

Update query question 04-18-07
I have a table with a field where the data is a calculated value. The original query's field looks like this: calcUnitTotalFBM: ([Thickness]*[Width]*[calcUnitTotalLength])/12 Originally, I have this calculated value entered in the actual field in the table, which is UnitTotalFBM on the original data entry form. And there will usually be multiple records. But he user wants to be able to change either Thickness or Width, which requires the recalculation of UnitTotalFBM for all the records on that form. An update query seems like the solution because it would update just that field...

query returns in different columns projects from different phases
I have a tblProject that contains projects and their phase of execution. Each project can be in one of the following three phases: Portfolio, Pre-project or Execution I want to show a table in a form containing the names of projects in each phase. I thought of using a query where the columns are the project phases and the query would return the names of the projects in each of these phases. Let’s say the query result would be: PORTFOLIO PRE-PROJECT EXECUTION Projetct1 Projetct4 Projetct5 Project2 Project7 Projetct6 Projetct3 ...

What are the advantages of a query using a derived table(s) over a query not using them?
I know how derived tables are used, but I still can=92t really see any real advantages of using them. For example, in the following article http://techahead.wordpress.com/2007/1= 0/01/sql-derived-tables/ the author tried to show benefits of a query using derived table over a query without one with an example, where we want to generate a report that shows off the total number of orders each customer placed in 1996, and we want this result set to include all customers, including those that didn=92t place any orders that year and those that have never placed any orders at all ( he=92s ...

Query based on a combobox on a subform in a subform
Hello I have a subform witin a subform. In that subform are two comboboxes. I would like to know how to get the list in combobox 2 to be dependent on combobox 1. Specifically, box 1 is measurement_unit_type with values: "Time", "Distance", "Mass" etc.. Once this is selected. I would then like the contents of box 2 to be filtered so that if, for example "Time" is selected, the values would be "seconds", "hours", "days", if Mass is seleted, then the values would be "grams", "kilograms", etc.. I have made a ...

LDAP Query needed
I need an ldap query to find all users with an exchange mailbox where the users alias does not equal the nickname of the smtp email address(s). Thanks! "MrRAlan" <MrRAlan@discussions.microsoft.com> wrote: >I need an ldap query to find all users with an exchange mailbox where the >users alias does not equal the nickname of the smtp email address(s). Thanks! I don't believe LDAP supports what you want to do. You'll have to write a small program (vbs or perl) that creates a mail address and then queries the AD for a match. -- Rich Matheisen MCSE+I, Exchange ...

Joining unrelated data in query
I want to take a single "StartDate" field from a table "Dates" and include it on every record in a query "qry_tblShipmentTotals_ASSOCID". The "StartDate" field is used in multiple queries, so I have it being input via a form into the "Dates" table one time (to prevent having to enter it over and over again until it changes). There are expressions in the "qry_tblShipmentTotals_ASSOCID" that use the "StartDate" value. Ideas? -- Kemo You can add the Dates table like you do any other table in a query, but you ...

Deleting specific records based on query results
Is there a way to use a select query to find and then delete specific records in a database. My database records basic applicant information in one table (tblApplicants) and any interview data in another (TblActivities). I need to do an annual purge of applicants from a previous year who were never interviewed. I have a select query that returns the name and date entered from the TblApplicants table where the requisition data in the TblActivities table is null. I know I can then go to the TblApplicants and manually delete each record, but at over 1700 records that seems a lit...

Pass through query and variables
I'm currently building a pass through query which ties to an IBM Iseries ODBC driver. Is there any way to include an "in" statement from another non-pass through table or query in the same access database? For the sake of example, let's say I have a static table of user names (name: tbl_users). I have a pass through query built that I would like to only return results based off of the tbl_users content. What would be the syntax to accomplish this? In theory, the logic would look like this but I'm not sure of the exact syntax: Select ODBC.userid From ODBC Where O...

Dividing across queries
Is it possible to create a query that divides the results of one query by the results of another query? If so...how? Thank you to all for any help you can give. Pretty generic ... without an example and with only general description, you're likely to get fairly generic suggestions. Yes. Both queries will have to have a field they share in common, though, so you can join one to the other and use (related) fields from eahc in a new (third) query you design. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received ...

Query a database on a server not working
I've set-up a query on a database that's on our server and I get an error: "UNEXPECTED ERROR FROM EXTERNAL DATABASE DRIVER (11270)", but if I save that database to my local drive, the query works just fine. How can I make the query work with the database on the server? ...

Struggling with my insert query...
I've got this insert query running embedded in a VBA statement. The VBA looks at a table and finds the largest number stored in the column BatchNum and then adds 1 to it and stores that new number in the variable lngBatch. It then runs the following SQL (note that the <lngBatch> as seen below is just a placeholder... in the actual code the SQL statment is put into a string and would be "... CaseNum," & lngBatch & vbCrLf & "FROM ..."): INSERT INTO tblRef ( RefNum, BatchNum ) SELECT DISTINCT CaseNum, <lngBatch> FROM tblMain; This statement...

Run Query
Is there anyway in which I can run a select query, but only return say the first ten rows to save time? Running a query against a million records takes some time and whilst making ammendments I quickly want to review some of the returned formats (say the first ten). Ta Change the SQL statement from: SELECT ...... to SELELCT TOP 10 ..... For the TOP statement to work you need to have a sort field or ORDER BY clause. HOWEVER, it still needs to search through all the records. An index on the sorted field(s) can help. Better yet would by criteria or a WHERE clause to limit the records. ...

Report based on Crosstab Query 02-12-10
Each year for the past 4 to 5 years, I have used a small db (I created) to supplement our accounting systems lack to provide an actual report for each and every fund budget we have. The detail table in the db I created only has 3 columns: Fund, Acct, and Amount. I created a crosstab query to list: CatDesc, Acct, Importance, Name, and each fund as columns and the amount. CatDesc Acct Imp Name 300 310 320 340 This portion works great except for the fact when I want to create the final draft of the budget. I manually arrange the fund columns into t...

Command Button
Hi, I have about 40 Queries by Year. I don't want to write it down one by one to make a command button. DoCmd.OpenQuery "2007", acViewNormal, acEdit DoCmd.OpenQuery "20071", acViewNormal, acEdit DoCmd.OpenQuery "20072", acViewNormal, acEdit I'm tired if I make the change then I have to go back the command button to adjust the name of the queries. Is there a way for create a code for "Like 2007*" or Like 2006* or Like 2005* or Like 2004*, etc. in one command button. DoCmd.OpenQuery "2007", acViewNormal, acEdit ...

Query based upon another query not returning decimal data
I have Qry2 based upon Qry1 and that query is based on a linked table in SQL 2005. Fld1 and Fld2 are in the table and defined as decimal(28,18) in SQL. Qry1 returns records containing Fld1 & Fld2 without any problems. Qry2 is a Crosstab query performing a Group By / Row Heading on Fld1 and a sum value on Fld2. Qry2 gets an error on Fld1 stating "Invalid Precision for decimal data type". If I use the CDec function on it, the error for this field does not appear any more - but then after some processing, I get this error on Fld2: "Invalid scale for decimal data type&...

Nested query with combined key
Hi I am working on an existing database that has three tables, no option to change the database structure, but have to work on it. CREATE TABLE Building ( b_id INT PRIMARY KEY, b_name char(50)); CREATE TABLE Door( d_id INT PRIMARY KEY, d_name char(30), d_key char(10), d_BuildingID int references Building(b_id)); CREATE TABLE History ( h_key char(40) PRIMARY KEY, transaction_time DATETIME PRIMARY KEY); The h_hey in the History is actually d_id + "-" + d_key, now I need to join three tables, get the building, door and associated last transacti...

PLEASE HELP: Display current Date & Time in Query Output
Hi All, How can I display current date and time in query output? Is there a way to do this? Thanks in advance You can use the built-in functions of Date() and Time() in the query. Today: Date() RightNow: Time() -- Duane Hookom Microsoft Access MVP "sam" wrote: > Hi All, > > How can I display current date and time in query output? > Is there a way to do this? > > Thanks in advance On Sun, 25 Apr 2010 20:39:01 -0700, sam <sam@discussions.microsoft.com> wrote: >Hi All, > >How can I display current date and time in...

I can't edit my web query.
Hi Win 200 pro, Excel '97 sr2. I've a work book that contains a web query to download my shares portfolio from a web page. This book was created on another computer (still Excel '97 - not sure of the service release No.) but on my new computer it won't retrieve the correct info. So I tried to edit the query. I activated a cell in the query range & clicked Edit Query on the external database toolbar. I got the message: This external data range contains data from a Web query. Web queries cannot be edited. Why is this? I'm sure I could on other computers. How do I re...

Still confused about queries to similate vlookup
Normally I would just dump this into excel, but the file that I am dealing with has over 1.5 million rows so excel can't stomach it. Here is what I have done. I have one table (MAIN) that is my main table. It contains all the raw data. I need to convert a city code to a city name. I have another table (CITY) with the conversions from city code to city name. Both tables contain all the city codes. All I want to do is put the city names into my MAIN table next to their corresponding city code (there are about 4000 different cities). I have related the tables relating th...

Weekly Crosstab Query 03-28-07
I am currently working on a small database project that is very basic. We are basically tracking the usage of our 3 internet cafe's. Each user is required to sign the sheet when they login, as well as when they log out. We take this info and enter into acces, that way we can create reports to better understand how everything is being utilized. Currently I have been using a few crosstab queries to get info per each direct report. I have used the wizard to create a query for monthly, yearly, and Quarterly. Now my question would be, what would I need to do to modify a query to...

Refresh queries, graphs, and charts
I have an Excel workbook that contains several worksheets that are linked to Access queries, of an ORACLE database. This workbook also contains numerous tabs which contain pivot tables (based on the worksheets that are linked to the Access db) and charts based on the pivot tables. I am looking for a way to quickly refresh the links to the Access queries, as refresh all of the data in the pivot tables and charts, without having to move to each of these worksheets and refresh them. Hope this makes sense. Dale "Dale Fye" <dale.fye@nospam.com> wrote in message news:F906...

"Query Builder Error: No Attribute"
I'm trying to go to the mapping options of the Lead to Account entities and get the following message: "Query Builder Error: No Attribute" All other entity mappings are accessible. Any ideas what might be going on with this one? -- Thank You, Robert Harrison Portland: 503-345-9176 ext 814 Seattle: 206-686-3254 ext 814 Gateway Solutions Inc www.GSIcrm.com Certified Microsoft CRM Solution Partner Hi Rob, I just posted a similar problem above yours and managed to solve it immediately after. The issue may be a deleted field on one side of the relationship - dont know ...

Run Saved Query
In Excel 2000, I am able to run a saved query in the following method: Data - Get External Data - Run Saved Query. How do I accomplish this in Excel 2003? ...