Crosstab 12 Months Doubling Up On A Month
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 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
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
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
Dec 12, 2007
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
View 10 Replies
View Related
Nov 15, 2013
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.
View 8 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
Jul 7, 2014
I have a crosstab where I want year and month in the header so I have formatted a variable MTHYR to be of the form mm/yyyy.
This works well as Access only wants one variable in the column header. My problem is that it is not sorted nicely and with my data being between Aug 2013 and Jun 2014 the headers look like this.
1/2014,10/2013,11/2013,12/2013,2/2014, not a very nice order.
Is there a way I can reformat MTHYR or a completely different solution?
My current code is
Code:
TRANSFORM Count(VOR_CALD_REGION.[Case Number]) AS [CountOfCase Number]
SELECT VOR_CALD_REGION.[Dealer Name], Count(VOR_CALD_REGION.Case Number]) AS [Total Of Case Number]
FROM VOR_CALD_REGION
GROUP BY VOR_CALD_REGION.[Dealer Name]
PIVOT VOR_CALD_REGION.MTHYR;
View 3 Replies
View Related
Jul 1, 2014
This is my expression to format crosstab query by month
Expr1: Format([Recharge_Date];Month([Recharge_Date]))
but i can't make it with month and year....
View 2 Replies
View Related
Nov 27, 2007
In My Db I have TblDatabase (contains company details) and TblContacts (contact details relating to the company joined on a one-many relationship.
I also have Tbl calls which records contact history with each company.
I made a query to output a report based on the source of the Company. What i am finding is that companies which have more than one contact, the contact history repeats itself by the number of contacts in the company, can anybody point me in the right direction. Here is my query:
SELECT DISTINCT TblDatabase.CompanyName, TblDatabase.RegisteredDate, TblDatabase.City, TblDatabase.WebSiteAddress, TblDatabase.BusinessDescription, TblCalls.[Call Note], TblDatabaseContacts.[First Name], TblDatabaseContacts.[Email Address], TblCalls.Result
FROM (TblDatabase INNER JOIN TblCalls ON TblDatabase.CompanyCode = TblCalls.CompanyCode) INNER JOIN TblDatabaseContacts ON TblDatabase.CompanyCode = TblDatabaseContacts.CompanyCode
WHERE (((TblDatabase.Source)=[forms].[FrmSourceReport].[ComboSource]));
View 2 Replies
View Related
Apr 7, 2013
I am creating a crosstab query in VBA to report on claims on a paid and incurred basis. I would like the query to have 13 columns - one for each month of the current year and one for all claims paid prior to January of the current year.
Is there a way to lump all data with a date less than Jan 1 into a single field while retaining the monthly detail for the current year?
All of the data is coming from a single table. Sample code below functions, but provides a column for every month a claim was incurred.
Sub Triangle()
'Triangle Reports
'Check Registers
On Error GoTo Error_Handler:
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim QRY As DAO.QueryDef
[Code] ....
View 4 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 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
May 5, 2014
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.
View 2 Replies
View Related
Mar 19, 2012
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.
View 10 Replies
View Related
Oct 18, 2005
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
View 11 Replies
View Related
Aug 9, 2013
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 Related
Oct 13, 2006
HiI have a field that is text which holds the month in form "oct" etc. I need to pass the numerical value of that month to a function so how do I create an expression that will change it (in this instance) to 09.kind regards in advance.peter
View 5 Replies
View Related
Oct 29, 2007
Hello mates, :cool:
I'm a bit confused with date codes. What I'm try to complish here is that how to add years and months from separate fields to text box, where's difference between two dates as an years and months? So basicly first I read year and month values from text boxes to a integer and then I'm going to add them to somewhere at last, but I'll get like 1 year and 14 months for result so, could you hit me with some tips or tricks what I should try out.
I'll approciate your time to help me out with this and thanks for advance. =P
View 1 Replies
View Related
Sep 14, 2006
Hi,
I am trying to create a query that will show data in a rolling four month period. Example: For September, show June through September; for October, show July through October.
I am using a general date format in the field. I can use >DATE() - 120 to get a 120 day period, but that obviously does not give me data for the actual Calendar dates. Is there a better way?
Any and all suggestions will be greatly appreciated.
Thanks in advance,
Jake
View 2 Replies
View Related
Sep 15, 2006
Can this be done?
I have a single $ amount and an effective date. I need to take that dollar amount and disperse it out evenly over 12 months. Is it possible to create a query that will create the fields 1/1/2006, 2/1/2006 and enter those amounts?
For example, total savings is $12k and effective date is 2/1/2006. I need to show $1k in each month field in my query from 2/1/2006 through the month of 1/1/2007. I've tried DateSerial and some other methods but I can only get it to give me the divided amount in the effective date column and nothing else. Is DateSerial the way to go?
Thanks,
Toni
View 2 Replies
View Related
Nov 10, 2006
Hello,
I'm trying to calculate months not days between
EX: 1/1/06 to 6/30/06
I tried to use end date (-) begin date, but is only give me the difference in days. What expression do I use to get 6 months?
:confused:
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
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
Mar 27, 2005
Searched and can't find the specific answer to my age/month question so I hope you will bear with me.
on my form I need to calculate someones age and show it in years & months, then display the age (years & months) in the format 7.5 ( i.e. 7 years 6 months displays as 7.5 ).
I also need to round that figure down to the nearest 0.5 of a year so 7.4 would display as 7 and 7.9 would display as 7.5.
Any help would be very gratefully received.
Cheers
RussG
View 2 Replies
View Related
May 23, 2013
1) I am trying to use the same set of data for different months. I will try to explain...For each month I want to mark the data as "Statement Received" and "Statement Reconciled". I don't want to create a brand new database every month with the same data. The database will become massive too quickly. Is there any way that I can set the date by using the filter date combo box I have created on the frm_SearchMulti form and having a fresh set of data for just April. When May comes around I can create a new date and have a fresh set of data, but still reference April data... I hope I am explaining this okay
2) Would it be possible to add a column into the "SearchResults" ListBox for Statement Received and Statement reconciled? I would want to update it through the text boxes I made directly above it. The buttons right now do not do anything... they are just there for formatting purposes. I want want to be able to select multiple record in the listbox and update them all simultaneously
3) How can I make the "New Account" button actually pull up a new account?
View 1 Replies
View Related