From this table I want to create a rolling total so that the rolling gross rev of product 810 MAR07 is based on APR06-MAR07 data. The same applies to the number of clients. Is this possible?
E.g. Rolling sum of clients in OCT07 = No. of Clients (NOV06+DEC06+JAN07+ ... +SEP07+OCT07) Rolling sum of clients in JUN07 = No. of Clients (JUL06+AUG06+ ... +MAY07+JUN07)
The problem is that the output has to be a table/query (a complete list) with following data: - Product - Period - Gross Rev (prenst) - Clients (present) - Rolling Gross Rev (last 11 month + the present) - Rolling Clients (last 11 month + the present)
The table is to be exportet to Excell and used in a pivot.
I have tried using the Dsum function but I have only managed to calculate a running total (not based on the last 12 months). Can somebody help me here?
I have a table comprised of/grouped by agency, payer source and billing date summing dollars the order/display of the data is the same as the grouping; that is, ordered by agency, payer source, and billing date. The billing date is just every Tuesday of every week. Every once in a while, there is a negative value in terms of dollars for a particular agency/payer source/billing date category. When this happens, a voucher isn't created and in billings that follow that 'rolling negative' amount must be accounted for. Is there a way in which I can produce a value on the line that adds the rolling negatives from prior weeks if they exist? An example is below:
Agency Payer Source Date of Billing Dollars Amt to Release ABC Patient 1/28/13 $500 $500 *full amt releases because no rolling negatives ABC Patient 2/04/13 $600 $600 *full amt releases because no rolling negatives ABC Patient 2/11/13 $-200 *nothing released because negative amt ABC Patient 2/19/13 $300 $100 *$300 - $200 released because a negative occurred in prior week
Sr.NO Name Amt.Tot Amt.Needed 1 Mark 100 24 2 Mark 100 80 3 Tom 150 12 4 Tom 150 45 5 Joe 50 23
Need to add a field which is Amount.LEft so that the table looks like this
Sr.NO Name Amt.Tot Amt.Needed Amt.Left 1 Mark 100 24 76 2 Mark 100 80 -4 3 Tom 150 12 138 4 Tom 150 45 93 5 Joe 50 23 27
The last field is Amt.Tot - Amt.Needed and this should roll over and subtract for all transactions of Mark, then restart again for Tom and then again for Joe. Let me know if there is any confusion on that.
I have developed a new database using Access 2003, which I need to roll out across various users. Some have XP, some Windows 7, and each of them has different size screens. I guess I will have to adjust the various forms to size to a particular users screen resolutions and sizes.
Is there any way of doing this within the database itself, or do I have have to adjust each form according to the local users PC specification?
(1) When I click on the combo box this month, the first value should be 2015-07 (yyyy-mm) and the last one should be 2016-06 (2) When I click on the combo box next month, the first value should be 2015-08 (yyyy-mm) and the last one should be 2016-07.
The combo box will check today's month and will automatically generate the 12 rolling months (include this month).
I have a query that takes a date from a selection form. Is there a way to window my data by taking this date and going back 12 months to give me a block of data covering this 12 month window.
Example: Date entered is 03/dd/06 Date window desired is 04/01/05 thru 03/14/06
Is there a way to calculate three different rolling averages in one query?
I just inherited a database where someone is using three queries to capture the same information only with different time frames. They were calculating a rolling three month average, six month average, and twelve month average. I would like to combine these queries into one to reduce time spent running reports from the database. All three queries are based on one table. One of the columns in that table is called "Month Start Date". That field shows the first day of the month when a call was entered. I can get the query to tell me the first month in the three month period and the first month in the six month period, but I can't get it to calculate the averages of the calls that fall in those time frames. Here is the SQL for the query I have now. When I try to run this, I get the error message that my formula is not part of an aggregate function.
Code: SELECT DISTINCT DateAdd('m','-2',(Max([Month Start Date]))) AS ThreeMonthStartDate, DateAdd('m','-5',(Max([Month Start Date]))) AS SixMonthStartDate, Max([Month Start Date]) AS MaxStartDate, IIf([Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' ') AS ThreeMonthAverageCallRate, LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num] FROM LIST_WITH_TNC;
Say for Material A: I need Access to see that the 1st date is 05/01/2013 and say 365 days out or 12 months (05/01/2013-04/01/2014) the average is 158.6 then 06/2013-05/2014 the average is 146.667 and so on.
Every month is not going to be listed in the results, if there is no month then assume 0.
I wanted to know if it is possible to set a rolling year based on the first recorded date for an individual. In other words, my table is updated every time an employee receives a point. The record includes the employee's name (empName), the date (dateOfOccurrence), and the point (occurrencePoint).
I would like to create a query that would be based on a rolling year from the very first record of an employee based on the first dateOfOccurrence. The first date/record of this employee would be the start of the rolling year. I am writing this in SQL in Access 2013.
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
I have a query that selects usage records from a table where a data field falls within a rolling 12-month period that ends on a month and year selected by the user on a form.
PARAMETERS [Forms]![frmReport]![cboMonth] Short, [Forms]![frmReport]![txtYear] Long; SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((DateDiff("m",[dtUsage],DateSerial([Forms]![frmReport]![txtYear],[Forms]![frmReport]![cboMonth],1))) Between 0 And 11));
I also have a query that pulls data between two dates that the user specifies using two textboxes on a form.
SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtStartDate] And [Forms]![frmReport]![txtEndDate]));
On the form I have an Option Group control that sets the visible properties of the 4 controls; 12-Month Rolling cboMonth - Month Combobox txtYear - User entered year Date Range txtStartDate - User entered Start Date txtEndDate - User entered End Date
What I was planning on doing was adding two new textboxes (txtQryStart & txtQryEnd) and depending on what option is selected, change the dates accordingly
If blnDateRange Then Me.txtQryStart = Me.txtStartDate Me.txtQryEnd = Me.txtEndDate End If
If blnMonth Then If Me.cboMonth = 12 Then EndMonth = 1 EndYear = Me.txtYear + 1 StartMonth = EndMonth StartYear = Me.txtYear Else EndMonth = Me.cboMonth + 1 EndYear = Me.txtYear StartMonth = EndMonth + 1 StartYear = Me.txtYear - 1 End If Me.txtQryStart = DateSerial(StartYear, StartMonth, 1) - 1 Me.txtQryEnd = DateSerial(EndYear, EndMonth, 1) End If
Now I can use one query to accomplish both types of query; SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtQryStart] And [Forms]![frmReport]![txtQryEnd]));
This approach works. It saves me from having to duplicate the queries and some future headache if anything needs to be changed in the queries.
What I was wondering is there an better/simpler way to do this? :confused:
