If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
using a rolling date range to sum
I want to use an update query to sum values of the previous twelve months. I
tried using BETWEEN "#" & [date from] & "# AND #" & [date to] & "#" in the criteria to limit records summed. I get a error message telling me I did not include the and. I really would like to find a source that has the explaination of each symbol and the parameters of the built in functions. My dates are short date and time and always the first of the month. I have several years of data that needs the rolling sum calculated so using a fixed date would not work. thanks in advance |
#2
|
|||
|
|||
using a rolling date range to sum
reluctantcoder wrote:
I want to use an update query to sum values of the previous twelve months. I tried using BETWEEN "#" & [date from] & "# AND #" & [date to] & "#" in the criteria to limit records summed. I get a error message telling me I did not include the and. I really would like to find a source that has the explaination of each symbol and the parameters of the built in functions. My dates are short date and time and always the first of the month. I have several years of data that needs the rolling sum calculated so using a fixed date would not work. The And is part of the Between operator so it must be outside quotes: BETWEEN "#" & [date from] & "#" AND "#" & [date to] & "#" -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
using a rolling date range to sum
Firstly, the date format is irrelevant. A value of date/time data type in
Access is stored as a 64 bit floating point number which is unchanged whatever format you choose to see the dates in. You don't need to include the # date delimiter characters if you are creating the query in design view or SQL view, only if you are building the SQL for the query as a string expression and wish to concatenate the values of variables into the string as literal date/time values. If creating the query in design view simply enter: BETWEEN [date from] AND [date to] in the 'criteria' row of the relevant data column. With date/time parameters, however, its always a good idea to declare them as such. To do this select Parameters form the Query menu in design view (or whatever the equivalent is if using Access 2007). In the left hand column of the dialogue enter [date from] in the first row and [date to] in the second. In the right hand column for each row select Date/Time as the data type. Declaring the parameters like this avoids the risk of their being misinterpreted as arithmetic expressions if entered in a format which might be interpreted as such. However, the fact that you are executing an update query suggests that you are storing the running sum values in a column in a table. This would not normally be necessary, and more importantly, would be inadvisable as it would leave the table open to inconsistent data. Where a value can be computed from other data it should not usually be stored, but computed on the fly in a query. There are exceptions to this, e.g. you would not compute invoice amounts from the unit prices of products in a products table as the price can change, but the invoice amount must remain static as that at the time the invoice was created, but in your case it doesn't sound like this sort of reasoning would apply. Here's an example of a query which returns a running balance per customer from a table of transactions with Credit and Debit columns for a calendar year entered as a parameter, bringing forward any balance from the previous year: SELECT CustomerID, TransactionDate, Credit, Debit, (SELECT SUM(Credit) FROM Transactions AS T2 WHERE T2.CustomerID = T1.CustomerID AND T2.TransactionDate = T1.TransactionDate AND ( T2.TransactionID = T1.TransactionID OR T2.TransactionDate T1.TransactionDate)) - (SELECT SUM(Debit) FROM Transactions AS T2 WHERE T2.CustomerID = T1.CustomerID AND T2.TransactionDate = T1.TransactionDate AND ( T2.TransactionID = T1.TransactionID OR T2.TransactionDate T1.TransactionDate)) AS Balance FROM Transactions AS T1 WHERE YEAR(TransactionDate) = [Enter year:] ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC; and here's one which does the same without bringing forward a balance from the previous year, i.e. its zero-based: SELECT CustomerID, TransactionDate, Credit, Debit, (SELECT SUM(Credit) FROM Transactions AS T2 WHERE T2.CustomerID = T1.CustomerID AND T2.TransactionDate = T1.TransactionDate AND YEAR(TransactionDate) = [Enter year:] AND ( T2.TransactionID = T1.TransactionID OR T2.TransactionDate T1.TransactionDate)) - (SELECT SUM(Debit) FROM Transactions AS T2 WHERE T2.CustomerID = T1.CustomerID AND T2.TransactionDate = T1.TransactionDate AND YEAR(TransactionDate) = [Enter year:] AND ( T2.TransactionID = T1.TransactionID OR T2.TransactionDate T1.TransactionDate)) AS Balance FROM Transactions AS T1 WHERE YEAR(TransactionDate) = [Enter year:] ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC; If the table has a single Amount column rather than Credit and Debit columns, just omit the second subquery, if bringing a previous balance forward: SELECT CustomerID, TransactionDate, Amount, (SELECT SUM(Amount) FROM Transactions AS T2 WHERE T2.CustomerID = T1.CustomerID AND T2.TransactionDate = T1.TransactionDate AND ( T2.TransactionID = T1.TransactionID OR T2.TransactionDate T1.TransactionDate)) AS Balance FROM Transactions AS T1 WHERE YEAR(TransactionDate) = [Enter year:] ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC; or for a zero-based balance: SELECT CustomerID, TransactionDate, Amount, (SELECT SUM(Amount) FROM Transactions AS T2 WHERE T2.CustomerID = T1.CustomerID AND T2.TransactionDate = T1.TransactionDate AND YEAR(TransactionDate) = [Enter year:] AND ( T2.TransactionID = T1.TransactionID OR T2.TransactionDate T1.TransactionDate)) AS Balance FROM Transactions AS T1 WHERE YEAR(TransactionDate) = [Enter year:] ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC; For an accounting year which is not the calendar year the following function can be used: Public Function AccountingYear(varDate As Variant, _ intStartMonth As Integer, _ intStartDay As Integer) As String Dim intYear As Integer If Not IsNull(varDate) Then intYear = Year(varDate) If intStartMonth = 1 And intStartDay = 1 Then ' accounting year is calendar year AccountingYear = intYear Else ' if date is before start of accounting year then ' accounting year starts in previous year If varDate DateSerial(intYear, intStartMonth, intStartDay) Then AccountingYear = intYear - 1 & "-" & intYear Else ' date is after start of accounting year so ' accounting year starts in current year AccountingYear = intYear & "-" & intYear + 1 End If End If End If End Function and instead of using the YEAR function the above function would be called, so for an accounting year starting 1st April you'd use: WHERE ACCOUNTINGYEAR(TransactionDate,4,1) = [Enter accounting year (yyyy-yyyy):] so for the current accounting year you'd enter 2009-2110 at the parameter prompt. Ken Sheridan Stafford, England reluctantcoder wrote: I want to use an update query to sum values of the previous twelve months. I tried using BETWEEN "#" & [date from] & "# AND #" & [date to] & "#" in the criteria to limit records summed. I get a error message telling me I did not include the and. I really would like to find a source that has the explaination of each symbol and the parameters of the built in functions. My dates are short date and time and always the first of the month. I have several years of data that needs the rolling sum calculated so using a fixed date would not work. thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200912/1 |
Thread Tools | |
Display Modes | |
|
|