Solved

Select Max Time and Max Date from table where time and date are in separate columns

Posted on 2012-03-16
7
529 Views
Last Modified: 2012-03-16
Thank you for looking at my question,

I have a table, Table X, of transactions orderer by date and time. I want to select the most recent transaction.

The transaction date (TransDate) is the date only eg 15/03/2012

The transaction time (TransTime) shows date and time in the format 01/01/1900 12:43:29

Currently I obtain the most recent transaction by first querying Table X for Max(TransDate)

I use a second query to get the latest time for the most recent date

SELECT Max(TransTime) FROM qryTableX_MaxDate INNER JOIN TableX ON qryTableX_MaxDate.MaxOfTRAN_DATE = TableX.TransDate
GROUP BY qryTableX_MaxDate.MaxOfTransDate

How do I do this in one query?
0
Comment
Question by:Crxfrd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 37729172
Could you join the two fields together, e.g. take the TransDate and add the last 8 chars of TransTime, and then do a max on that? For example:

TransDate 15/03/2012
TransTime 01/01/1900 12:43:29

CombinedDate: CDate(TransDate & " " & Right(TransTime, 8))

and then do a Max on CombinedDate?
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37729189
I'm not sure I understand your question correctly, but maybe something like this:
SELECT Max(TransTime) FROM qryTableX_MaxDate WHERE qryTableX_MaxDate.MaxOfTRAN_DATE = (SELECT MAX(TransDate) FROM TableX)
0
 

Author Comment

by:Crxfrd
ID: 37729239
>Cluskitt,
Thanks for responding,

To try and clarify it, I want to get the most recent date and time from TableX where the date and time are in separate columns.

I tried a single query to select max(Date) and Max(Time) from TableX this gave me the maximum date value and the maximum time value but these aren't related to each other.

I need the max(Time) of max(Date) in TableX which I currently get using two queries and am asking can it be achieved using only one.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 18

Accepted Solution

by:
Cluskitt earned 500 total points
ID: 37729271
I see. What you want is:
SELECT MAX(Time) FROM TableX WHERE Date=(SELET MAX(Date) FROM TableX)
0
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 37729272
Crxfrd,

Did you try my idea?  I mocked up a table with separate date and time fields and tried it out to see if it would do the job, it seemed to do the trick, though I appreciate it is somewhat inelegant :o)

Update: Since posting this, I've just seen Mcluskitt's post, that looks a better option.  When you said do it in one query, I presumed you meant one select statement (i.e. no subqueries), hence my suggestion.
0
 

Author Closing Comment

by:Crxfrd
ID: 37729784
THank you
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 37730217
You could simply do:

SELECT TOP 1
  TransDate,
  TransTime
FROM
  TableX
ORDER BY
  TransDate DESC,
  TransTime DESC;

Or for a combined date/time:

SELECT TOP 1
  TransDate + DateAdd("d", -2, TransTime) As TransDateTime
FROM
  TableX
ORDER BY
  TransDate DESC,
  TransTime DESC;

/gustav
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question