Queries :: Matching Dates By Month And Year
Aug 27, 2013Looking 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
ADVERTISEMENT
Queries :: Matching Year Field In 1 Query
Feb 5, 2015Using Access 07
This is my query
SELECT QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation, Sum(QryReserveTotals.SumOfAddtlAllocationAmt) AS SumOfSumOfAddtlAllocationAmt
FROM QrySiteAlloc LEFT JOIN QryReserveTotals ON QrySiteAlloc.Site = QryReserveTotals.Site
GROUP BY QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation;
In the QryReserveTotals is a datereceived field.
I need the year of the date received field to match the QrySiteAlloc.AllocYr that will be selected by a parameter..
what would be the criteria to achieve this?
Month / Year Queries
Apr 5, 2006I 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:
Queries :: Total By Each Month Of The Year
Feb 6, 2014I 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;
Queries :: Group By Month And Year?
May 9, 2013I 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.
Queries :: Sorting Month / Year Within A Crosstab?
Jul 7, 2014I 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;
Queries :: Sort By Year Is Sorting By Month
Sep 16, 2013I'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?
Queries :: Calculates Number Of Days / Month / Year
Sep 15, 2013I 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.
Queries :: Calculate Fiscal Year And Month From A Date
Jul 13, 2015I 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.
Queries :: How To Format Crosstab Query By Month And Year
Jul 1, 2014This 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....
Queries :: Query Using Date From Form In Month And Year?
Apr 3, 2013My 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
Queries :: Convert 3-char Month And Year To Date
Aug 17, 2015I 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?
Queries :: Set Month Or Year Default As Per System Date
Oct 16, 2013How 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 RelatedQueries :: Calculate Profit Made By Type / Month And Year
Apr 22, 2013I want to make a query to figure out profit made by month year and room type.
View 1 Replies View RelatedMatching Current Month With Month In A Table
Oct 18, 2005I 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
Queries :: Filter Query By Year Of Dates?
Jan 23, 2014I'm trying to filter an append query by a year selected in a combo box [cboYear] for a field [ProgramDate]. The AfterUpdate on the cbo filters the append query based off of the selection (or selections-I'm using multiple combo boxes on the form). I then run a report based off of the appended table for a report.
I set my criteria for [ProgramDate] the query to be:
Code:
Year([ProgramDate])=[Forms]![frm_rpt_Programs]![cboYear]
The above code isn't working, even when I just try to run the query while the form is open. It's still returning all records.
Queries :: Write A Query To Pull Out Dates With Current Year Only?
Jul 18, 2013all using access 2010. I have a date field. I need to write a query to pull out dates with current year only. ex data: 1/1/10, 1/1/11, 2/1/12, 2/1/13. Need to pull all dates with current year which would be 2/1/13. Tried in query criteria of the date field: =Format(Now(),"yyyy") I received data mismatch. this is a date/time field.
View 4 Replies View RelatedHELP: Changing Dates To FY (fiscal Year) And YTD (year-to-date) Values
Apr 25, 2006I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.
For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006
THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.
What do you suggest? Many thanks.
Mehran
Queries :: Displaying All Dates Within A Month
Oct 14, 2014I have created a query to limit the list in a combo box to display the months and years of the date field in a table. Below is the code that I am using. This works fine except this displays the years within the months - for example:
January 2012
January 2013
January 2014
February 2012
March 2012
March 2014
April 2012
May 2012
May 2014
I would much prefer it to sort by month and year:
January 2012
February 2012
March 2012
April 2012
May 2012
January 2013
January 2014
March 2014
May 2014
Code:
SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName
FROM MainTBL
ORDER BY Month([QTDate]);
How to change this code to make it sort on Month and Year, not just month?
Queries :: Eliminate Records With Dates In Same Month
Jun 17, 2015I am trying to figure out a logical WHERE condition. I have a Query that gives me data,
Code:
Name | Start | End
--------+-----------------+-------------
Paul | 30-May-15 |
Eugin | 21-May-15 | 28-May-15
Francis | 04-Mar-15 | 08-May-15
Samuel | 10-May-15 | 13-May-15
I want to have only Paul and Francis. As Eugin start is 21-05-2015 and End is the same month, so is Samuel's. So I want to eliminate those two records.I have checked pbaldy's web of overlapping records. But unlike that, I need something customised.
Begins and ends before range - we don't want - Yes
Begins before, ends during - we want this one - Yes
Begins and ends during - we want this one too - NO
Begins during and ends after - we also want this one - Yes
Begins and ends after - we don't want this one - Yes
Begins before and ends after - we want this one -NO
Queries :: Expression To Show Years And Month Difference Between 2 Dates?
May 6, 2014 I am wanting to get an expression that will return the difference in years and months between 2 dates. Specifically, I want to know peoples ages in years and months based on a person's D.O.B and todays date. I have managed to do it in years:
Expr1: DateDiff("yyyy",[TBL_EmployeeDetails]![D_O_B],Date())
and in months but I would like to know how to return the difference in years and months.
Queries :: Dates In A Query - Show Orders By Month For Last 12 Months
Apr 22, 2013I 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.
Queries :: List Of Dates And Records With No Matching Record OR Existing Record With Higher Date
Nov 24, 2014I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
Matching Dates
Feb 23, 2007Hey all
I have a function to display the opening hours for the next 7 days for a client. I have also included the functionality whereby you can create a "special day" with non - standard opening times which are stored in the database.
I have a simple SQL to check to see if the current day is a special day:
Code:SELECT * FROM SpecialDays WHERE SpecialDate = #" & currentDate & "#"
I have run into this problem tho. There is a special date in the database for 03/01/2007 (UK date format 3rd January) but this is being retrieved for the 1st of March (01/03/2007)
therefore this query:
Code:SELECT * FROM SpecialDays WHERE SpecialDate = #01/03/2007#
retrieves the record with the date 03/01/2007
Any ideas?
Thanks
Date - Month And Year Only
Dec 18, 2005can 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..
Month End And Year End Reports
May 1, 2006For 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!