X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Comparing String Length And Choosing Longer Strings, Access 2016    
post Sep 18 2019, 12:18 PM

Posts: 70
Joined: 21-September 18

I have a table that has a possibly multiple entries per customer.
One field has data that has the same information, however one entry per customer may have more on the listing than the other

I would like to 1) group by each person, and 2) choose the record entry that has the longer string length for the description information.

How would i do that?

Go to the top of the page
post Sep 18 2019, 12:37 PM

Access Wiki and Forums Moderator
Posts: 76,380
Joined: 19-June 07
From: SunnySandyEggo

Hi. Try using the Len() function to get the length of the string/data.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Sep 18 2019, 12:39 PM

Posts: 70
Joined: 21-September 18

ok so i know len() function....but my question is, is if the same person with say for example id 1234...has two records...1 record has order description with length of 10 and a second record with length of 12
I want the record with length of 12 returned.

Go to the top of the page
post Sep 18 2019, 12:52 PM

UtterAccess VIP
Posts: 11,515
Joined: 6-December 03
From: Telegraph Hill


I don't know what the performance will be like, but you can try using a subquery like:
  (SELECT TOP 1 o2.Description FROM tblOrders o2 WHERE o2.CustomerID = o1.CustomerID ORDER BY Len(o2.Description) DESC) AS Decsription
FROM tblOrders o1
GROUP BY o1.CustomerID;





David Marten
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    15th October 2019 - 04:32 PM