Hi there,
I have a table, which has an order number, a comment & a date the comment was added.
Therefore you could have 3 or 4 comments against one order number. All I want to do is create a table via a query which takes my existing table and shows me the latest comment based on the date. So for example, if there are two comments for an order, one done today and one yesterday I want my query to show me only the comment done today.
I thought this would be easy, but when I use the MAX on the date on the query designer I am still getting two rows of data for some orders.
I just can't seem to get my head around what I am doing wrong.
4  1696 
Please post the SQL for the query concerned - we can't answer you without having the details of the tables and fields concerned in front of us.
-Stewart
NeoPa 32,563
Recognized Expert Moderator MVP
First of all let me warn you against duplicating the pre-existing data into a separate table. This is very rarely a good idea (See Database Normalisation and Table Structures). Instead, just use the SELECT query when that specific data is required.
Next, please check the new title of the thread. This is not only a valid title, but it also gives you clues as to what you're actually trying to do. Please remember in future to use a sensible title for any new question threads.
Now let's look at your actual issue. You're not looking to return the value of the maximum date, but actually you want the record associated with that maximum. This can be done in the following format : - SELECT *
-
FROM [YourTable] AS [YTo]
-
WHERE [CommentDate] In(SELECT Max([CommentDate])
-
FROM [YourTable] AS [YTi]
-
WHERE YTi.OrderNo = YTo.OrderNo)
Does that make sense?
Thank you very much for your help, I've tried what you said above and it worked straight away.
Thanks again.
NeoPa 32,563
Recognized Expert Moderator MVP
I'm very pleased to help, and almost more pleased even to hear that what I knocked up worked first time. Very gratifying :-)
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: jas |
last post by:
> This is what I want to do with the data table below.
>
> I only want it to show one id for each issue. ( ie, 4001 only once,
> 4002 only once, etc. But only the one with the latest date and time)
>
> Ie. 4001 should only be in the table once, with the latest date -
> which should be :
> 4001 09/12/2003 17:12:09 (as I only want to show...
|
by: John |
last post by:
I have a query that returns this recordset:
Bob, 1/1/2000
Bob, 3/1/2001
Bob, 2/4/2004
Bob, 5/9/2002
I need the query to return this instead:
Bob, 2/4/2004
So, it needs to return the most recent date in records that otherwise
|
by: TGEAR |
last post by:
I have a column which stores dates (datetime data type). I would like
to fetch one data which is the most latest date among them. So if there
are 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one I
need to output.
how can I write this in sql stmt?
select datebegin
from testtable
where datebegin = ?????
|
by: TGEAR |
last post by:
I have an ItemSTDPriceHistory table as below and this is a child table
of itemlookup table with one to many relationship.
if exists (select * from dbo.sysobjects where id =
object_id(N'.') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table .
GO
|
by: keri |
last post by:
Hi,
I am creating a table where I want to use the date as the primary key -
and to automatically create a record for each working date (eg Mon to
Fri) until 30 June 2007. Is this possible? I do not want my user to
have to create a record for each date.
Thanks,
| | |
by: piyushdabomb |
last post by:
Hi All,
Currently, I have a SQL query where I need to grab the SECOND latest date by group.
COMPONENTID COMPLETIONDATE
CBT_HAZCOM 02/26/2007 15:17:00
CBT_HAZCOM 07/31/2007 21:23:00
QS 02/12/2007 13:51:00
SHEN158 02/12/2007 13:29:00
|
by: ringer |
last post by:
I have a db where each record is a certain task to be done. Each record includes the date the task should be completed. Some of the tasks are one time only but some are recurring. I have been having to create a new record for every task even the recurring ones, but I'd like to try to automate creation of the recurring tasks.
What I'm trying to...
|
by: helpmeplease213 |
last post by:
Hello,
I have a table that has five categories: Unit No, Date, P1, P2, P3.
I have a query that only extracts data from the table that is over 40 for P1, P2 or P3 but I only want the data with the latest date for that particular Unit No to come up. Any Ideas?
|
by: selvialagar |
last post by:
i have a date field in a table..i want to retrieve the data with latest date...is there any query for that.....
|
by: SunshineInTheRain |
last post by:
I have a log table.
I want to get record of latest date. Meaning if 1-May-2008 13:32:22 and 1-May-2008 2:22:21, it will retrieve 1-May-2008 13:32:22 as a result.
how the statement should be?
thaanks in advanced.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
