Display 12 Months On A Crosstab Query?
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 Replies
ADVERTISEMENT
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
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 7 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
Dec 19, 2014
I have a crosstab query. There is a field for row headings that specifies QUARTER (Q1,Q2, Q3,Q4) and another field for row headings that specifies YEAR(2015,2016,...etc)
I want to be able to have the user input the criteria in to a box and filter the crosstab query to then display the filtered results.
Right now on my column headings I have MONTH.
When I try to reference the form in the criteria, I always get the error
"The Microsoft Database Engine does not recognize ..... as a valid field name or expression"
Does this mean that you can not filter crosstab queries?
View 5 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
Sep 6, 2005
If I key in to the control source of a text box "=Date()-184" I will get the date 6 months ago but if I tried the same of 06/10/05 I will lose a day because of the 30/31 day months. Does anybody know another way around this?
View 6 Replies
View Related
Dec 12, 2013
I have to make program for a school
Some students pays fee for one month, some for two months and some 3 months.
School administration requires me to print notices for every student according their payment mode after comparing with next due months and already paid months.
Some notices will show their fee for the months as
(Id= 1 Jan-14, Feb-14),
(Id=2 Jan-14, Feb-14, Mar-14),
(Id =3 Dec-13, Jan-14) Considering as Id 3 has not paid due month i.e. Dec-13 Fee and his payment mode is 1 month only.
Here Id means Id number alloted to Students.
View 1 Replies
View Related
Sep 21, 2007
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit
Please help!!!
View 2 Replies
View Related
Feb 27, 2007
How do I write an expression to pull data greater than six months from a user entered date.
View 3 Replies
View Related
Nov 29, 2005
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 12 Replies
View Related
Jun 1, 2005
Hey Guys,
I've been racking my brains on this one and need your help. I was asked this question by a friend who has just set up a database for a non-profit organisation in the UK. I have attached an simple example database to illustrate the problem.
There is a table called "tblMembers" which contains data on members of clubs and the dates their membership expires.
There is a query called "qryExpiryCrossCheck" and it needs to display people whose membership has expired in the previous month (i.e. May 2005).
Sounds easy? Well the query needs to know that 'now' is June 2005 and that the previous month is May 2005 and then as I said display people whose membership has expired in the previous month.
Any ideas/solutions would be greatly appreciated. As I said this is a favour for a friend who is carrying out some free work for a non-profit organisation.
Cheers,
Rusty
:D
View 3 Replies
View Related
May 30, 2006
I have a field called DATE_END_DEERS which is in a format of YYYYMMDD (20060530). I need to run a query that shows 6 months subtracted from this date. I can never get an answer that is even close. Can someone please help? ACCESS2000.
View 3 Replies
View Related
Dec 12, 2006
I have a little problem
I have an access database containing the following
LibraryYearMonthVisits
Lenzie1999Dec1095
Lenzie1999Feb789
Lenzie1999Mar1293
Lenzie1999Apr1526
Lenzie1999May1231
Lenzie1999Jun1171
Lenzie1999Jul1206
Lenzie1999Aug1287
Lenzie1999Sep1206
Lenzie1999Jan1451
Lenzie1999Nov1706
Lenzie1999Oct1414
Lenzie2000Oct956
Lenzie2000Jan1113
Lenzie2000Feb858
Lenzie2000Mar1110
Lenzie2000Apr1198
Lenzie2000May1203
Lenzie2000Jun1105
Lenzie2000Jul1152
Lenzie2000Aug916
Lenzie2000Sep985
As you can see the months are in order alphabetically. How can I fix my query so the months can be in the right order. Jan,Feb,Mar etc?
I imagine it is a simple procedure but I am not sure how. PLEASE HELP :confused:
Derek
View 3 Replies
View Related
May 22, 2007
I have a query that I need to always pull data from the newest past 3 months, so if this query were ran today it would take from months 2,3,4 (Feb, Mar, Apr). Any clue on how to do this?
View 14 Replies
View Related
Apr 10, 2008
Hello Everyone,I have a query where I am trying to pull records where the user took a class that is 2.5 years old or newer based on their license expiration date. Can someone tell me what I'm missing/doing wrong?SELECT tblCE_Details.LIC_NUMBER, tblCE_Details.Course_Code, tblCE_Details.Course_Date, sociwork1.expirat_dtFROM Sociwork1 INNER JOIN tblCE_Details ON Sociwork1.LIC_NUMBER = tblCE_Details.LIC_NUMBERWHERE (tblCE_Details.LIC_NUMBER = Forms![SW form]!SLIC_NUMBER) AND tblCE_Details.Course_Date >= (sociwork1.expirat_dt - Month(30))ORDER BY tblCE_Details.Course_Date;Thanks,Crhodus
View 3 Replies
View Related
Nov 30, 2005
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 2 Replies
View Related
Nov 15, 2007
I have a database that i am trying to clear out old customers that have't bought something in the last 6 months.
I have a table with the customer details in and another table with the purchases in.
I have been using the folowing criteria:
Not Between Date() And #01/05/2007#
But i think this still still shows customers who have actually bought more recent than 01/05/2007.
Is there a way to just show customers who have not purchased anything within the last 6 months of the date the query is run on and to only show their last transaction date.
View 4 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
May 11, 2012
I have a query to calculate the monthly depreciation for a machine for specific period of time.
My problem is how to get the number of months for each period assuming i want to start from month September 2009 till may 2012.
Attached the data extracted by the query.
I want to calculate the depreciation to be grouped year by year in report.
[TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
View 3 Replies
View Related
Sep 3, 2005
Hi, i have a query which looks at a table named "attendance" the query includes the fields: "employee number", "absent code" (a 3 letter code), "absent reason" (list of different reasons) "shiftdate" (weekly shift dates in format: DD/MM/YYYY). If possible i would like to be able to promt the user to enter a employee number, then a absent reason (ideally from a list box) and show all occurences (with all the shiftdates) only of that particular absent reason within the last 12 months, and ideally calculates in another field the number of times that employee has been absent for that particular reason for the last 12 months.
Does this make sence?, i know how to promt the user to enter a employee number but not sure how to go about the rest, any help with what seems to be quite a challenging task to me would be excellent.
View 4 Replies
View Related
Jul 13, 2007
Can someone help?
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.
Any ideas?
View 4 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
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
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
View 1 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
