Queries :: Sort Months Logically And Not Alphabetically
Mar 3, 2015
I have a report (based on a query)which informs staff of students who withdrew from our school, and the month and date they withdrew (sorted ascending). I am grouping by month and it looks like the attachment I've included.
It works well enough, but what I would really like is for the month column (Month([withdrawldate]) to show January for 1, February for 2, and so on, but I find that when I do that, the months sort alphabetically, and not logically.
View Replies
ADVERTISEMENT
Mar 16, 2006
I have a series of subforms that show customer mix percentages by month. These forms will be used for data entry and to ease the process I would like to have months in order of our fiscal calendar, July through June. Right now the months are listed alphabetically. Is there a way I can list the months in order without having to resort to a naming convention such as:
01June
02August
03September
ect.
Thanks
View 1 Replies
View Related
Mar 16, 2006
I have a series of subforms that show customer mix percentages by month. These forms will be used for data entry and to ease the process I would like to have months in order of our fiscal calendar, July through June. Right now the months are listed alphabetically. Is there a way I can list the months in order without having to resort to a naming convention such as:
01June
02August
03September
ect.
Thanks
View 2 Replies
View Related
Mar 22, 2013
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
View 2 Replies
View Related
Jul 7, 2007
I'm currently developping a database and in the process of re designing my queries for my users needs. one of these is to be able to search entries of the database determinable by their first letter (e.g. find all customers whose name begins with 'A'). Can anyone help me with how to do this. So far I have managed to create a 'Like' query, so the searches are not case sensitive, but i still need to make it search alphabetically.
Any help, much appreciated
Sam
View 13 Replies
View Related
Aug 9, 2013
I am pulling sales results per customer and month. For presenting the numbers in a diagram I would like to show 0 sales for months in which no sales were generated. At the moment the query results only the months in which there are sales > 0. Is there a trick that I don't know?
SELECT Year([InvoiceDate]) & ' ' & Month([InvoiceDate]) AS [Month], totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]) AS Ausdr1
FROM Invoices INNER JOIN customers ON Invoices.CustomerID = customers.CustomerID
WHERE (((Invoices.InvoiceDate) Between #1/1/2013# And #7/1/2013#) AND ((customers.CustomerID)=145))
GROUP BY totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]), Year([InvoiceDate]), Month([InvoiceDate])
ORDER BY Year([InvoiceDate]) DESC , Month([InvoiceDate]) DESC;
View 3 Replies
View Related
Sep 16, 2013
As a Access newbie I am looking into a problem that I hope to solve structurally and "transparently" via Access in stead of Excel.
I would like to compare two similar tables for different months. I am looking for:
+ loan id's that are new in table(t), i.e. not exist in table(t-1)
+ loan id's that exited: that is exist in table(t-1) and not in table(t)
+ loan id's that stayed in both
How do I solve this in the easiest way?
View 4 Replies
View Related
Aug 6, 2014
We have a field (tbl_Enrolment.CourseCompletedOn) which holds the date a colleague completed a course. I want to bring through all courses completed within 12 months of todays date ( Date() ) from our database. I checked google and it suggested YEAR but that would only bring through dates with the same year, not within a 12 month period.
View 4 Replies
View Related
Mar 3, 2015
I have a query that pulls data for the current month and the last two months. It had been working fine until 2015. The problem is that it's looking for the last two months but for 2015. How do I edit my formula to pick up the two months and for it to know it's a different year??
Code:
CMSPM2: IIf([LOB]="CMS" And Month([Month])=Month([Forms]![Main Menu]![EndDate])-2,Count([Question]),0)
View 2 Replies
View Related
Mar 26, 2007
I am trying to figure out how to sort a column in access the same way it comes out in excel.
I have a text list of "buildings" ie: 89, 33, 270C, 270B, 270A etc. In excel this simply sorts alphanumerically ie 33, 89, 270A, 270B, 270C.
I made the mistake of changing the format from text to number, lost all the data and successfully scared myself into not wanting to experiment anymore so any input I could get would be very appreciated
View 8 Replies
View Related
Aug 6, 2014
I'm trying to breakdown a date into years, months, and days. Simple right?
The code I've been using for months returns a negative month if it happens to be the next month. Instead of returning 1 Year, 1 Month, 23 Days, it will instead return 1 Year, -1 Month, 23 Days.
Code is:
Code:
Months: IIf(IsNull([TIR]),0,IIf(Day([TIR])<=Day(Date()),DateDiff("m",[TIR],Date())-[Years]*12,DateDiff("m",[TIR],Date())-[Years]*12-1))
View 4 Replies
View Related
Jun 18, 2015
I have a query that is supposed to be showing todays data (as it is labeled) but it keeps pulling today's number date (i.e. the 1st, or the 17th) for every month, when i only want June 18th not including jan/feb/march etc. 18th.
I started running this query in may, that's why it only shows may and june's data.
I'm thinking it's definitely a problem with the criteria but check out the attachments and let me know
View 14 Replies
View Related
Jun 23, 2014
I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.
I could represent an activity using a colour i.e. yellow for delivery, green for design etc.
Also do I need to create a table with all the dates? Is there a quick way of doing this?
View 7 Replies
View Related
Oct 8, 2014
I'm having problems figuring this one out -- I'm fairly new to access. I have included a JPG attachment that shows the information I currently have in use and what I would like. I need a SQL statement that will generate the rolling 12 months by period.
The end results will be a table that is populated with the rolling 12 month values so I can qry a sharepoint infopath form to look up the rolling value (look up against lng_PERIOD and chr_EE_RACF) to populate the YTD values.
View 4 Replies
View Related
May 4, 2014
I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).
As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.
View 1 Replies
View Related
Nov 15, 2013
i have to process 3 upcoming months bills .. say i want to show in query like this bill for Nov, Dec, Jan based on the issue date i mentioned earlier and the months to be find from the numeric month field like 3 i mentioned which mean 3 months ahead from issue date.
View 8 Replies
View Related
Dec 12, 2013
I have to make program for a school
Some students pays fee for one month, some for two months and some 3 months.
School administration requires me to print notices for every student according their payment mode after comparing with next due months and already paid months.
Some notices will show their fee for the months as
(Id= 1 Jan-14, Feb-14),
(Id=2 Jan-14, Feb-14, Mar-14),
(Id =3 Dec-13, Jan-14) Considering as Id 3 has not paid due month i.e. Dec-13 Fee and his payment mode is 1 month only.
Here Id means Id number alloted to Students.
View 1 Replies
View Related
Feb 13, 2015
I have an existing combo box on a form that gives me access to 297 different selections. The problem is that as things have been added to the table it sources they have become out of order alphabetically. At the time of creating the combo box, I don't believe the option to display alphabetically was selected. Can I change the properties of this combo box now so it will display the 297 options alphabetically.
If So............ How?
View 4 Replies
View Related
Aug 28, 2013
Trying to get a formula or function for calculating profit for each month between selected dates. Partial month calculations are required.
Source Data (record) examples:
Contract1: Startdate = 6/30/12; EndDate = 9/22/12; DailyProfit = $500; MonthlyProfit = $9500
Contract2: Startdate = 7/7/15; EndDate = 12/17/12; DailyProfit = $600; MonthlyProfit = $11400
Contract3: StartDate = 8/1/12; EndDate = 6/30/13; DailyProfit = $350; MonthlyProfit = $6650
User Query #1:
Notes:
ReportStartDate will always be entered as the first day of the month;
ReportEndDate will always be entered as the last day of the month
The report will always show 6 consecutive months
User selects
ReportStartDate = 7/1/12;
ReportEndDate = 12/31/12
Notable conditions:
-The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0.
-The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value.
-The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.
Request: I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.
View 6 Replies
View Related
Apr 22, 2013
I 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.
View 6 Replies
View Related
Sep 14, 2014
I have drop down list linked to table included "agent names" , the names appeared normally in the form but not Alphabetic (A-Z) although the table was alphabetic .
View 1 Replies
View Related
Dec 7, 2006
i've now converted a large database to run with sql as well as linked jet tables. The main issue was checking every recordset to ensure it opened properly. I also found that sql returns a few different errors code from Jet, that needed trapping differently = all OK.
Now, in some cases I had opened the backend Jet table directly in order to use seek on a particular index, so this needed changing, as this is not available with SQL
I may well be wrong but i couldn't see an easy way of traversing a recordset table in SQL/ODBC according to a particular segmented key
Instead I was able to modify my process to extract the data I wanted with a query, and I then used a recordset on the query, which is fine.
HOWEVER, to emulate the index I was using, the query is sorted on about 6 or 7 different segments, the first of which is CustomerCode. Now when I opened the query to view it, I changed the sort order by clicking on a date column (as it happens) to sort it. When I then closed and reopened the query it still opened sorted on the date column, which is not right, as the customners are now all out of sequence. When the whole dataset was re-extracted it did return to the original and correct order
THEREFORE Is there a way of forcing a query to reset itself to its original ordering sequence. I think it is not likely that any user would ever open a query directly anyway, but it would be nice to ensure the query is always processed in the intended order. Perhaps it isn't a problem at all - maybe when a query recordset is opened in code, the sort order is reset anyway, but I would appreciate some views on this.
Thanks in anticipation
View 3 Replies
View Related
Jul 23, 2013
I am creating a query to select and sort events whose payments are overdue, prioritizing those that are most overdue at the top. The catch is that the due dates for payment varies based on several conditions and criteria.
Here's my end goal of the sort order (it's not in SQL, I'm just describing it):
1st step- If [Program_Code]="BD" Or "GT" Or "SG" Or "SC" Or "PR", then the sort criteria is [Date_of_Event] < Date()
2nd step- If [Program_Code]="WE" Or "KD", then the sort criteria is [Date_of_Invoice] < Date()
3rd step- If [Program_Code]="ZM" And [Cost_Category]="Full Price" Or "Discount", then the sort criteria is [Invoice_Date]+30 Days is < Date()
4th step- If [Program_Code]="BD" Or "PR" And [Deposit_Paid] Is Null, then the sort criteria is [Invoice_Date] < Date()
I'm not sure how to go about this but I have a feeling it involves an iif statement in the SQL and/or breaking it into smaller queries that are then consolidated into a "master" query.
View 14 Replies
View Related
Sep 13, 2013
I have a query that is returning running conditions for a machine over multiple shifts. the table is set up like this
Code:
date shift parameter condition
09/13/13 1 front 500
09/13/13 1 middle 450
09/13/13 1 back 475
09/13/13 2 front 510
09/13/13 2 middle 460
09/13/13 2 back 490
this is just a small example. there are about 24 different parameters reported on in a shift..I want the query to sort by date ascending, shift ascending, and then by parameter in a specific custom that i define. I want it ordered "front, middle, back" for each shift. Again this is just an example, there would be 24 parameters I want in a specific order. Then do I need to define the sort order again for the report based on this query?
View 3 Replies
View Related
Feb 26, 2015
I have a created a crosstab query which gives me the results I need, but I want to sort the row headings differently. These are not numbers, but machine sizes which range from 4 Metre to 20 Metre. Currently, the crosstab gives me:
10 Metre
12 Metre
14 Metre
17 Metre
20 Metre
3 Metre
4 Metre
5 Metre etc
But I need to show it as:
3 Metre
4 Metre
5 Metre
10 Metre
12 Metre
14 Metre
17 Metre etc.
The field is short text data type and data comes from an ODBC linked table to SQL server table, and I am using Access 2013.
View 4 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
