picking latest revision

  • Thread starter Thread starter tracktraining
  • Start date Start date
T

tracktraining

Hi Everyone,

How do I write a code that will allow me to pick the latest revision. For
example, I have a document number 001-002 A, 001-002 B, and 001-002 AB. I
want it to return to me 001-002 AB as the latest one (i.e. not 001-002 B).
Please teach.

Thanks.
 
Define "latest" ... no, really!

You may understand what you mean by that, but I certainly don't, and I'm
pretty sure Access would have even more trouble "figuring it out".

You will have to tell Access specifically how to determine the "latest".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Let's say I have 4 documents all with the same part number, but different
revision.

001-002 A
001-002 B
001-002 C
001-002 AB

I want to bring back 001-002 AB. How do I write that? If i write MAX
(revision) then it will bring me back 001-002 B and that is not the latest
revision (001-002 AB is).

Thanks.
 
What is it about the "AB" version that makes it "the latest" in your mind?
Is it because it is longer than the rest?

I'd probably call the "C" record the latest. If I were sorting those, I'd
probably use:

002 A
002 AB
002 B
002 C

Do the records have anything (say, a date/time field) that would help you
pick "the latest"?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Let's say I have 4 documents all with the same part number, but different
revision.

001-002 A
001-002 B
001-002 C
001-002 AB

I want to bring back 001-002 AB.
Click to expand...

If you're assuming that Access keeps track of - or even cares! - what the
order entry was, your assumption IS WRONG.

A table has no order. It's an unordered "bag" of data. You cannot count on the
records even staying in the same sequential order (say if you Compact).

If you want to keep track of the order of records, you must include some field
in the table to define that order (such as a Date/Time field with a default
value of Now() to timestamp each record).
 
I understand that Access doesn't keep track of the order. I just want to know
if code would I have to write in order to retrive back such data.

For example: I have 2 column (one for part number and one for revision).

001-001 B
001-001 A
001-001 AB
001-001 C

What code should I write to get back 001-001 AB? Or it is possible to do
such thing in Access?
 
I understand that Access doesn't keep track of the order. I just want to know
if code would I have to write in order to retrive back such data.

For example: I have 2 column (one for part number and one for revision).

001-001 B
001-001 A
001-001 AB
001-001 C

What code should I write to get back 001-001 AB? Or it is possible to do
such thing in Access?
Click to expand...

I guess I don't understand the question, or the mindset behind the question.
What do you mean by "get back"? If the data is in a table, you can write a
Query to find it, given criteria based on the content of the fields in the
table.
 
Let me try to explain:

Let's say I have a new employee who needs to read document 001-001. But I
don't know what the latest revision is. So I need to query for it. My table
have the following documents:

Part Number Revision
001-001 A
001-001 B
001-001 C
001-001 D
001-001 E ..... goes up to Z then starts over with AB
001-001 AB
001-001 AC

If I write MAX(Revision), then I will get 001-001 Z since Access assume that
Z is the latest one since it is the last letter in the alphabet. But AC is
the max (the latest). I think Access sort it by A, AB, AC, B, C.... and so
on.

I want to know how would I write the code/set parameters so that AC is the
latest revision and not Z. OR is that not possible?
 
I understand that Access doesn't keep track of the order. I just want to know
if code would I have to write in order to retrive back such data.

For example: I have 2 column (one for part number and one for revision).

001-001 B
001-001 A
001-001 AB
001-001 C

What code should I write to get back 001-001 AB? Or it is possible to do
such thing in Access?
Click to expand...

I guess my question should be:

Given these four records, with the data that you are displaying, how can you
ascertain that AB is the desired record? What is there about it which makes it
the "latest"?
 
Part Number Revision
001-001 A
001-001 B
001-001 C
001-001 D
001-001 E ..... goes up to Z then starts over with AB
001-001 AB
001-001 AC

If I write MAX(Revision), then I will get 001-001 Z since Access assume that
Z is the latest one since it is the last letter in the alphabet. But AC is
the max (the latest). I think Access sort it by A, AB, AC, B, C.... and so
on.

I want to know how would I write the code/set parameters so that AC is the
latest revision and not Z. OR is that not possible?
Click to expand...

Only with a little help. Since your revision numbers are neither in numeric
nor alphabetic order, but rather use this idiosyncratic composite value (what
happened to AA for example!?), you'll need either a function or (perhaps
better) an auxiliary table.

If you had a table with all the possible values from A through ZZ (or more,
if you need to go on to DKZW...) along with a number field indicating that
value's sort order, you could Join your Revision field to this table by the
text value to retrieve the numeric sequence.

Or you could use a VBA function to return a sequence number; something like
the following

Public Function SeqNo(strRev As String) As Integer
strRev = UCase(strRev) ' make sure it's upper case
Select Case Len(strRev)
Case 0
SeqNo = 0
Case 1
SeqNo = Asc(strRev) - 64 ' Asc("A") is 65
Case 2
SeqNo = 26 * (Asc(Left(strRev, 1)) - 64) + Asc(Mid(strRev, 2, 1)) - 64
Case Else
MsgBox "Erroneous input", vbOKOnly
SeqNo = 0
End Select
End Function

Examples:

?seqno("a")
1
?seqno("z")
26
?seqno("aa")
27
?seqno("az")
52
?seqno("ba")
53
?seqno("zz")
702

Note that this will call a VBA function for every record in your query, and
not take advantage of any indexing.
 
Since, as you are aware, Access doesn't keep track of the order, you have to
give Access a way to keep track of the order. When you asked a question
about "last", the responses you got asked you to define how you would
determine "last".

Instead of trying to figure out a function that can tell that "AB" comes
after "Z", why not just use a date/time field to record the date/time the
record was saved? That way, you can use a query to find the maximum
date/time value (i.e., the "latest")...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top