UtterAccess.com
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    
 
   
jczst21
post Sep 18 2019, 12:18 PM
Post#1



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
 
theDBguy
post Sep 18 2019, 12:37 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,425
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
 
jczst21
post Sep 18 2019, 12:39 PM
Post#3



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
 
cheekybuddha
post Sep 18 2019, 12:52 PM
Post#4


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


Hi,

I don't know what the performance will be like, but you can try using a subquery like:
CODE
SELECT
  o1.CustomerID,
  (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;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd October 2019 - 04:39 PM