ORDER By Date (SQL)

I have a query which i've ordered by date with:

ORDER BY status_reports.date;

But I want it to display in reverse order with most recent date first for example i want it in order of 18/sept/05 then 15/sept/05 then 10/sept/05.

im guessing i just need to add to the above SQL but im not sure what!

Can anyone help please

Thanks


Post your answer or comment

comments powered by Disqus
Hi. I'm writing a query in vba (first time) and i've got the recordset i want, but i'm having trouble sorting it by the date.

I've created a Date (Dim CurrentDate As Date), and assigned it liek this:

	Code:
	CurrentDate = DateAdd("m", 1, Me.ContractStartDate)

i'm putting CurrentDate into the sql query like this:

	Code:
	strSql = "SELECT Tenancies.ID, Tenancies.Rent, Tenancies.ContractStartDate As PeriodFrom, Format('" & CDate(CurrentDate) &
"', 'dd/mm/yyyy') AS PeriodTo,

and the Order by clause:

	Code:
	ORDER BY PeriodTo;

Problem is, it orders it first by the date, then month, then year. I need it reversed, to order it by year, month then date.

anyone have any ideas? I could just change dd/mm/yyyy to yyyy/mm/dd but thats not very user friendly

I applogies if this has been asked before, i saw many threads about grouping, but none i could figure out how to apply to what i need.
Also im sorry if this post seems rushed, i wrote it up nicely only for the connection to go a few seconds after hitting submit

Basicaly, im makeing a database of film footage on tapes.
Each tape has a Unique ID,Production (text), Date Field, and a Notes field.


eg.


	Code:
	ID   Production     Date(DD/MM/YYYY)   Notes 
  
1    Name A         06/10/1998       Notes
2    Name B         15/02/2001       More Notes
3    Name A         08/10/1998       Even More Notes
4    Name D         18/02/1997       Yadadyada
5    Name D         20/02/1997       blahblahblah
6    Name B         18/07/2001       ectectect
7    Name A         07/10/1998       lalala
8    Name C         19/02/1997       Finalnotes

I would liked them in order of dates from earliest to newest, but grouped by Production.
As each production has multiple tapes, each with their own date, i would like to take the minimum date and use that to base the order on.

eg.


	Code:
	4    Name D         18/02/1997       Yadadyada
5    Name D         20/02/1997       blahblahblah
8    Name C         19/02/1997       Finalnotes
1    Name A         06/10/1998       Notes
7    Name A         07/10/1998       lalala
3    Name A         08/10/1998       Even More Notes
2    Name B         15/02/2001       More Notes
6    Name B         18/07/2001       ectectect

Note that D comes before C, becauses D's lowest date is lower then C's lowest date.
The order dosnt go D,C,D either because the Productions must be kept grouped together.

I hope thats clear enough :-/

I assume i need multiple querys for this, but i just cant work out how.
Any help or advice would be most welcome

(im dealing with 1000+ odd tapes here, so doing it manualy isnt a solution )

Hi all, i have a query which will be used for reports, and only the last order placed by a customer is needed to be shown. Here's the table used in the query:-

tblCustomerPurchases
CustomerNumber
PurchaseNumber - PK,Autonumber
DateOfPurchase
SeasonID
TotalCost

So obviously for one customer there can be several orders, but i just want to show the latest one by the date field. I have tried grouping by the date field on both Last and Max, but with all the other fields from the table in the query (i need to have all the fields from the table for the report by the way) it still shows all the orders for a customer.

If i only include the customernumber field and dateofpurchase field and then group by date i have 4923 records, which is the correct amount, only one order per customer (the latest one) but adding any other fields to the query gives me the full 7000 odd orders (several per customer).

Any help would be much appreciated, many thanks in advance.

Hi Folks

I have the following as the Row Source for a list box:

SELECT Field1, Field2 ORDER BY Field1, Field2;

Supposing the possible values for Field1 are 1 and 2, how do I get Field2 to sort in ascending order if the value in Field1 is 1 but in descending order if the value in Field1 is 2? I'm guessing it's something like this, but I don't know enough about SQL to work out the correct syntax:

SELECT Field1, Field2 ORDER BY Field1, If(Field1=1, Field2 ASC, Field2 DESC);

Any help would be very welcome.

Thanks

Remster

I have a table that tracks customer orders (by date of order) and I need to find out all customers who have ordered prior to January 1, 2006.

I am stuck between using a Where or a Between criteria but am still having problems with the correct phrasing.

Please help!

I have trouble getting the data in the right order from a table with the columns Date and City:

APRIL 8 2010 | AMSTERDAM
APRIL 9 2010 | ZAGREB
APRIL 10 2010 | AMSTERDAM
APRIL 11 2010 | NEW YORK
APRIL 15 2010 | NEW YORK

I want to have the record with the nearest Date in the future on the top. Then I want to display all the other records with the same City under it ordered by date. Next I want to have the next city with the nearest date ... and so on.

So the result should be:

APRIL 8 2010 | AMSTERDAM
APRIL 10 2010 | AMSTERDAM
APRIL 9 2010 | ZAGREB
APRIL 11 2010 | NEW YORK
APRIL 15 2010 | NEW YORK

Who can help me with a single select statement to get this?

I am lost

I am creating a database to manage the volunteers in my company have worked. I have two databases, one is their volunteer information. The other database is an "HoursDatabase" where I can input the volunteer's ID number, the date, start time, and end time of each time a volunteer has been working with us. (From here I will calculate their hours volunteered, etc.) These two databases are linked by the "VolunteerIDNumber" field. I have created forms to facilitate the data entry into these two databases. The VolunteerInformation form is working perfectly, but the HoursInformation form is having some issues. I have created a tabular view form where the data can be entered and it appears to work perfectly except it refuses to order by anything other than the volunteer ID number. I would like it to order by date, or at least the "TimeIntervalIDNumber" (the primary key for the database). I would really like to keep these in some sort of chronological order. Please assist.

Thank you in advance,

Jewlia

This is my first Access project, I have been doing a lot in VBA and as I use access 2002 there has been a thread for every problem I've encountered until now (most useful threads I have found have been from access-programmers ).

I have a form which initially displays a list of records in a listbox (list102). Originally I had a SQL statement that selected another field [Actions] which is a memo field and corresponding [Date] from list102 and displayed these in a non-visble list box (list162). The data in list162 was then compiled into a single string and displayed in a textbox (text160).

This method however limited the memo to 255 characters which is much to small. Now instead of compiling the contents of list162 into a string I use the Date column from list162 and an identifier strVesselNumber in a Dlookup function to find the corresponding [Action] memo field as the final string strCActiontext is created (I use list162 rather than using list102 in the Dlookup is because list102 is not a multiselect listbox).

In the Dlookup function the dates from list162 are stored in dateInsp which is dimensioned as a date using CDate. The dates in the data tables are automatically stored in the format DD/MM/YYYY and are a date/time field.

The problem I'm having is that when DD

I have a query that uses 2 Fields from a large table. Cashier and Date. The problem I am having is I would like the select a time frame, a Start and Stop date, and count how many times the cashier comes up for that day.

Sounds easy, I would think so but having a problem. The dates are automatically imported into the large table as format 01/01/2005 12:00pm (Can't do anything about this).

When writing the SQL and the Count function, it is using the time as well in its calculation, but I simply want it to count on the date only. So unless a cashier existed exactly in the table as 01/01/2005 12:01pm, 01/01/2005/12:01pm it only counts it as a total of 1, instead of twice for that day.

I simply want the SQL function to read the Date field as 01/01/2005, and drop off the Time. I can format it, but only changes it visually and the SQL statement still sees the time.



Any help would be appreciated.


I found this code on the boards here, almost what I want but does not group each Cashier by day and total, it does the total of all cashiers for that day. I need the Cashiers sorted for each day, and totalled.

SELECT Format([Date1],"dd mmm yyyy") AS Day1, Count([ Cashier1].[Cashier1]) AS CountofCashier
FROM CashierQ1
GROUP BY Format([Date1],"dd mmm yyyy"), Format([Date1],"dd mmm yyy")
ORDER BY Format([Date1],"dd mmm yyyy");

HELLO
I'm working in Access database .. i'm using this SQL query to extract dates in English format below :
SELECT DISTINCT Format([Date Commande],"yyyy/mm") AS Expr1
FROM Relevé
GROUP BY Format([Date Commande],"yyyy/mm");
Result:
Expr1
2006/10
2006/11
2008/10
2008/11
2008/12
2009/01
AS YOU see i have the good order of my dates ..But when i m trying to have the same result (Order)
in france format date ... my query doesnt work properly :
SELECT DISTINCT Format([Date Commande],"mm/yyyy") AS Expr1
FROM Relevé
GROUP BY Format([Date Commande],"mm/yyyy");
Result is :
Expr1
01/2009
10/2006
10/2008
11/2006
11/2008
12/2008

AS you see the order of date is not respected..
My question is how to convert date from english format to frensh format...IS THERE ANY function or script to do this???.
Im beginner in ACCESS..So i m pleased to get a response.
THANK YOU.

Hello All,

I have a query which runs, and i extract the "month and date" from a field using this function:

MonthYear: Format([dateContractEnds],"mmmm yyyy")
(ascending)

When i run the query, it works great, and the dates are sorted as they are supposed to be (I.E.// Jun 2010 first, August 2010 next, Jan 2011 next, and so on in order of smallest to greatest date).

However, i now created a report and using the "sorting and grouping" option on this field (MonthYear). It now mixes up all the dates and they aren't in the same order as the query.

Any rationale?

I basically want the report to be ordered by month and year...

Andrew.

Need help implementing way to obtain Median grouped by 2 columns and filtered by date in a Crosstab query.

We use Access as front end reporting tool with linked tables to SQL Server 2008 backend db. Management wants a time analysis report of each performer by median (in seconds) per PO lines of invoices with 1 to 10 lines only. We will later be doing a volume analysis to account for invoices with lines greater than 10. I kinda have the DMedian VBA function I got from MSDN site working, but I can’t figure out how to:

1. group by PERFORMER and then by NUM_LINES in the set strSQL = … line(s) of code in the VBA module(in seconds)
2. call DMedian function from within the Crosstab query.

I have attached (zipped per rules for new register users with less than 10 posts) a temporary db I threw together for sole purpose of trying to explain and use as test to get this Median issue worked out. The table, tblmedian_test_data, was dump of some test data from actual db. The crosstab query, qry_median_avg_test_1, is where I need to get this median thing worked out.
The db has a few VBA functions I started to play with trying to figure this out. The one I got from MSDN was commented better than any of the others I’d seen but at this point I don’t care which one we use. I just need to get this working. Lastly I am including the sql for the crosstab query and the VBA code for the DMedian function. Again there are other functions in db but this is one I’ve been trying to get to work.

I welcome any and all help!


	Code:
	TRANSFORM Nz(Avg(DMedian("SECONDS","tbl_median_test_data")),0) AS CrossTabAvgMedian
SELECT tbl_median_test_data.NUM_LINES
FROM tbl_median_test_data
WHERE (((tbl_median_test_data.APPROVE_DATE)>=#1/1/2013# And (tbl_median_test_data.APPROVE_DATE) 0 Then
        'strSQL = strSQL & " WHERE " & strCriteria
        strSQL = strSQL & " GROUP BY " & strCriteria
    End If
    
    strSQL = strSQL & " ORDER BY " & strField
    
    Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    ' Check the data type of the median field
    intFieldType = rstDomain.Fields(strField).Type
    Select Case intFieldType
    Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
        ' Numeric field
        If Not rstDomain.EOF Then
            rstDomain.MoveLast
            intRecords = rstDomain.RecordCount
            ' Start from the first record
            rstDomain.MoveFirst
    
            If (intRecords Mod 2) = 0 Then
                ' Even number of records
                ' No middle record, so move to the
                ' record right before the middle
                rstDomain.Move ((intRecords  2) - 1)
                varMedian = rstDomain.Fields(strField)
                ' Now move to the next record, the
                ' one right after the middle
                rstDomain.MoveNext
                ' And average the two values
                varMedian = (varMedian + rstDomain.Fields(strField)) / 2
                ' Make sure you return a date, even when
                ' averaging two dates
                If intFieldType = dbDate And Not IsNull(varMedian) Then
                    varMedian = CDate(varMedian)
                End If
            Else
                ' Odd number or records
                ' Move to the middle record and return its value
                rstDomain.Move ((intRecords  2))
                varMedian = rstDomain.Fields(strField)
            End If
        Else
            ' No records; return Null
            varMedian = Null
        End If
    Case Else
        ' Non-numeric field; so raise an app error
        Err.Raise errAppTypeError
    End Select
    DMedian = varMedian
    
ExitHere:
    On Error Resume Next
    rstDomain.Close
    Set rstDomain = Nothing
    Exit Function
HandleErr:
    ' Return an error value
    DMedian = CVErr(Err.Number)
    Resume ExitHere
End Function



I am attempting to sue an SQL statement to filter a query. I am using this statement:

strSQL = "SELECT * FROM qryGroup WHERE Left([TxGroup],3) = " & strGroup & " AND ActiveInGroup = True" & ORDER BY [SSAN];

Everything works until I try to use the "Order By" criteria (this is where I get a syntax error.

I am giving the user the option of sorting several different ways and I don't want to make 5 different queries.

Can anyone help?

Don

Can someone rescue me! - 4 days of trying everything and everywhere but defeated at last.

Me.RecordSource = "select * from [tblLVRStatements] where [tblLVRStatements]![clientID] = " & clientIDVar & "ORDER BY [tblLVRStatements]![NumberStatement]"

Above is just one of my many attempts! not to work....

I have a table; tblLVRStatements.

I have an ID for the client; clientID and have a variable;clientIDVar as an identifier.

I have Statements which are in order in the table and wish to display these in the order (from NumberStatement field) in which they appear in the table BUT they display in random order with the following code;

< Me.RecordSource = "select * from [tblLVRStatements] where [tblLVRStatements]![clientID] = " & clientIDVar

Me.OrderBy = Me.[NumberStatement]
Me.OrderByOn = True >

I thought to try and order them with the SQL ORDER BY but my syntax is 'up the spout'! Please!!!!!

I have a a table with primary key being [Account Number]. I am trying to order all my accounts by their priority and then return the next account to be worked (the top account). i use the sql like:


	Code:
	SELECT  TOP 1 [Account Number]
FROM LiveData
WHERE WorkingRep Is Null
ORDER BY Priority ASC;

However, upon using the order by clause, ALL account numbers are returned. Likewise, i can take out the order by clause it works, but i am unable to work my first priorty, then 2nd priorty, etc. Any ideas?

I have been working with SQL queries for a while now but only recently has there been a need to introduce sub queries.



The following code works fine but I now need to be able to order the data by “table4.price” I have tried adding “ORDER BY table4.price” to the very end of the string and get an error informing me of an object required, so I tried adding it to the table4 sub query but there is not order to the results.


SELECT table1.*
FROM table1
WHERE table1.ID IN(SELECT ID FROM table2 WHERE allow=true)
AND table1.ID IN(SELECT ID FROM table3 WHERE catalogue=1)
AND table1.ID IN(SELECT ID FROM table4 WHERE price>0 AND price

I'm trying to ORDER BY the first column in my Union Query. The problem is that the first column is a formatted field. Everything I have tried produces errors. Below is a sample of what I have.

I have tried Order By:
Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting' Syntax Error (missing operator)
Format([Month Reporting],"mmmm yyyy") Error stating the field is not selected by the query
[Month Reporting] Error stating the field is not selected by the query

SQL
SELECT Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting',[Portfolio]
From [qryRetrievalTotalsUS]
ORDER BY [Month Reporting]

Union ALL SELECT Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting',[Portfolio]
From [qryRetrievalTotalsCAD]
ORDER BY [Month Reporting]

UNION ALL SELECT Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting',[Portfolio]
From [qryRetrievalTotalsPR]
ORDER BY [Month Reporting];


Thoughts? Suggestions?

I have created a named index which has multiple columns in said index.

I am trying via SQL to specify that index name as the ORDER BY object in a SQL statement. Access 2007 does not like such. This is a query against a FE temp table, so no other DB's involved than Access 2007.

Is there any syntax by which I can sort based on that index by referring to the index name and not have to specify the same column structure in the ORDER BY clause that is already defined in the index? After all, what would the point be to name indexes if you can not specify them by the same name?!

Paradox for Windows / Object PAL had such support... I could switch indexes to a known name index, and what ever the index had defined, the table would end up sorted thusly.

In this case I happen to be updating on-the-fly the SQL statement in a Form based on a Query to change the record display sort order. It works perfectly for sorting by single columns... but now I need to update the ORDER BY setting it to multiple columns, so looking for a way to ORDER BY indexname and what ever the index has defined will be the sort.

Suggestions of how to accomplish such? TIA!

P.S. Access 2007 is the version.

With the help of IanH I was able to throw together this SQL:

SELECT Month(ReviewDay)&Year(ReviewDay) AS MyMonth, Count(*) AS MyCount, Sum(IIf(SignedOn-SchTime

I have tried almost everything and I still can get this to work. I thought I had it with this attempt until I inputted October data and found that Access was sorting it by Number vs. Date.

I am simply trying to compare the number of tickets opened each month to the number of tickets closed each month. I also need to address how a report will appear if no tickets were opened or if no tickets was not closed in a month.

Incase the SQL in screencap is too difficult to read:

Query: qryTotals_Assigned_And_Completed - This calls the following two queries.
#############################################
SELECT qry_AssignedByMonth.MyAssigned, qry_AssignedByMonth.Count, qry_ClosedByMonth.MyClosed, qry_ClosedByMonth.Count, qry_AssignedByMonth.Month, qry_AssignedByMonth.Year, qry_ClosedByMonth.Month, qry_ClosedByMonth.Year
FROM qry_AssignedByMonth INNER JOIN qry_ClosedByMonth ON qry_AssignedByMonth.Month = qry_ClosedByMonth.Month
ORDER BY qry_AssignedByMonth.Month, qry_AssignedByMonth.Year, qry_ClosedByMonth.Month, qry_ClosedByMonth.Year;
#############################################
qry_AssignedByMonth
#############################################
SELECT (Format([DateAssigned],"mmm"" '""yy")) AS MyAssigned, Str(Month([DateAssigned])) AS [Month], Str(Year([DateAssigned])) AS [Year], Count(*) AS [Count]
FROM tblRequests
GROUP BY (Format([DateAssigned],"mmm"" '""yy")), Str(Month([DateAssigned])), Str(Year([DateAssigned]))
ORDER BY Str(Month([DateAssigned])), Str(Year([DateAssigned]));
#############################################
qry_ClosedByMonth
#############################################
SELECT (Format([DateClosed],"mmm"" '""yy")) AS MyClosed, Str(Month([DateClosed])) AS [Month], Str(Year([DateClosed])) AS [Year], Count(*) AS [Count]
FROM tblRequests
GROUP BY (Format([DateClosed],"mmm"" '""yy")), Str(Month([DateClosed])), Str(Year([DateClosed]))
ORDER BY Str(Month([DateClosed]));
#############################################

Hi There!

In my query i can easily write "ORDER BY coaching.date" in my SQL. But instead I want to order by the output of a dropdown menu... I want the user to select what he wants the data ordered by!

I have tried ORDER BY [forms]![coachingstatus]![dropdown], but that doesn't work!

Help please :-)

// JR

On another forum I gave someone the following query as a solution.

Select WeekDayName(DatePart("w", Date_Time)) as The_Day, DatePart("h", Date_Time) +1 as The_Hour, Avg([Val]) as The_Avg
From [CO-Test]
Group by WeekdayName(DatePart("w", Date_Time)), DatePart("h", Date_Time) + 1
ORDER BY WeekdayName(DatePart("w",Date_Time))

The above query groups a lab result by DayName and by Hour resulting in something like this

Day…………....Hour……..The_Avg
Saturday………..1………….. .7387
Saturday………..2………….. .8783
Saturday………..3…………. .6745
Saturday………..4…………… .5424

And so on for 24 hours. And then the next day is done the same way. And so on for four days. The data is here and spans the 4 day period from may 5 2007 (Saturday) to may 8 2007 (Tuesday).

The only problem is the ORDER BY clause. Given the above dates, I would like the output to be in this order (for the 4 days Saturday thru Tuesday).

Sat
Sun
Mon
Tues

Instead I get this:

Monday
Sat
Sunday
Tuesday

Two questions:

(1) Why does my Order By clause produce the above?
(2) How do I change it to get what I want?

I have a query that i wanted ordered by most recent date so that the note date shows July first, then June, then May and so on.

heres my sql

strSQL = "SELECT tblCollectionNotes.NoteID, tblCollectionNotes.CustID, tblCollectionNotes.CollNote, " & _
"tblCollectionNotes.NoteDate, tblCollectionNotes.InvoiceNumber " & _
"FROM tblCollectionNotes, tblDelinquentInvoices " & _
"WHERE (((tblCollectionNotes.CustID)='" & gstrCurCustID & "'))" & _
"and tblCollectionNotes.CustID = tblDelinquentInvoices.CustID and " & _
"tblCollectionNotes.InvoiceNumber = tblDelinquentInvoices.InvcNum " & _
"ORDER BY tblCollectionNotes.NoteDate;"

I've inherited this database that is a nightmare the way it was structured. I have this query where I have to type in yesterdays date and it then populates the appropriate fields. Here is the SQL statement.

SELECT [SOLTEX_Daily_MTD/FGI].[PART NUMBER], [SOLTEX_Daily_MTD/FGI].SOL_PART_NUMBER, [SOLTEX_Daily_MTD/FGI].[5/06] AS QTY, [SOLTEX_Daily_MTD/FGI].COST, [Qty]*[COST] AS TOTAL, [UPDATE SOLTEX].Family INTO BILLINGS
FROM [SOLTEX_Daily_MTD/FGI] INNER JOIN [UPDATE SOLTEX] ON [SOLTEX_Daily_MTD/FGI].[PART NUMBER] = [UPDATE SOLTEX].[Assembly Name]
WHERE ((([SOLTEX_Daily_MTD/FGI].[5/06])0))
ORDER BY [UPDATE SOLTEX].Family;

I would like to automate this if possible. Right now I have to open the query in design and change the date for the Qty to next date which is always
date()-1.

The table has the dates as the column names. Appriciate any help....


Not finding an answer? Try a Google search.