Month / Year Queries
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 Replies
ADVERTISEMENT
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
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
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 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
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
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 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
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
Aug 16, 2005
hi friends,
I want to get year and month field of datetime field so that i can get data by comparing them with other table fields.
ex: date field column contains 1/28/2005 as data
how to get year(1/28/2005)
how to get month(1/28/2005)
expecting your help
Thanks,
Spec
View 3 Replies
View Related
Aug 20, 2007
I have a table a
year month day
2005 1 1
2005 3 1
2006 5 1
I want to generate the associateed
1/1/2005
3/1/2005
5/1/2005
which funciton I can use to do that?
Jeff
View 3 Replies
View Related
Sep 5, 2005
I have a reports form where the user can enter enter a from date and to date for the report criteria. I have an unbound field on the report which i want to display the month and maybe the year in which this date criteria is in. This will be needed to display on the actual form. Say if the date range is between 01/01/05 and 31/01/05. I need the field to say January 05.
Hope this makes sense and can be done
View 6 Replies
View Related
Mar 13, 2006
I have a field that is:
Current length of continuous allocation of Care Co-ordinator. ( years and months )
Ideally I would like to have two drop down menus - one with years and one with months.
Would I have to have two fields in my table - one for years and one for months or can I have one?
Whats the best way?
Thanks.
View 3 Replies
View Related
Oct 20, 2006
On my form day/month/year has switched to year/month/day. On a table there are three separate date cells. Order Date, Shipping Date, and Report Date. They are each formated to medium style. At first I also used an input mask then I found that Access would convert to the format I choose. I had missed one of the input masks during the removal and when I removed the last input mask Access now converts to yy/mmm/dd. What just happened and how do I reverse this? It is more convienant for some of the users to not have the input mask but the order has to be uniform.
View 3 Replies
View Related
