Queries :: Dates In A Query - Show Orders By Month For Last 12 Months
Apr 22, 2013
I want to create a simple query from a list of orders dating back over 12 months.
The fields I have is Ord_Date, Qty
I need to show the orders by month for the last 12 months.
The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.
I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.
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 am wanting to get an expression that will return the difference in years and months between 2 dates. Specifically, I want to know peoples ages in years and months based on a person's D.O.B and todays date. I have managed to do it in years:
I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.
I could represent an activity using a colour i.e. yellow for delivery, green for design etc.
Also do I need to create a table with all the dates? Is there a quick way of doing this?
I need a query that lists each day of the month (or quarter) that is selected from a form. Meaning, if a user selects June 2005 on the form, the query will return to show every date from June 1, 2005 to June 30, 2005. Is this simple, tiny little thing possible?
The reason behind this is that on a report, I need to show data for every date. If there is not data entered for that date, it still needs to appear with the date listed, but with no entries represented.
i have to process 3 upcoming months bills .. say i want to show in query like this bill for Nov, Dec, Jan based on the issue date i mentioned earlier and the months to be find from the numeric month field like 3 i mentioned which mean 3 months ahead from issue date.
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012 April 2012 May 2012 June 2012 July 2012 August 2012 September 2012 October 2012 November 2012 December 2012 January 2013 February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
I have a query that is supposed to be showing todays data (as it is labeled) but it keeps pulling today's number date (i.e. the 1st, or the 17th) for every month, when i only want June 18th not including jan/feb/march etc. 18th.
I started running this query in may, that's why it only shows may and june's data.
I'm thinking it's definitely a problem with the criteria but check out the attachments and let me know
Date From - >=[ENTER Start Date] Date Till - <=[Enter End]
The results work fine to show which vehicles are booked between these dates, the only thing is I want to be able to show the vehicles that are not booked for the given dates in the parameter query is this possible?
I need the code for a filter or query which will show the past x (12, 24 etc) months worth of data. Each record has a date tag so i need a filter which will just show records for say the past 3 months.
I have created a query to limit the list in a combo box to display the months and years of the date field in a table. Below is the code that I am using. This works fine except this displays the years within the months - for example:
January 2012 January 2013 January 2014 February 2012 March 2012 March 2014 April 2012 May 2012 May 2014
I would much prefer it to sort by month and year:
January 2012 February 2012 March 2012 April 2012 May 2012 January 2013 January 2014 March 2014 May 2014
Code: SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName FROM MainTBL ORDER BY Month([QTDate]);
How to change this code to make it sort on Month and Year, not just month?
I am trying to figure out a logical WHERE condition. I have a Query that gives me data,
Code: Name | Start | End --------+-----------------+------------- Paul | 30-May-15 | Eugin | 21-May-15 | 28-May-15 Francis | 04-Mar-15 | 08-May-15 Samuel | 10-May-15 | 13-May-15
I want to have only Paul and Francis. As Eugin start is 21-05-2015 and End is the same month, so is Samuel's. So I want to eliminate those two records.I have checked pbaldy's web of overlapping records. But unlike that, I need something customised.
Begins and ends before range - we don't want - Yes Begins before, ends during - we want this one - Yes Begins and ends during - we want this one too - NO Begins during and ends after - we also want this one - Yes Begins and ends after - we don't want this one - Yes Begins before and ends after - we want this one -NO
My current database creates an invoice per order. Until now that worked wonderful. Now i want to create an invoice per month per customer with multiple orders on the same database. So i will have to create a new table/query on the existing order table.
Have a crosstab query for a running 12 months that is used for a report and I noticed today that each month is correct except that it is also picking up Feb of last year as well as this year. All other months are good. No other pre-queries have any date ranges in them. Anyone know what I have to do to prevent this problem? Below is my SQL.:confused:
TRANSFORM Sum([92660ScorecardUnionName].Hrs) AS SumOfHrs SELECT [92660ScorecardUnionName].EMPID, [92660ScorecardUnionName].Category, [92660ScorecardUnionName].OPID, [92660ScorecardUnionName].NAME, [92660ScorecardUnionName].Description, Avg([92660ScorecardUnionName].Hrs) AS [Avg Of Hrs] FROM 92660ScorecardUnionName GROUP BY [92660ScorecardUnionName].EMPID, [92660ScorecardUnionName].Category, [92660ScorecardUnionName].OPID, [92660ScorecardUnionName].NAME, [92660ScorecardUnionName].Description PIVOT Format([Date],"mmm") In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug',' Sep','Oct','Nov','Dec');
Sorry the title dosen't make much sense, can't come up with a good title. Here's the problem, I work in a hospital and I have a program that store patient's admit date and discharge date. My concern is, I need to calcuate patient days (the amount of days the patient stay in the hospital). Let's say a patient was admitted on Jan 15th and discharged on March 20th. So I need to build a query that capture the months, let's say I do it on Feb, how can I tell that this patient was here on Feb? since I have admit date and discharge date. Thanks
eg. we have 4 patients John Smith, Ortiz Prerez, Jim Johnson and Nancy Cruz and we are running the query for Feb.
ID Name AdmitDate Discharge Date "The result should be" 1 John Smith 1/2/07 1/26/07 24 patient days 2 Ortiz Perez 1/27/07 3/4/07 28 patient days (because this user was in the unit) 3 Jim Johnson 12/25/06 Null 28 patient days because of Feb month 4 Nancy Cruz 2/14/07 4/1/07 14 patient days
I am needing a fairly simple query to show records from a table that occurred between a set of dates specified by the user; I have quickly built the query using the wizard, however the date is stored as DateTime;
In the criteria, I tried - Between [Enter Start Date:] And [Enter End Date:] which returns an empty result (presumably because there are no records that match exactly, as they all have the time included?)
Is there a way to have the query show all results between the dates specified by the user, regardless of the time?
The table I want to run the query on has a date in the format of dd/mm/yyyy. I want to be able to run a query so that the user can type in a month and all records with dates in that month will be shown.
I have done this before many years ago but now have a mental block and cant figure it out.
I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17. Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.
Option Compare Database Option Explicit
Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String On Error GoTo Err_Handler
If IsNull(StartDate) Then getTimeElapsed = "No startdate" Exit Function End If
Dim YY As Integer Dim MM As Integer Dim DD As Long
' use current day if EndDate is not supplied, ' + 1 makes both days inclusive. EndDate = IIf(EndDate = 0, Date, EndDate) + 1
MM = DateDiff("m", StartDate, EndDate) DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
' recalculate if DD is negative. If DD < 0 Then MM = MM - 1 DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate) End If
YY = MM 12 ' integer division. MM = MM Mod 12 ' remainder.
getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _ MM & IIf(MM < 2, " month ", " months ") & _ DD & IIf(DD < 2, " day", " days") Exit Function
I need to find Customers who have not had a meeting for over three months and i need to list them in a query, please could someone help me as this is getting very frustrating
I'm having problems figuring this one out -- I'm fairly new to access. I have included a JPG attachment that shows the information I currently have in use and what I would like. I need a SQL statement that will generate the rolling 12 months by period.
The end results will be a table that is populated with the rolling 12 month values so I can qry a sharepoint infopath form to look up the rolling value (look up against lng_PERIOD and chr_EE_RACF) to populate the YTD values.
'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'
in a query by form.
The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.
How can I change it so that I don't have to enter dates into the date reported fields for results to show?
I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).
As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.
