I have a date field "yyyy/mm/dd"
The data is back up til 2009, but i only want to bring a rolling 12 months of data.
What should my query look like in Access
I did
Month()-12
This gives me an error Message ...
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.
Hi all,
I have a problem creating a query calculating a rolling total based on the last 12 months grouped by product.
The table:
Product no. / Period / Gross Rev / Clients
810 / 01-01-2006 / 1.000 / 10
810 / 01-02-2006 / 1.000 / 10
...
810 / 01-01-2007 / 500 / 10
990 / 01-01-2006 / 1.000 / 10
990 / 01-02-2006 / 1.000 / 10
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?
May be VBA is a better solution for this:
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.
Sample data attached.
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
blnDateRange = (Me.optDate = 1)
blnMonth = (Me.optDate = 2)
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:
is it possible to have a query carry out a running total, so that it counts the record above and the current entry.....
example
Record 1 Total 10 Running total 10
Record 2 Total 10 Running total 20
Record 3 Total 30 Running total 50
Record 4 Total 5 Running total 55
as i said i need this done in a query if Possible...
anyone help?
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
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;
Is there a way to make this work?
Hi,
this is my first question in this site.
I have a database with the following fileds and associated values
areacode dt rainfall
---------------------------
AAA 1/1/2005 10
AAA 2/1/2005 4
AAA 6
. 0
.
.
.
AAA 31/12/2005
SOME BODY HELP ME IN FINDING OUT ROLLING SUM OF RAINFALL
FOR THE WHOLE YEAR ,FOR EVERY FIVE DAYS.
To put it simple ,consider the following data
rainfall rollingsum
1 15
2 20
3 25
4
5
6
7
Also ,i would apprecite on tips how to address the fact that a leap year has 366 days in the above scenario.
Is there any way i can calculate a rolling average for a field in a record, based on the 10 previous records?
Cheers,
Ben
Have a table/query result
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?
How to code the rolling 12 months in a combo box?
(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).
please help me
i have a date field called open & i need to generate a query to count all entries by month, so far i can get all cases each day, but i'm unable to get the query to count them just based on the month & year..
any ideas please
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
What is the best way to pull all records in a rolling 365 day period. Like from 4/3/2013 to 4/4/2014?
View 2 Replies View RelatedI 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 have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.
I have a table full of dates of meetings through out the year...
Example
tMeetingDates
16th August 2005
18th September 2005
19th October 2005
23rd November
-----
i also have a report that i print out each month that has the date of the meeting on it... i currently edit the date manually.
I was wondering if there was a way to automate this facility, so that the report looked to the table of dates and looked for the current Months Date that is stored i the table.
i then want this date to be displayed in the Report.
So in this instance if i am running a report for tomorrows meeting being the 19th October it would display that date in the report....regardless of when i run the report...
Obviously if i run the report on the first of November because the month has changed it would then display the date of the November Meeting...
we only ever have one meeting a month!!!!
Please help
Andy
Using Access 2010..I have form with a date on it. For this control I have show date picker set to "For dates" and lo and behold I get calendar! I can scroll through this calendar month by month. Great if I just want to go back or forward a month or three. What I'd like to be able to do is scroll through the calendar year by year. Can I do this with the method I'm using at the moment? If not is there a way round it that isn't over complicated?
View 6 Replies View RelatedAnyone know if i can use DateAdd or something like in a query to show current month I want the user to be able to see records for the month we`re in Thanks all
View 1 Replies View RelatedHi Everyone,
I come to you guys for help once again,
Here is my query code:
Total Records: Count(IIf([Record #]=1 And [Date]=Month([3]),0))
I think I am formatting this wrong, I would like to query data based on the record number equaling 1 and the month in the [Date] field equaling March. Could anyone help me with the proper formatting of this? Thanks in advance!
Hi,
I was wondering is there a way to only QUERY day and month?
dd/MM ?
The issue is i need to build a report that has all the accounts opened within a certain month/time period. BUT its not year specific so i would need for instance.
01/02 to 28/02
dd/mm dd/mm
Note: The date stored in the DB is in this format dd/mm/yy < Standard format.
Is there a way to do this?
Your help is much appreciated... THANK YOU..
Cheers
Tristan F
Would someone be so kind as to tell me why I get Err in the Runtot field from this query ? It seems to run fine except no totals.
SELECT DatePart("yyyy",[Recdate]) AS AYear, DatePart("m",[Recdate]) AS AMonth, DSum("Creditamt","DatePart('m', [Recdate])<=" & [AMonth] & " And DatePart('yyyy', [Recdate])<=" & [AYear] & "") AS RunTot, Format([Recdate],"mmm") AS FDate
FROM TblPayments
GROUP BY DatePart("yyyy",[Recdate]), DatePart("m",[Recdate]), Format([Recdate],"mmm")
ORDER BY DatePart("yyyy",[Recdate]), DatePart("m",[Recdate]), Format([Recdate],"mmm");
I am trying to set up a query where the user inputs the month they want in "MMM" format (i.e. Jan or Dec). Additionally is there a way to run a query where the user is promted to select a value from a drop down menu? Thank you for your help in advance
View 2 Replies View RelatedHi everyone,
I am trying to design a query to pull all the records which have a birthday in a specific month (ex., all birthdays that fall in May). The table is imported from Excel. Currently, the format of the field is MM/DD/YYYY.
I'm not sure how to either format the birthday field so that I can run a query on it or how to design the query parameters to pull a specific month. Within the query, I need to pull all birth years and days in the month.
I'm a complete newbie to Access, so any help is appreciated! Thank you in advance.
Jess