Grouping Date By Year And Month In ADP+SQL

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 Replies


ADVERTISEMENT

Grouping By Month & Year

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

Grouping By Month From Date Field

Sep 16, 2004

I have a Date field, (an Expiry Date) and I want to use this in a Report that with Group the Report into Months. So we know, such and such a month, these are set to expire. Rather than have a separate Month column... how can I make use of exisiting Date in expiry field. Some one must know... Thanks in advance.

View 1 Replies View Related

Grouping Results Based On Date By Month

Oct 20, 2005

I need a subgrouping for a report I am working on.

I have a DateIn field that contains date as mm/dd/yy. I have managed to get a count of all of a certain type of document that came in during a certain period of time that usally spans a quarterly period. So typically there are three months in the query. I would like a subgroup that breaks the numbers up in the 3 month period by each month.

So if a total of 150 docs moved in 3 months it shows 50 moved the first month, 50 the second, etc. I believe this is grouping by month. I have not been able to determine the criteria for getting the query to group by a single month as well as the three months period being grouped by document type. Here is what I have so far.

SELECT ctblDocuments.DocTypeID, tlkpDocTypeList.DocType, Count(ctblDocuments.DateIn) AS CountOfDateIn
FROM ctblDocuments INNER JOIN tlkpDocTypeList ON ctblDocuments.DocTypeID = tlkpDocTypeList.DocTypeID
WHERE (((ctblDocuments.DateIn) Between #7/1/2005# And #9/30/2005#))
GROUP BY ctblDocuments.DocTypeID, tlkpDocTypeList.DocType
ORDER BY tlkpDocTypeList.DocType;

View 5 Replies View Related

Date - Month And Year Only

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

Year To Date Totals, Month To Date, Week To Date

Oct 9, 2005

Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.

I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.

View 9 Replies View Related

How To Extract Month & Year Together From A Date

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

How To Get Year And Month Value From Date Field

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

How To Format Date From Year Month And Day?

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

Format Date To Show Month & Year Only

Oct 22, 2006

I have a need to enter dates so that they always appear as the first of the month. These dates are used in vlookup functions in Excel and need to be constantly recorded as eg 1 Nov 06. Currently I have to rely on notes and training to make sure users only insert the date in this way.

What I would like to do is get them to enter Nov 06 only (without a day) and have that stored as 1 nov 06. I have searched for date formatting functions and nothing addresses this. Perhaps it is something that could be achieved using VB if it is possible to do it.

Thanks for the help.

View 4 Replies View Related

Modules & VBA :: Getting A String With Month / Year From A Date

Apr 7, 2015

I need to use VBA to get a string which is essentially just a month and year (so date, not including day)

Would Prefer it to be 04/15 as opposed to April-15, as the number is easier to sort than the word (how to sort month names in numerical order as opposed to alphabetical id).

So that I assume i could do by =format(now(), mm/yy) (though havent actually tested it)

However to add to the complexity id also need to add a month to it, so for instance todays date is 08/04/15, id need the string to read "05/15"

or if the date was 10/12/15 id need the string to read "01/16"

View 3 Replies View Related

Date Query (last Week, Last 2 Weeks, Last Month, Last Year, All)

May 31, 2007

Hi

Would be great if I could get some help on this:

I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:

It somhow need to be check what the date is today and then caclulate back.

Any help on this would be much appreciated.:D

Thanks
Daniel

View 6 Replies View Related

Queries :: Calculate Fiscal Year And Month From A Date

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

Queries :: Query Using Date From Form In Month And Year?

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

Queries :: Convert 3-char Month And Year To Date

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

Queries :: Set Month Or Year Default As Per System Date

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

Reports :: Date Fields (Month And Year) Of Reporting Period

Mar 5, 2013

I need to put a date field in that is the month and year of the reporting period...

for example

February 2013

and then another date field that is the 10th day of the month following the reporting period.

example: March 10, 2013

I have tried a number of different formats and can't get it right.

View 4 Replies View Related

Creating Combobox With Unique Month And Year Entries From A Date Field In A Table

Jul 18, 2005

Hi,

Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?

I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...

Any help would be much appreciated!

View 2 Replies View Related

Grouping By Year Then By Months

Dec 3, 2007

I have been assigned a task to create a query that gets record from Access database by grouping it by year and then by months.


DATA ILLUSTRATION

date incidents
01/01/2007 2
01/02/2007 3
01/03/2007 4
01/01/2008 1
20/01/2008 1
01/02/2008 1

Based on the above data illustration, what I am trying to do is to get total by year and then break it down by the months

year 2007 total_incident: 9
Jan total_incident: 2
feb total_incident:3
mar total_incident: 4

year 2008 total_incident:3
jan total_incident:2
Feb total_incident: 1


I am using Microsoft Access, but trying to get the result into a web form. Many thanks in advance

View 2 Replies View Related

Grouping Records By Month Query

Oct 27, 2005

This is probably an easy question for you all:

I want to run a query that does a GroupBy with DATE and Counts the number of records associated with that DATE.

I can do this, but I also want the dates to group together by Month so I can count the number of records per month for multiple months in the same query. How do I do this?

Thanks very much

View 1 Replies View Related

HELP: Changing Dates To FY (fiscal Year) And YTD (year-to-date) Values

Apr 25, 2006

I 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

View 7 Replies View Related

Sum Of Current Year Minus The Year Of A Date In Past?

Apr 25, 2014

I'm trying to add a couple of fields to the Contact database in Access 2010.

In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995

I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).

I have been trying to tweak this:

SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))

but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"

View 2 Replies View Related

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 1 Replies View Related

Month End And Year End Reports

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

Distinct Month AND Year

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

Return Month And Year

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







Copyrights 2005-15 www.BigResource.com, All rights reserved