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
> Query That Only Shows Latest Revision, Access 2016    
 
   
rule1j
post Oct 16 2019, 08:33 AM
Post#1



Posts: 10
Joined: 21-January 15



Hi,

I have a query that I need only the latest revision. (I've worked on this for days.)

Data
1461772
1461772R1
1461772R2
1475428
1474298
1474298R1


What I need is:

1461772R2
1475428
1474298R1

Any help would be greatly appreciated.

Thank you,
Jewel
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 08:45 AM
Post#2


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


Just to be clear, before we attempt an answer, how do you know what qualifies as "the latest revision"? Are we looking at the suffixes?

Also what other fields are available? I would hope that you have a date or date and time field associated with revisions? That's basically what "latest" usually refers to.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Daniel_Stokley
post Oct 16 2019, 08:48 AM
Post#3



Posts: 329
Joined: 22-December 14
From: Grand Junction, CO, USA


Hello,

Not a lot of information there to go on so I had to make some assumptions. I assumed that your Revision Codes are always 7 digit characters followed by 0 or 2 more characters.

I created a table named tbl_RevisionCodes to store your data. Here is the SQL based on that table:

SQL
SELECT Left([RevisionCode],7) AS [RC Prefix], Max(Format([RevisionCode],"0000000 ")) AS [RC Formatted]
FROM tbl_RevisionCodes GROUP BY Left([RevisionCode],7);


Notice the use of the Format function. The format string is seven zeros followed by two spaces. That query returns the results you wanted.


Go to the top of the page
 
arnelgp
post Oct 16 2019, 09:29 AM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


this will return your Data even if the Length of the string is Variable:
CODE
SELECT Max(tbl_Revision.Data) AS Data
FROM tbl_Revision
GROUP BY Left([Data],InStr([Data] & "R","R")-1);

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
rule1j
post Oct 17 2019, 06:52 AM
Post#5



Posts: 10
Joined: 21-January 15



Latest revision with be the highest number after the R, but the R is only sometimes. It's considered a revision, but not all numbers have a revision. Hope this is clear and thank you so much.
Go to the top of the page
 
GroverParkGeorge
post Oct 17 2019, 07:20 AM
Post#6


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


I was really hoping to hear that there is actually a more reliable way to determine "latest" via a date and time field in the same table. You know, that way you don't have to resort to VBA or other calculations to parse out the elements of the code itself.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Oct 17 2019, 08:41 AM
Post#7



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


using Query only on my post #4, yield:
CODE
Data
1461772R2
1474298R1
1475428


--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Oct 17 2019, 09:12 AM
Post#8


UA Admin
Posts: 36,017
Joined: 20-June 02
From: Newcastle, WA


I know it works with the sample data provided.

Unfortunately, when we have to rely on parsing existing values to determine things like "first", "last", etc., we run the risk of breaking the function if, and when, data values change. It's more a case of "when" that happens, as a matter of fact.

That's why I lean towards solutions that do NOT rely on data values that can, and do, change. If there is a Date/Time field, it's less likely to break that way.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th November 2019 - 11:02 PM