Running Sum Resetting Each Year
Oct 17, 2006
Trying to calculate a YTD running sum by Month where the cumulative amount resets the next year as below.
AYearAmonthSumOfFreightRunTot FDate
19951$2,798.59$2,798.59Jan
19952$1,675.06$4,473.65Feb
19953$2,165.37$6,639.02Mar
19954$1,661.66$8,300.68Apr
19955$3,166.25$11,466.93May
19956$3,461.40$14,928.33Jun
19957$1,852.65$16,780.98Jul
19958$2,458.72$20,527.88Aug
19959$3,078.27$25,003.32Sep
199510$3,237.05$29,363.85Oct
199511$3,934.70$34,708.77Nov
199512$2,019.68$38,990.68Dec
19961$3,757.96$3,757.96Jan
19962$5,395.28$9,153.24Feb
19963$4,341.10$13,494.34Mar
19964$5,379.02$18,873.36Apr
Here's my practice SQL (in NorthWinds.mdb
SELECT DatePart("yyyy",[OrderDate]) AS AYear, DatePart("m",[OrderDate]) AS Amonth, Sum(Orders.Freight) AS SumOfFreight, Val(DSum("Freight","Orders","DatePart('m',[OrderDate])<=" & [Amonth] & " And DatePart('yyyy',[OrderDate])<=" & [Ayear] & " ")) AS RunTot, Format([OrderDate],"mmm") AS FDate
FROM Orders
WHERE (((DatePart("yyyy",[OrderDate])) Between 1995 And 1996))
GROUP BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]), Format([OrderDate],"mmm")
ORDER BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]), Format([OrderDate],"mmm");
Here's what I get:
AYearAmonthSumOfFreightRunTotFDate
19951$2,798.59$2,798.59Jan
19952$1,675.06$4,473.65Feb
19953$2,165.37$6,639.02Mar
19954$1,661.66$8,300.68Apr
19955$3,166.25$11,466.93May
19956$3,461.40$14,928.33Jun
19957$1,852.65$16,780.98Jul
19958$2,458.72$20,527.88Aug
19959$3,078.27$25,003.32Sep
199510$3,237.05$29,363.85Oct
199511$3,934.70$34,708.77Nov
199512$2,019.68$38,990.68Dec
19961$3,757.96$6,556.55Jan
19962$5,395.28$13,626.89Feb
19963$4,341.10$20,133.36Mar
19964$5,379.02$27,174.04Apr
19965$6,481.29$36,821.58May
19966$597.36 $40,880.34Jun
Anyone have an idea for correcting this problem?
Thanks in advance,
Barb
View Replies
ADVERTISEMENT
Aug 5, 2014
I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:
FYear BudgetedCostIndCont Commitment
2010
2011 8585643 4742000 3843643
2012 2297116511432165 11539000
2013 3618726216963282 19223980
2014 4457769020706644 23871046
2015 4963815023206644 26431506
As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:
Code:
SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1
AS FYearExport
FROM tblProject
GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted
HAVING (((tblProject.FPAccepted)=True));
and the Aggregate query:
Code:
SELECT qryDashboardChart1.FYearExport,
DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])
[code]....
I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.
View 8 Replies
View Related
Apr 25, 2006
I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.
For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006
THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.
What do you suggest? Many thanks.
Mehran
View 7 Replies
View Related
Apr 25, 2014
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I have been trying to tweak this:
SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))
but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"
View 2 Replies
View Related
Aug 1, 2005
I trying to figure out how to make this query work. I have a simple database that is being used to show employee employment information - name, hire date, salary, bonuses, etc. Everything is just about done but they want me to show what percentage of the prior year the employee was there. In other words if an employee was hired 4/20/2004 they want me to show the percentage of 2004 they were employed with the company. I've tried just about everything I can think of but nothing seems to give me the right answer. I am also showing the percentage for the current year (2005) and that works ok. Just can't figure out how the calculate it for a prior year.
This is being done in a query and we're using Access 2000.
Any help would be greatly appreciated.
Thanks,
View 6 Replies
View Related
Jul 3, 2014
I have fields [DayOfYear] and [Year] can I somehow produce the dd/mm/yyyy from this. I know how to do it in Excel but the Asscess function Date() is a little different.
I.e. if [DayOfYear] =152, [Year] = 2014 then [Date] = 2/6/2014
View 6 Replies
View Related
Jan 10, 2012
I'm going to try using the year, day of year, hour & minute (24 hour clock) as a report number. It's set up in a field on a table. Right now I have....
Default Value =Format(Now(),"yyyhhnn") 'which works but not exactly how I would like
yy = Last two digits of the year
y = Number of the day of the year (1 to 366) 'can this show three digits all the time?
hh = Hour in two digits (00 to 23)
nn = Minute in two digits (00 to 59)
For instance, right now for Jan. 10th, 2012, 1306 hours the result would be 12101304 which, for all intents and purposes works, but I would prefer the "day of the year" to always be represented by three digits and not just when it hits day 100 of the year.
I would prefer to see 120101304
View 4 Replies
View Related
May 3, 2006
Hi there gurus...
I have made a form with various text-boxes, radio-buttons and other controls on it. The form is not bound to a table or query: I'm just using the form as a data-gathering device for my VBA code. After the user has entered the data I want to use a button to reset the boxes and controls to the way they were when the form was openened, ie as though the user closed the form down and re-opened it again. I know I can do this by resetting the .value of each control back to its default, but there's about 30 controls to reset! Is there not something like "forms!main.reset" that just resets all the controls a form back to their default values, clears out text boxes, etc etc as though the user has closed & reopened the form?
Ta
John
View 12 Replies
View Related
Oct 11, 2005
here the scenario:
i have a page displaying all the contents of my table with the first field a autonumber as a button on the page.
when i click the button, (Ex. number 15) it brings me to another page where i can delete the clicked entry.
after deleting, i go back to the viewing page, because i deleted number 15, it now shows, 1,2,-----14,16,--- so on so forth.
how can i reset the autonumber so that when i go back to the viewing page the numbers are in order again?
hope u got my point. thanks for any help u can give.
View 3 Replies
View Related
Sep 15, 2005
could someone please tell how i can reset an autonumber field to start from 1.
basically i have created a table and carried out a number of test with useless data. now that i have the table set up the way i want it and i have tested the queries etc i want to delete all the current data in the table and begin filling it with correct data. however i want my primary id to start from 1 which at the moment it doesn't because i have just deleted 50 records.
thanks in advance
View 2 Replies
View Related
Mar 5, 2006
I have a quick question. I have a table that has a field with a seed identity with an increment of 1. Right now I have records in the table ranging from 1965001 - 1988704. Every so often the oldest records are archived off to another database. However, I have just been informed that the records from this table can not exceed 2000000 in the seed identity field. Ideally, I would like to just go into the table design view and change the seed identity to 1000001 and leave all of the existing records as they are, knowing that it will be years before it causes an issue. I've tried this in the lab and it didn't work, the seed identity issued was always 1 higher than the highest record in the table.
My plan B is to export all of the records out of the table, change the seed identity, and append the records issuing a new identity. The problem with this is that the seed identity is referenced in other tables, which would have to updated.
The easiest solution for me would be to be able to just change the seed identity and start issuing new seed identities, leaving the existing records alone.
I am no Access expert so please forgive me if my approach is way off.
View 3 Replies
View Related
Jan 4, 2007
I cleared a table of records and need to restart (at one) the autonumber field. I used the instructions found on on-line help but they will not reset the field back to one. Any one have a solution. I have even deleted the field and created a new one with the same name and it started with the same value it would hav used before I deleted any records.
View 3 Replies
View Related
Feb 7, 2005
I have a Form/subform that is used to allow editing of a user selected record from a table called personnel. When the form/subform is first opened, all text boxes are blank. The user selects the desired record from a combo box that uses a dropdown list to display all records of the table. When a record is selected, the contents of that record are displayed in the subform in which the user can then edit/modify. The user then clicks on a "save button" which then saves the record.
I want to use a msgbox that will notify the user that he has just updated a record and ask if he/she would like to update another record. If the answer is NO, then close form/subform. If answer is YES, I would like to clear the combo box and the subform text boxes. then the user can them start the process over by selecting the desired record from the combo box.
When I placed the msgbox coding into the "save" button's on click property, the NO portion worked fine. However, when YES is selected, the combo box would clear but the subform texboxes still show the previous record data (does not clear).
Can anyone provide some assistance?
Here is the code for the main form:
Sub SetFilter()
Dim LSQL As String
LSQL = "select * from personnel"
LSQL = LSQL & " where last = '" & cboSelected & "'"
Form_Editpersonnel_sub.RecordSource = LSQL
End Sub
Private Sub cboSelected_AfterUpdate()
'Call subroutine to set filter based on selected last name
SetFilter
End Sub
Private Sub Form_Open(Cancel As Integer)
'Call subroutine to set filter based on selected last name
SetFilter
End Sub
Here is the code for the subform "save" button:
Private Sub SAVE_Click()
On Error GoTo Err_SAVE_Click
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close
If MsgBox("You have updated information on a Detachment Member. Do you wish to update another member?", vbExclamation + vbYesNo + vbDefaultButton2, "WARNING") = vbNo Then
DoCmd.OpenForm "PERSONNEL MANAGEMENT"
Else
DoCmd.close
DoCmd.OpenForm "Edit personnel"
End If
Exit_SAVE_Click:
Exit Sub
Err_SAVE_Click:
MsgBox Err.Description
Resume Exit_SAVE_Click
End Sub
View 2 Replies
View Related
Dec 1, 2006
I have a number of tables (I know someone is going to say you don't need a primary field for these tables but I want to use one) that are basically lookup tables for combo boxes. Generally I use an autonumber to identify the ID of each record. There are occasions when all the records need to be changed (i.e. delete all old records) - does anyone know how I can programmatically (or otherwise) reset the autonumber from the last used back to "1" if all records from the table are deleted.
Beeky
View 4 Replies
View Related
Mar 14, 2006
I have a query based on payment date which I have extracted the Year part as a seperate Field StartYear, but I want to now add EndYear which just adds 1 year to the StartDate. e.g. EndYear = StartYear +1. Anyone kow please I know i's proably simple but I keep getting syntax errors.
View 3 Replies
View Related
Jul 3, 2007
I'm redeveloping a DB for a new project, so have removed all previous records from relevant tables (to start a-fresh)
One snub, the ID fields in the tables are auto increasing from where they left off, rather than from 1
If anyone knows how to reset the auto counters, it would be very much appreciated
Thanks in advance
View 4 Replies
View Related
Feb 9, 2005
I have five Option Groups on my form. I was running into a problem where after selecting options for one record and then moving onto the next record, the previous options would stay selected for the new record.
I "thought" I had found a solution by setting the Option Group values to Null in the OnCurrent event of the form, but I found a problem in that when going back to a previous record, it is nulling out the previously selected options. Doh!
Any ideas on how to get around this problem? I want to be able to go from one record to the next without carried over options, but I also want to be able to go back to already entered records without having them nulled out.
View 3 Replies
View Related
Mar 2, 2005
I have a couple of fields on my form (Resolution Type, Date Closed) that I want to display only if the Status field has Closed as its selection for each record. I initially set the Visible parameter of both the type and closed fields to False and I reset them to visible by checking for open/closed with an afterupdate event. This works great for the first record but...subsequent entries display those 2, regardless.
I know just enough regarding events to struggle my way through on some things but more advanced field, record level and form level events are beyond my experience level.
Can anybody offer any advice on how to get those two fields to toggle visible/invisible when scrolling through the records?
With that said, does anyone have a link to a post/website referencing this subject?
Thanks in advance,
Mike
View 1 Replies
View Related
Apr 1, 2006
I have a form that I use to collect selection criteria for reports. The controls on the form are combo boxes and date fields. After I run the report, I would like all of the fields on the form to be cleared of the selected values that were used to run the previous report. I have tried the 'repaint' method but it does not work.
Any suggestions?
Thanks,
John
View 2 Replies
View Related
Sep 11, 2013
I am fairly new with Access and VBA and am having troubles with the following. I filter a second combo box "cboTagNumber" with the first combo box "Combo133". The problem is when I clear the first combo box, the second combo box remains filtered. Is there an easy way to clear this?
This is the code:
Private Sub Combo133_AfterUpdate()
Dim strSource As String
strSource = "SELECT ID,[Tag Number] " & _
"FROM [E&I Table] " & _
"WHERE System = '" & Me.Combo133 & "' ORDER BY [Tag Number]"
Me.cboTagNumber.RowSource = strSource
Me.cboTagNumber = vbNullString
End Sub
View 10 Replies
View Related
Apr 4, 2013
On a form I have 2 list boxes and a subform in a cascading arrangement. When the form is opened the first list box is populated with data from a query. When an item is selected in List Box 1 then entries appear in List Box 2 from another query. When an entry in List Box 2 is selected then entries appear in the Subform from a third query. It's all working fine except for this:- if a new selection is made in List Box 1 then I want all entries in the Subform to be cleared until something is selected in List Box 2, but I can't see how to do it.
I've tried setting the Subform's recordsource to null, to " " and to "" but none of these work properly. They either give an error or leave a single entry in the subform with #Name? in every text box.
It does work if I use a List box instead of a subform but that's not what I want here.
View 2 Replies
View Related
Mar 13, 2008
I have a primary key field called "Issue Number", with the properties set to auto-number.
After testing, I need to set it back to zero before going live.
I deleted all the records and did a compact and repair, but the first number that comes up is the one after the last one used during testing.
Help????
Thanks
View 14 Replies
View Related
Jan 19, 2013
I have a form for student attendance that is bound to a query and stores a temporary value for ClassesAttended in a StudentEnrollment table as faculty enters the attendance. They then run an append query to write the temporary records to a StudentAttendance table. Because the ClassesAttended field is bound, when the form is opened, it recalls the last number entered for that student in that class as entered by the faculty the last time attendance was updated. I tried leaving the field unbound, but the first value enter into the first record of the form is updated automatically to all subsequent records.
Is there a way to change the properities, use code, etc. to assigne a null or 0 to the ClassesAttended field when the form opens, without the first updated record to propagate through the reaming records?
View 3 Replies
View Related
Mar 14, 2005
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
Many thanks in advance.
Peter
View 2 Replies
View Related
Aug 20, 2004
I have created a form in access that contains a multiple select list box and a command button "New Record," that creates a new record. I select values in the list box for the current record. When I click on the "New Record" button, the values that I selected in the previous record are still selected. How can I reset the list box so that no values are selected when I create a new record?
View 8 Replies
View Related
Oct 8, 2013
Occasionally a user of mine needs to reset a sequence number in a table.
A few months ago I made him a simple app that has two text entry boxes and a button. The first text entry box uses a dlookup:
Code:
=DLookUp("sSessionNumID","qryGetAPIsessionLastValue")
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
View 2 Replies
View Related
