Queries :: Running Total Query Not Calculating First Fiscal Year
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:
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));
I need to provide a report on data over a fiscal year that has the following format:
apr 03 may 03 jun 03 q1 total jul 03 aug 03 sep 03 q2 total oct 03 nov 03 dec 03 q3 total jan 04 feb 04 mar 04 q4 total year total
I can group by quarter and sum() in the group footer which works, but when I group by year I get totals for 03 and 04 separately. I have also tried sum() and dsum() in the page footer, which gives #error, and vba in page footer:
Dim pageTotal As Long Sub Detail_Print(Cancel As Integer, PrintCount As Integer) ' Add to Page Total pageTotal = pageTotal + Me![ClientsServed] End Sub Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer) ' Show Page Total Price Me![yrClientsServed] = pageTotal End Sub
which calculates properly in the detail section (var watching "pageTotal") but outputs nothing in the footer, though the name of the text box in the footer is correct. If anyone can see an error in what I'm doing or suggest another approach, I would really appreciate it, this is driving me nuts.
Product (Table PDZRN1) Whse (Table PDZRN1) Description (Table PDZRN1) On Hand (Table PDZRN1) Unit Cost (Table PDZRN1) Unit Price (Table PDZRN1) LastDate: Date (Table Sales_History) (using the Max function)
Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:
Field - FY2013Sales: Sales Table - Sales_History Total - Sum Criteria - <= 04/01/2012 and <= 03/31/2013
how to link my criteria back to the "Date" field, or if I'm even going down the write path.
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
If my fiscal year ends 12/31 but the pay period doesn't end until 1/10, how do I get a query to tell me there are 25.3 pay periods remaining (counting from the last pay period 1/11)?
So far, in my query, I have a column for Quarters and under the field of that column I have put a statement:
Quarter: IIf(Month([VisitDate]) In (4,5,6),"1",IIf(Month([VisitDate]) In (7,8,9),"2",IIf(Month([VisitDate]) In (10,11,12),"3","4")))
The statement works fine but the problem is it does not tell me which year of does that quarter belongs to. Example: whether it is Quarter 1 of 1999 or Quarter 1 of 2000. Right now it only output either 1 or 2 or 3 or 4.Therefore, is there a way where I can command to give me an output like "Quarter 1, 1999", "Quarter 2, 1999", "Quarter, 1, 2000". Basically with respect to the Year that I have in my data?
I want to build a query that calculates the fiscal year and the month from a date on-the-fly. I tried to do it in VBA, but it's more complicated than in query I guess. So this is my table:
The date is on the left, in the middle I want to have calculated Jan 14 and on the right I want 14/15.
In VBA I started with this: strMonth = MonthName(Month(A), True) & " " & Format(A, "YY") which gave me "Jan 08". But the date was hard-coded and not from my table. Then I tried to store the information by SQL statement into a string. But this also didn't work, the types were different.
Code: UPDATE tblSAPOD SET sapOD_month = Year([sapOD_OrderDate])-IIf([sapOD_OrderDate]< DateSerial(Year([sapOD_OrderDate]),6,16),1,0) WHERE sapOD_OrderDate Like "*/*/94";
.. but I guess it's wrong, it gives me a syntax error.
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.
I am creating a line graph from a running total query to show our income from items shipped for each month. Currently I have the following Code in my query which works but it displays the month as a number in my graph and I would like it to show the Month name.
Code: SELECT DatePart("yyyy",[ShippedDate]) AS AYear, DatePart("m",[ShippedDate]) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND DatePart('m', [ShippedDate])<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot FROM tblJobs WHERE (((tblJobs.ShippedDate) Is Not Null)) GROUP BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]) ORDER BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]);
I tried this solution, but I get an error in the RunTot field, I'm assuming because Access can't use the month name in dsum.
Code: SELECT DatePart("yyyy",[ShippedDate]) AS AYear, MonthName(DatePart("m",[ShippedDate])) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND MonthName(DatePart('m', [ShippedDate]))<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot FROM tblJobs WHERE (((tblJobs.ShippedDate) Is Not Null)) GROUP BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]) ORDER BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]);
How do you grab a custom Fiscal Year's values based on the system clock's date?
I am building a query where I want to see the number of closed cases based on the current custom fiscal year with the system clocks date. The report that it feeds only cares about the current FY.
I need the System Clock's FY value in this query
Code: SELECT shortname AS Station, NZ(TotalCount.TotalCases,0) AS [Cases Complete] FROM StationList LEFT JOIN (SELECT station, count([Open Issues].ID) AS TotalCases FROM [Open Issues] WHERE [Status]="Closed" GROUP BY Station) AS TotalCount ON StationList.shortname =TotalCount.station;
Within the Query Open Issues I have the FY broken up
I am using the following UNION QUERY to total up equipment tested for a report.
SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS FROM [LAPTOPS] WHERE (((Date)Between [Enter Start Date] AND [Enter End Date])); UNION SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS FROM [WORKSTATION]
[code]...
I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?
I have a running total query that seems to run but when I try to total the query results then Access will be "Not Responding". I tried to change it to a Make Table query because I need to use the running total result in another query. So I created a table but when I try to run the make table query it just says "Run Query" at the bottom. Here is the query:
SELECT [OTMissing].[Employee], [OTMissing].[AsOf], [OTMissing].[HRsEarn], (SELECT Sum(OT1.[HRsEarn]) FROM [OTMissing] As OT1 WHERE OT1.[Employee]=[OTMissing].[Employee] AND OT1.[AsOf] <=[OTMissing].[AsOf]) AS RunningTotal, [OTMissing].[RemainPP] INTO OTGenerated FROM [OTMissing] ORDER BY [OTMissing].[Employee], [OTMissing].AsOf;
My OTMissing query is 47061 rows. Does that have something to do with it? The only other thing it might be is that most of the records have 0 although I'm not sure why it would be a problem I thought I would at least mention it.
Below is a sample from the query. I guess Im just not sure where to start since there are multiple months the same value, but i need them to total up..
Code: SELECT DISTINCTROW tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy') AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks FROM tblBlotter GROUP BY tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy'), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;
I have created a query in Access 2000 which contains a date field in short date format.
I would like to add a field to this query which calculates the financial (fiscal) year from my date field. Our financial year runs from Apr 1st to Mar 31st.
I'm totally stuck - any ideas would be greatly appreciated!!
I'm trying to change the way date calculations are done in a training db. I show them now in an expression in my query as Between Date() And Date()-365, but I need to show them based on a FY beginning 7/1 of any year and ending on 6/30 of any year. Any suggestions?
Function GetFiscalYear(ByVal x As Variant) If x < DateSerial(Year(x), FMonthStart, FDayStart) Then GetFiscalYear = Year(x) - FYearOffset - 1 Else GetFiscalYear = Year(x) - FYearOffset End If End Function
I call on the function in my query to determine the FiscalYear for a date. Expr1: GetFiscalYear([ProdDate])
It sends back the correct Fiscal Year. However, when I try to put criteria in for Expr1 to show only Fiscal Year 2007 instead of all fiscal years, it gives me a data mismatch type error.
The [ProdDate] has the following formula: Prod_Date: IIf([40Day]="True",DateAdd("d",-40,[Date Code]),DateAdd("d",-50,[Date Code])) where [40Day] returns True or False to determine if I should subtract 40 or 50 days from the Date Code. The Date Code is in the format ##/##/#### and is sometimes null.
I have tried everything I could think of and am completely stumped. Thanks in advance for any help!!!
I have a list of products that have a loan payment associated to them. To cover these loans, we have incoming revenue for each product at different dates.
The incoming revenue is a field of running sum of revenue for each product.
Desired output:
I want to how how much % of loan (jn total; for all products) is paid in October, november and december and as such (as cumulative). i.e. total of 40% in Oct, 70% in Nov and 100% by Dec etc.
I would like to sort records based on fiscal year for a chart. The fiscal year would be 7/01/2003 to 06/30/2004. I would like to sort this based on oldest date to newest date. This query could span several fiscal years. Any help would be greatly appreciated.
