Queries :: Sorting Month / Year Within A Crosstab?
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 Replies
ADVERTISEMENT
Sep 16, 2013
I've created a query based on 2 other queries.
I then filter the results of the third query based on 2 dates.
This worked great for 2013.
As a test I started making some records for 2014 and I've now found that my third query is not filtering the dates properly.
The filtering is happening based on date fields that contain only month and year eg: "09-2013".
My problem is that when I try to filter for records in 2014 it brings up results for 2013 as well.
If for example I had a record in August 2013, September 2013 and August 2014.
The sort would be:
08-2013
08-2014
09-2013
NOT:
08-2013
09-2013
08-2014
How do I make the sort apply to month then year to get the correct results returned?
View 6 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
Apr 5, 2006
I always have a problem getting this to work without a lot of effort and I know it should be easier than I make it out to be. Problem is say I have a bunch of records I need to summarize by month. Say Dec 05, Jan 06, Feb 06, etc. When I bring down the date col, I do something like:
Format([date],"mmm - yy")
And do a totals query with a second col that returns say a count for each period. This works fine except that when I sort I get Dec 05, Feb 06, Jan 06, etc.
How can I do this with out having to create a third col to do the sorting stuff? Should I somehow wait until the data gets to the report to do the date format stuff???
:confused: :confused:
View 1 Replies
View Related
Feb 6, 2014
I have a query that I need to total by each month of the year. Field names and types are as follows
ID (PK)
Month (Date/Time)
PoliceResponse (1 or 0)
TerminalCheckTrue (1 or 0)
RampCheckTrue (1 or 0)
AOACheckTrue (1 or 0)
TotalTerminalRampAOA (TerminalCheckTrue + RampCheckTrue + AOACheckTrue)
TotalChecks (PoliceResponse+TerminalCheckTrue + RampCheckTrue + AOACheckTrue)
Now, I need to just get the total of each of these fields per month for the entire year. So it would look like
Jan, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Feb, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Mar, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Below is a sample from the query. I guess Im just not sure where to start since there are multiple months the same value, but i need them to total up..
Code:
SELECT DISTINCTROW tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy') AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks
FROM tblBlotter
GROUP BY tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy'), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;
View 14 Replies
View Related
May 9, 2013
I try this code
Code:
SELECT Format(ReportTbl.lot_date,"mm-yyyy") AS Lot,
Round(Avg(ReportTbl.avg_dat),2) AS DataAvg,
Round(Avg(ReportTbl.avg_len),2) AS LenAvg,
Round(Avg(ReportTbl.avg_in),2) AS InAvg,
Round(Avg(ReportTbl.avg_out),2) AS OutAvg,
Round(Avg(ReportTbl.avg_thi),2) AS ThiAvg,
Round(Avg(ReportTbl.avg_moi),2) AS MoiAvg
FROM ReportTbl
GROUP BY Format(ReportTbl.lot_date,"mm-yyyy")
ORDER BY ReportTbl.lot_date;
But it not working and have warning box with "you tried to execute a query that does not include the specified expression 'ReportTbl.lot_date' as part of an aggregate function"
and when I try
GROUP BY Month(ReportTbl.lot_date),Year(ReportTbl.lot_date) or
GROUP BY datepart("m",ReportTbl.lot_date),datepart("yyyy",R eportTbl.lot_date)
or other code that I can search in google
I get the warning message that resemble with above message
and when I try to GROUP BY ReportTbl.lot_date it work but the result does not meet the requirements.
View 3 Replies
View Related
Aug 27, 2013
Looking to match to date fields within a query by using the month and year only, is it possible and how.
I'm using Access 2003
View 13 Replies
View Related
Sep 15, 2013
I am building a report and need to know the coding in the query (or in the report) to achieve the following.
Currently, in my query - 3 fields that work fine:
AppDate - dates of set appointments (Appt)
MonthNo: DatePart("m",[App_Date]) - get Month value
YearNo: DatePart("yyyy",[App_Date]) - get Year value
What I need is : EXACT coding to get these counts to show in my report:
January 2013
12 appointment (there were a count of 12 appts in January)
over 23 days (there were 23 days where appts were booked in January)
of 31 days (total number of days in the month of January)
February 2013
18 appts (there were a count of 12 appts in February )
over 19 days (there were 23 days where appts were booked in January)
of 28 days (total number of days in the month of February )
etc.
View 3 Replies
View Related
Jul 13, 2015
I want to build a query that calculates the fiscal year and the month from a date on-the-fly. I tried to do it in VBA, but it's more complicated than in query I guess. So this is my table:
The date is on the left, in the middle I want to have calculated Jan 14 and on the right I want 14/15.
In VBA I started with this: strMonth = MonthName(Month(A), True) & " " & Format(A, "YY") which gave me "Jan 08". But the date was hard-coded and not from my table. Then I tried to store the information by SQL statement into a string. But this also didn't work, the types were different.
Code:
UPDATE tblSAPOD
SET sapOD_month = Year([sapOD_OrderDate])-IIf([sapOD_OrderDate]<
DateSerial(Year([sapOD_OrderDate]),6,16),1,0)
WHERE sapOD_OrderDate Like "*/*/94";
.. but I guess it's wrong, it gives me a syntax error.
View 11 Replies
View Related
Apr 3, 2013
My question is that I wish to have a query that looks up on a name in a form (No problem got this bit) and also the Date field from the same form so that from the date :-
example 24/03/2013 only cares about the Month and Year so would look up only March in 2013
The end result would be to supply me with a number (using count) of how many times this persons name has occurred within the calendar month and year of the date supplied in the form
View 1 Replies
View Related
Aug 17, 2015
I have 2 columns that are listed as such:
AssumptionMo AssumptionYr
MAY 2014
JUN 2015
JUL 2015
OCT 2016
I need to create a field called AssumpDate that converts the month into a date field on the 1st day of the month. ex May 2014 needs to read 5/1/2014. When I use the expression AssumpDate: DateValue("1-" & [Assumption_Month] & "-" & Year(Date())) of course the year changes to the current one--2015. How can I I change the expression so that the year is based on the AssumptionYr column?
View 3 Replies
View Related
Oct 16, 2013
How can I set Month or Year default as per the system date. ie Only select records by Month (October) or Year (2013) as per system date?
View 3 Replies
View Related
Apr 22, 2013
I want to make a query to figure out profit made by month year and room type.
View 1 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
Jul 12, 2005
I have a crosstab query where my column headings are the names of the months based on invoice dates.
what i currently have:
Month: MonthName(Month([AnnualIncome]![InvoiceDate]))
so this spits out April May and June in the column headings.
The problem is that it is sorting it alphabetically, not by the numerical value - so my column headings are coming out as april, june, then may. (I assume once I have july that will come in between april and june.)
Any help would be REALLY appreciated.
Thanks in advance.
View 4 Replies
View Related
Jan 25, 2005
:confused: :confused: :confused:
Please bear with me. I'm new to using access and plodding along little by little and I had little luck finding relavent past posts. I have a table with the column headings and a couple of records listed below. Each of those records has a subdatasheet pertaining to data for the commands. For every command, my dept does a survey or inspection every so many years. How often we do these surveys depends on the HAZ CAT. If the command has a HAZ CAT of I, they have a survey done every year. (HAZ CAT= II, every 2 years; HAZ CAT = III, every 4 years). On a form, I want to be able to generate the next serveral survey years (let's say for the next 10 years) based on the command's most recent survey year and its HAZ CAT. So for each command, there should be a listing of all its survey years. Then I want to be able to sort by year so I'll know what commands will need to be surveyed each year. I'll eventually have a form which will be my control panel. On the control panel will be command buttons labeled with years for the next 10 years. The button should open up a form with the records for the respective command information.
[CommandID] [Command] [HAZ CAT] [Last Recent Survey]
1 AIMD I 2004
16 DODDS III 2002
View 2 Replies
View Related
Mar 1, 2006
I have looked and tried a lot of things that I have seen in this forum, but I am not able to make this work. I have a query that I am trying to sort a table field, "Date" by month. The field has a "General" time/date format in the table. This is the last code I tried, but as you can see I am not much with VB. Please see if you can help me. The table name is "Batch Data" and the field name is "Date".
Thanks,
Robert
SELECT Format([Batch Data].[Date],'General Date') AS formDate
FROM [Batch Data]
ORDER BY [Date];
View 5 Replies
View Related
Nov 1, 2005
I have my query set up, but I want it to sort by chronolological month and not alphabetically. Below is the SQL statement.
GROUP BY [Implementation Pipeline].[Parent Company], PUR_OWN_ASC_11.CUST_ID, MonthName(PUR_OWN_ASC_11!PERIOD,False), [Implementation Pipeline].[Transition to FSCR/AM]
HAVING (((MonthName([PUR_OWN_ASC_11]![PERIOD],False))<"October"))
ORDER BY MonthName([PUR_OWN_ASC_11]![PERIOD],False) DESC;
View 2 Replies
View Related
Dec 18, 2005
can i change date format that contain day, month, and year to month and year only..
i try change at fromat at porperties, but it change back into dd/mm/yy at combo box..
this is bcoz i want to filter up my subform that contain parts that purchased by customer by month..
thanks..
View 3 Replies
View Related
May 1, 2006
For the record, I have indeed searched the forum for help with this, but no luck so far, so here goes. (C++ programmer, little background in access).
I have a table that stores a person's name, gross amount spent, gst spent, pst spent, and total amount spent.
I need to be able to create a query or something that asks me to enter a year, and it will sum up the amounts, gst, and pst over the course of that year, and print it neatly on screen.
Also, I need to be able to create a similar query that asks for a year and a month (does access possess Java's ability to easily create a pop-up calendar applet-like instance that provides a GUI for selecting months/years, or does the data need to be entered through a command prompt?) which will create a month-end report in the same manner, but only spans a 1 month period?
Any information that could be provided to me, even a means of getting started on this problem, would be great. Thank you and cheers everyone!
View 2 Replies
View Related
Aug 28, 2006
if i have a table like this:
4/3/2006
4/3/2007
4/12/2006
4/12/2008
5/2/2003
5/5/2003
i would like to extract only the distinct month and year date, so my query should return
4/12/2006
4/3/2007
4/12/2008
5/5/2003
can anyone plz help me with the sql query? thanks
View 2 Replies
View Related
Jul 22, 2007
I have another...
How do I group the dates on my query to show only a certain month and year?
View 2 Replies
View Related
Aug 14, 2007
Hey all, I have a form that has two textboxes, one box is for month and the other is for the year. After I enter the month and year I want, I click on a command button that previews a report. Well, the report is not giving me the information for that particular month and year, actually it gives me nothing.
The report gets its information from a query. In that queries criteria field is the below expression that I have.
Like [forms]![frmPEL]![cboMonth] & "/*/" & Right([forms]![frmPEL]![txtYear],2)
Can anyone see anything wrong with this? I should be able to enter the month and year and click on the report button I want and preview that report, but no joy.
Your help is greatly appreciated.
RR
View 5 Replies
View Related
Jul 30, 2012
I have tried to do this using DateAdd but I cant get it to add both the month and year.
I have a date field [HireDate] and I need to add one year then get then get the first day of the next month.
For example: HireDate = 1/15/2012 - I need access to return 2/1/2013.
how to build that expression?
View 4 Replies
View Related
Feb 19, 2007
Hi all.
I have chart in ADP project/MS SQL Express and I want to see in chart table (date Field, data field) grouped by year and month: 2005.01, 2005.02, 2005.03....
I try this str = DATENAME(yyyy,date)+ DATENAME(m,date), SUM(data) FROM table GROUP BY DATENAME(yyyy,date)+ DATENAME(m,date) ORDER BY DATENAME(yyyy,date)+ DATENAME(m,date)
problem is with sorting: 2005April, 2005februar,2005,Januar - its sorting not by date, but alpabhetic.
Please, help with other grouping way!
View 2 Replies
View Related
Apr 19, 2007
Hey guys.... stucked in one thing..... I need to extract Month and Year together from a date in a Query. for example if date is 4/19/2007 i want to put this as April 2007.
Any idea ...?
thanks
View 5 Replies
View Related
