Sort Query Results By Calendar Month

Jun 12, 2013

I'm trying to sort my query results by the month value "Data (IMPORT).KP_MM_DS" but I get alphabetical results.

SELECT [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, Round((Count([Data (IMPORT)].KP_ISSU_ID_NB)/20)) AS CountOfKP_ISSU_ID_NB, [Data (IMPORT)].KP_MM_DS
FROM ([Data (IMPORT)] LEFT JOIN [Facility Lookup] ON [Data (IMPORT)].KP_FAC_NM = [Facility Lookup].[FAC NAME]) LEFT JOIN DEPTLOOKUP ON [Data (IMPORT)].KP_DEPT_ORG_NM = DEPTLOOKUP.[EPM DEPARTMENT NAME]
WHERE ((([Facility Lookup].[REPORTING MCA])<>"XXX") AND (([Data (IMPORT)].KP_SB_RGN_CD)="N"))
GROUP BY [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, [Data (IMPORT)].KP_MM_DS;

View Replies


ADVERTISEMENT

Sort By Calendar Month

Dec 5, 2006

How do I setup a query so I can sort a report by calendar month?

View 2 Replies View Related

Sort Query By Month

Jan 14, 2008

I have made a query where the user have to enter from date and to date, and the result will be between these dates. If I write, lets say, from 01.01.2008 to 29.02.2008 It brings the results for all entrys between these dates, But I wonder if it is possible to make two results for that one, one for january and one for february. (All in one query, just sorted after month) Or maybe in the report perhaps. But I have no idea if this is possible, or how to do this. I know how I can sort it after date with the same query, but I want the results per month. Would be very nice if I can get it listed in a report, so that I can print out one page with results for january, february, march etc.

View 1 Replies View Related

Sort Query Results

Feb 27, 2008

The following is produced by a query i've built....

4a (2007 Jan - Jun)
5a (2008 Jan - Jun)
4b (2007 Jul - Dec
5b (2008 Jul - Dec)

I need it in numerical order not alphabetical so it looks like this:

4a (2007 Jan - Jun)
4b (2007 Jul - Dec
5a (2008 Jan - Jun)
5b (2008 Jul - Dec)

Any ideas

View 5 Replies View Related

Forms :: Calendar Scrolling Month By Month

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

Sort On Results Of Crosstab Query

Jan 16, 2007

Below is the code to my crosstab query. I want to sort
descending) on the GrandTotal. I can apply a sort on the
results of the query but cannot sort in the query. It is
my understanding that this is due to this being a crosstab
query. Has anyone any advice on how I can somehow
automatically descend sort on the GrandTotal Column?
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 );
TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;

View 2 Replies View Related

Forms :: How To Sort Query Results With A Form

Aug 14, 2013

Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No).

So what I am trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.

My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?

View 1 Replies View Related

Set Month Only On Calendar Control

Jun 17, 2005

I would like the calendar control to default to the current month and year (if there is no date in the control source). I don't want to set it to the current date.

I've tried:
Month([CalendarOut].Value) = Month(Date)

but that doesn't seem to work.

Any suggestions?

Thanks!

View 2 Replies View Related

Sort By Month

Jun 21, 2005

Hello, I have the following query which gives me a count by day.

However, it sorts by day instead of day/month.
For example:

01/01/05
01/02/05
01/03/05
02/01/05
02/02/05 and so on...

Can I sort so it will show me all of January, then all of Febuary etc?

Thanks.

J.

View 5 Replies View Related

Queries :: How To Determinate Which Day Of Month To Be In Calendar

Aug 17, 2015

I have 31 checkbox to determinate which day of month to be in calendar with

Code : iif([01]=-1;cdate("01/" & [month] & "/2015");cdate("01/01/2001")

In the query to get date field to compare to paid date for that customer...

Now for list unpaid date I have all the customer who has date after today so I think about criteria >date() but I have all customer that have date before today and after today...

I need a new calculated filed with date in calendar checkbox to determinate the oldest date, do you have a better formula then this?

Code:
IIf([31]=-1;cdate("31/"& [month] & "/2015"; _
IIf([30]=-1;cdate("30/"& [month] & "/2015"; _
IIf([29]=-1;cdate("29/"& [month] & "/2015"; _
...........
...........
IIf([01]=-1;cdate("01/"& [month] & "/2015";"")))))))))))))))))))))))

View 3 Replies View Related

Sort Date By Month

Oct 8, 2007

In a report I need to sort by the month. the table field is "Appointment_Date" this is the code I am trying to use in the query behind the report . Appointment_SortMonth: Format([Appointment_Date],"mmmm" & " " & "yy")
but it doesnt sort. also the SORTING & GROUPING MENU does not work either. what do I try Now ???. your assistance will be appreciated.

Jabez

View 3 Replies View Related

Pull Month/Year/WeekDay From Calendar?

Mar 20, 2006

I've looked and looked and am afraid that I didn't find this because it's not possible...

The forms in my db are not the clearest, but one way of simplifying it for the users is to have them click on the calendar icon and then have a few fields automatically populate with the selected date's month, another with the year, and another with the day of the week.

Is this possible? I figured out how to have a field populated with the date, but with all of the specific queries needed, I have to do it this way...

Any thoughts? Also, is it at all possible (this is totally separate) to have an icon on the desktop that brings up a login sheet for which the entry users only see the front end, but the db admins log in and see the back end?

Thanks in advance!!
E

View 5 Replies View Related

Forms :: Month Calendar On Main Form?

Jun 11, 2013

I want to display a month calendar on my db's main form.

View 3 Replies View Related

Queries :: Sort By Year Is Sorting By Month

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

User Input Data In Table Using Form - Sort By Month

Mar 28, 2013

I have a table with product numbers and their cost. That table has 3 fields : SKU, Cost, Month. (The cost can change every month, therefore the list is built to have a cost / month)

I want the user to be able to input the data in the table using the form but I would like the form to be sorted Alphanumerically for the SKU numbers and then per month (in their normal order not alphanumerically).

I have a combobox (named month for testing purposes) to force the user to select one of the 12 months and the field type is Text.

Also I am unsure how to "autosort" (or apply a permanent sort) the form whenever it's opened. Will it be applied if I just set the sort and then restrict the user from seeing let's see design mode?

View 4 Replies View Related

Show Last Month's Results

Jan 23, 2007

Hello All,

I am having problems getting the following lines to do what I need exactly. The formatting is kicking my butt basically.

Bob helped with these lines but I actually need the result to be last month. For instance it is now Jan. 07 and I need the query to total up the hours flown for Dec. 06. On Feb 1 07 I need it to now display Jan 07 totals and so on.

Query Field:
Format$([tblEntry].[tblDate],'mmmm yyyy')

Criteria:
Format(date(),"mmmm yyyy")

How can I change this to do what I stated above?

View 3 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

General :: Comment History Field To Appear On A Form - Change Sort Order Of Results

Aug 28, 2013

I have after much stress gotten my comments history field to appear on a form.

The following code gives me what I needed

Code:
=ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0))

I have only one simple thing left, that is perplexing to say the least.

Some comment fields will be many short notes, spread over time.

My desire is to change the sort order of the results so the most recent (rather than the initial) comment is at the top.

View 5 Replies View Related

Reports :: Custom Reports Creating Chart Based On Month Not Calendar Year

Jun 15, 2015

I am editing a database that provides the option of creating custom reports, where the user can input a date range of their choice and receive aggregate data for that time frame. Although all of the numbers in the report are correct, I am having trouble with a chart that I inserted into the report.

Specifically, if the date range requested spans 2 calendar years (i.e. April 2014 through January 2015), the data for January 2015 appears at the beginning of the year (so the chart x-axis is for Jan through Dec, and the Jan 2015 data is showing up in Jan (as if it was 2014, not the end of the given range in 2015). When I try with smaller time frames within a calendar year, it adjusts just fine (i.e. shrinking the window so just March-May is displayed on the graph).

How to adjust the axis so that it properly records the data range- so that it would start the axis with April and end in January, for example?

View 2 Replies View Related

Queries :: Rolling 12 Month Query - Keeping Track Of Orders Placed For Given Part Number By Month

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

Queries :: General Date Field Criteria - Limit Results To Only Specific Month

Apr 16, 2015

I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.

View 2 Replies View Related

Report Sort Different From Query Sort

Mar 3, 2006

i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..

now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..

i dont want to re-do the report it took ages.. how can i fix this?

View 3 Replies View Related

Report Sort Is Not The Same As Query Sort...

Jan 9, 2005

I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??

View 3 Replies View Related

Forms :: Applying Sort And Filter In Form Results In Design Changes To Form

Dec 27, 2014

When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.

Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.

View 2 Replies View Related

Month By Month Query

May 17, 2005

please help me

i have a date field called open & i need to generate a query to count all entries by month, so far i can get all cases each day, but i'm unable to get the query to count them just based on the month & year..

any ideas please

View 2 Replies View Related

Getting A Value From Calendar Controller Into A Query

Nov 28, 2004

i got a problem with the calendar controller (access 2003)
I need the calendar0.value into a query so when i click on a date a form or rapport opens with the info that is on that spesific date, is this possibel?

View 1 Replies View Related







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