Queries :: Crosstab Query Using Dates For Next 12 Months
Jun 23, 2014
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?
View Replies
ADVERTISEMENT
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.
View 6 Replies
View Related
Nov 4, 2013
I have generated a table using a crosstab query and it gives the information as below
12/10 12/11 12/12 13/01
Part 1 1 2
Part 2 4 4 4
Part 3 5
So basically part, qty used, year/month used - it works fine up to this point.
Now I want to either add more columns to the crosstab query to count number of times Part 1 was used in last 12 months & 24-13 months
Or
Create another query to do this.
But I want the count function to start counting from current month to last 12 months and from month 13 to month 24 (in another column).
The problem I am having is that this query will run every month and I don't want to update the column headers (in query property sheet) instead I want query to pick this up automatically.
I use the design view to generate queries.
View 1 Replies
View Related
Jun 28, 2005
Is there something I can put into the criteria of a date box to only use the last 6 months. I dont want have have to do this type of criteria: >12/1/2004. I want it to just use information from the last 6 months no matter what the current month is. If anyone knows what I am trying to say, please help me out! Thanks
Bowes
View 4 Replies
View Related
Nov 30, 2005
Ok,
I have a table with 3 fields Line address, Availability, Relevance Date
Every month i put in the data given and i have back from june (so i dont actually have 12 months.
At the moment i am grouping by line address(Row header) and datepart("m",[relevance date]) - (column header) and have the availability as a value and sum. Because i only have data from june it only gives me 6,7,8,9,10. How do i get it to always display 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and just put the availability into the relevant months and put nothin in the others.
Current SQL: -
TRANSFORM Sum(Monthly_cleansed_data.Availability) AS SumOfAvailability
SELECT Monthly_cleansed_data.[Line Address] AS LineID
FROM Monthly_cleansed_data
GROUP BY Monthly_cleansed_data.[Line Address]
ORDER BY Monthly_cleansed_data.[Line Address]
PIVOT DatePart("m",[Relevance Date]);
Thanks
k0r54
View 2 Replies
View Related
Mar 22, 2013
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]))
View 2 Replies
View Related
Jun 18, 2015
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
View 14 Replies
View Related
Oct 10, 2005
Hello,
I have a crosstab query with the following column which calculates the number of days an employee took to perform a task. Problem is that it is also counting weekends and holidays.
Field: LogDate
Table: tblWorkLog
Total: Count
Corsstab: Value
Is there anyway to exclude weekends and holidays from that value?
I have a table, tblHolidays, with the field, HolidayDate. Is there a function that I can use like the WeekDay and use that with the HolidayDate field so that I can only get the Number of Work days?
***Addition***
Sorry I would also like to clarify something I have only one date field. What's happening is the user logs in and inputs what he did for the day, and the date is logged.
So the crosstab query sums up those days, but I would like to exclude weekends and holidays.
View 5 Replies
View Related
Mar 10, 2006
I print a report based on Query1, with the selection criteria for dates being taken from 2 fields on a form. The selection criteria reads:-
>=[Forms]![DTdates]![StartDate] And <=[Forms]![DTdates]![EndDate]
Simple and it works fine.
I now need to base a second report on a Crosstab query based on Query1. Follow me?
Now, I have tried using the selection criteria in both Query1 and in Crosstab_Query1 and I get the error message:- "The Microsoft Jet database does not recognise 'Forms!DTdates!StartDate' as a valid field name or expression."
How do I get round this or can I?
The bottom like is I want to fire off printing the report from dates input to a form.
Help please.
View 8 Replies
View Related
Nov 5, 2007
Hello,
I have this so far:
TRANSFORM Count(tblclientcontacts.ENTERED_BY) AS CountOfENTEREDBY
SELECT "Count" AS [ENTERED_BY+OFFICER1+OFFICER2+OFFICER3]
FROM tblclientcontacts
GROUP BY "Count"
PIVOT tblclientcontacts.ENTERED_BY;
However I need this to show data that is for a certain date. I have a field named CONTACT_DATE which has the date in this format: xx/xx/xxxx.
I have been messing with the above and havent had any luck trying to get the crosstab query to only query say from months 10,11,12 or even for month 10.
Anyone have any pointers for me or a direction I can look at.
I keep getting not part of a aggregate function error, or syntax error in my attempts.
Thanks.
View 8 Replies
View Related
Mar 14, 2006
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');
View 2 Replies
View Related
Oct 18, 2007
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.
Any help with this would be greatly appreciated
View 3 Replies
View Related
Jun 7, 2005
Good morning all,
I currently have the following expression in a query, to go back 6 months from the current date.
Between DateAdd("m",-6,Date()) And Date()
Does anyone know of a way from the current date, go back to the end of the previous month ( 30th, or 31st) then go back 6 months?
hope that made sense.
Thanks
Kevin
View 2 Replies
View Related
Dec 12, 2005
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
Err_Handler:
MsgBox Err.Description
Exit Function
End Function
View 1 Replies
View Related
Mar 19, 2007
Hey
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
View 14 Replies
View Related
Oct 8, 2014
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.
View 4 Replies
View Related
Apr 28, 2006
I want to convert my dates to months so I added the statement below in the Field section of my query. It is not working. What am doing wrong?
Month:IIf([dateActivity]>=7/1/2004,IIf([dateActivity]>=8/1/2004,IIf([dateActivity]>=9/1/2004,IIf([dateActivity]>=10/1/2004,IIf([dateActivity]>=11/1/2004,IIf([dateActivity]>=12/1/2004,IIf([dateActivity]>=1/1/2005,IIf([dateActivity]>=2/1/2005,IIf([dateActivity]>=3/1/2005,IIf([dateActivity]>=4/1/2005,IIf([dateActivity]>=5/1/2005,IIf([dateActivity]<=6/30/2005,”June”,”May”),”April”),”March”),”February”),” January”),”December”),”November”),”October”),”Sept ember”),”August”),”July”))
Thank you.
View 5 Replies
View Related
May 4, 2014
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.
View 1 Replies
View Related
Aug 31, 2006
Hello to everybody.
I have a problem in calculating the difference between two dates and displaying the output into Years Months Weeks Days. My problem is that dividing the number of days by 30 and requesting an integer is not accurate enough. I have looked at Pat and Jons examples shown in these threads
http://www.access-programmers.co.uk/forums/showthread.php?t=67472
http://www.access-programmers.co.uk/forums/showthread.php?t=89051&highlight=calculating+number+months
which either method would suit me as the accurate answer is what I am after.
My problem is that not all of my records would have a start and end date. Therefore both examples would result in an error.
Can anyone please give a hint on how to perform the calculations on those fields which have dates recording the results but ignoring those fileds which do not have a start / end date.
I am having a bad day and I'm sure the answer is so easy that I am going to kick myself but having played around with the modules by limited knowledge is not enough.
Many thanks in advance
Ian
View 3 Replies
View Related
Jul 19, 2013
Find the difference between two dates is simple if you're simply looking for the number of days between them. However, if you're looking to express the difference in a bit more user-friendly way, you can use the following code to calculate the difference in years, months, and days. The code accounts for zero values, plurals and the Oxford comma. Further, it automatically sets the later of the two dates to the End date, so the dates can be used interchangeably.
Examples:
June 4, 2010, July 3, 2012 returns "2 Years and 29 Days"
June 4, 2010, July 5, 2011 returns "1 Year, 1 Month and 1 Day"
June 4, 2010, June 5, 2010 returns "1 Day"
Code:
Function DiffOfTwoDates(dtmDate1 As Date, dtmDate2 As Date) As String
' Written by Will Knapp, Freelance Access Developer, 2013
Dim dtmStart As Date, dtmEnd As Date
Dim strDiff As String ' Resulting String
Dim yDiff As Integer ' Year Difference
Dim mDiff As Integer ' Month Difference
Dim dDiff As Integer ' Day Difference
Dim CommaLoc As Integer
[code]....
View 3 Replies
View Related
Jun 19, 2014
I have a crosstab query to summarise the counted string values from another query: E.g.;
TRANSFORM Count(Table1.Viable) AS CountOfViable
SELECT QryTable01.productName
FROM QryTable01
GROUP BY QryTable01.productName
PIVOT Table1.Viable;
As there are some null values returned (ie blank cell in the pivot table)
I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;
So if I add a text box in the report with = [viable] + [Not viable]
t
Then the report row with the values:
Viable Not viable
14 12
displays as 1412
So how do I add the values in the rows???
View 1 Replies
View Related
Sep 17, 2014
Below is the SQL view of a Crosstab Query I am running. However I am lost at how to keep it from creating a serpate row for the same data on the column. For instance in the row data if I have an ATM batch come in for 1111 on 9/16 and then one come in for 1111 on 9/17 instead of keeping it on the same row it drops 9/17 down to the next row. Is there a way to keep the data on the same row?
Code:
PARAMETERS [forms]![frmReporting]![txtDatefrom] DateTime, [forms]![frmReporting]![txtDateTo] DateTime;
TRANSFORM Sum(Query1.CountOfATM) AS SumOfCountOfATM
SELECT Query1.ATM, Query1.Store, Query1.Type, Sum(Query1.CountOfATM) AS [Total Of CountOfATM]
FROM Query1
WHERE (((Query1.[Date Stamp])>=[forms]![frmReporting]![txtDateFrom] And (Query1.[Date Stamp])<=[forms]![frmReporting]![txtDateTo])) OR (((Query1.[Date Stamp]) Is Null))
GROUP BY Query1.ATM, Query1.Store, Query1.Type, Query1.[Date Stamp], Query1.[Date Stamp]
PIVOT Format([Date Stamp],"Short Date");
View 4 Replies
View Related
Apr 16, 2013
I have the table "tblProductionForecast" with the following fields:
ProductionForecastProductId
ProductionForecastMonth
ProductionForecastQuantity
I want to show this in a report, similar to an spreadsheet, with:
ProductionForecastProductId = Rows
ProductionForecastMonth = Columns
ProductionForecastQuantity = Data
I thought of using a crosstab query like this:
Code:
TRANSFORM avg(tblProductionForecast.[ProductionForecastQuantity]) AS AvgOfProductionForecastQuantity
SELECT tblProductionForecast.[ProductionForecastProductId]
FROM tblProductionForecast
GROUP BY tblProductionForecast.[ProductionForecastProductId]
PIVOT tblProductionForecast.[ProductionForecastMonth];
but I don't want the average of ProductionForecastQuantity. I want the actual value!
Is a crosstab query the wrong method to use?
If no, how do I show the actual value of ProductionForecastQuantity
If yes, how should I create the report?
View 5 Replies
View Related
Nov 12, 2013
I have a crosstab query that I would like to sum data across. Each row is a seperate experimental product, and each column is a test phase that each product goes through. The data is the number of days each product was in each test phase. What I need is the sum total of days that all products were in all phases. Think of this table as a single item, and each row is a slightly different version of the item to test. So what I need is essentially how many days this item has been in testing across all variations.
View 1 Replies
View Related
Jun 13, 2006
I have a Qry1 that contains a criteria of:
Between [Start Date] And [End Date]
I eventually use a crosstab Qry2. The wizard does just fine.
but when i run the crosstab, i get an error message:
"the microsoft Jet engine does not recognize "start date" as a valid field name or expression."
Is there anything i can do?
Penwood
View 2 Replies
View Related
Aug 12, 2014
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
View 9 Replies
View Related
