Group on several fields. Require 'exact' grouping on text field

[Daj]

Hi and Thank You for looking at this.

I need to select the latest updated record by RO_FAC_LAST_UPDATED where all other fields are equal.

The issue I have is that data entry in the text field [FAC Data ME].RO_FAC_DESCRIPTION may contain the same text but different capitalized letters, and these must be considered as different records.

With the code below, "Trade" and "trade" in [FAC Data ME].RO_FAC_DESCRIPTION are considered the same record. I need the query to return 2 different records.

I know I should use StrComp but despite many attempts, I had no success.



Is this possible?

SELECT [FAC Data ME].RO_FAC_RO_ID, [FAC Data ME].RO_FAC_DESCRIPTION, [FAC Data ME].RO_FAC_LGD_MODEL, [FAC Data ME].RO_FAC_BU_OWNER, Max([FAC Data ME].RO_FAC_LAST_UPDATED) AS MaxOfRO_FAC_LAST_UPDATED
FROM [FAC Data ME]
GROUP BY [FAC Data ME].RO_FAC_RO_ID, [FAC Data ME].RO_FAC_DESCRIPTION, [FAC Data ME].RO_FAC_LGD_MODEL, [FAC Data ME].RO_FAC_BU_OWNER;

[AccessToGo]

Hi Daj, How about summing the ASCII values in the string which will be different depending on case...
Create a global function in a module (create a new module if none in the project) and the code would be something like...

Function SumAsciiValues(MyString As Variant) As Long 'variant allows NULL values in without error
Dim x As Long
SumAsciiValues = 0 'initialise
For x = 1 To Len(Nz(MyString)) 'no loop if null string so returns 0
SumAsciiValues = SumAsciiValues + Asc(Mid(MyString, x, 1))
Next x
End Function

Then in your query add to the SELECT clause ...
SumAsciiValues([FAC Data ME].RO_FAC_DESCRIPTION) as MySum
and then add MySum to the GROUP BY clause.

I think that will do it.
Regards
Chris

[Daj]

Thank you for the idea, which is a good one..
Ill work on making this work and post back as soon as I can.

thank you again

[Daj]

AccesGoTo

I think this is working.. I am a novice in VBA so if you could review and point out errors, let me know..

Module / Function
Option Compare Database
Option Explicit


Public Function SumAsciiValues(RO_FAC_DESCRIPTION) As Long 'variant allows NULL values in without error
Dim x As Long
SumAsciiValues = 0 'initialise
For x = 1 To Len(Nz(RO_FAC_DESCRIPTION)) 'no loop if null string so returns 0
SumAsciiValues = SumAsciiValues + Asc(Mid(RO_FAC_DESCRIPTION, x, 1))
Next x
End Function

Query

SELECT [FAC Data ME].RO_FAC_RO_ID, [FAC Data ME].RO_FAC_DESCRIPTION, SumAsciiValues([FAC Data ME].RO_FAC_DESCRIPTION) AS SUMASCII, [FAC Data ME].RO_FAC_LGD_MODEL, [FAC Data ME].RO_FAC_BU_OWNER, Max([FAC Data ME].RO_FAC_LAST_UPDATED) AS MaxOfRO_FAC_LAST_UPDATED
FROM [FAC Data ME]
GROUP BY [FAC Data ME].RO_FAC_RO_ID, [FAC Data ME].RO_FAC_DESCRIPTION, SumAsciiValues([FAC Data ME].RO_FAC_DESCRIPTION), [FAC Data ME].RO_FAC_LGD_MODEL, [FAC Data ME].RO_FAC_BU_OWNER;

[orange]

See if Option Compare Binary is relevant to your situation.

[ssanfu]

How about

Option Compare Database
Option Explicit

Public Function SumAsciiValues(RO_FAC_DESCRIPTION) As Long    'variant allows NULL values in without error
    Dim x As Long
    Dim y As Long

    SumAsciiValues = 0    'initialise
    y = Len(Nz(RO_FAC_DESCRIPTION, 0))   '<<-- your version is missing the ValueIfNull (the zero)

    If y > 0 Then
        For x = 1 To y    'no loop if null string so returns 0
            SumAsciiValues = SumAsciiValues + Asc(Mid(RO_FAC_DESCRIPTION, x, 1))
        Next x
    End If
End Function

[Daj]

ssanfu: thank you but the field cannot be null for a record.. but good point.

Orange: not relevant I believe but thanks.

[orange]

You said you had an issue using strComp with binary compare.

Here's a sample

Sub testcompB()

'"Trade" and "trade"
    Dim a As String: a = "Trade"
    Dim b As String: b = "trade"
    If StrComp(a, b, vbBinaryCompare) = 0 Then
        Debug.Print "a(" & a & ")  and b(" & b & ") are same value and case"
    Else
        Debug.Print "a(" & a & ")  and b(" & b & ") are different either value or case"
    End If
End Sub

Sample outputs with changes to a, b.

a(Trade)  and b(trade) are different either value or case
a(Trade)  and b(Trade) are same value and case
a(Black)  and b(trade) are different either value or case
a(traDe)  and b(trade) are different either value or case

A question re your processes:
If field values are manually inputted, how do you check that variations such as Trade/trade are not simply keying/typos?

[ssanfu]

I was just reviewing your code.

ssanfu: thank you but the field cannot be null for a record.. but good point.

You have a comment about NULLS

Public Function SumAsciiValues(RO_FAC_DESCRIPTION) As Long 'variant allows NULL values in without error

If the data passed to the function cannot be NULL, then the argument type should be string.

Public Function SumAsciiValues(RO_FAC_DESCRIPTION As String) As Long

And you wouldn't need the NZ function - but should still check for a ZLS....... just sayin......


Good luck with your project....

[orange]

Steve, Daj

When you sum ASC values of chars in a string, it seems you could get same sum value yet have different strings.
I tried using your function, with some sample data.

Sub testsumasc()
    Dim valA As String: valA = "Trade"
    Dim valB As String: valB = "tradE"
    Dim SvalA As Long
    Dim SvalB As Long
    SvalA = SumAsciiValues(valA)
    SvalB = SumAsciiValues(valB)
    Debug.Print SvalA; SvalB
    If SvalA = SvalB Then
        Debug.Print SvalA; SvalB & " " & valA; " and " & valB & "   have matching sums "
    Else
        Debug.Print SvalA; SvalB & " " & valA; " and " & valB & " have **NON matching sums** "
    End If
End Sub

which produced the following:

496 496
496 496 Trade and tradE have matching sums

Perhaps I've misunderstood the purpose of the function, but I have seen equivalent sums from different strings in the past.

Just curious. So I added some displays in the function and tried with another pair of similar values.
tRade and tradE
currentsum 116 Char (t) value[116]
currentsum 198 Char (R) value[82]
currentsum 295 Char (a) value[97]
currentsum 395 Char (d) value[100]
currentsum 496 Char (e) value[101]

currentsum 116 Char (t) value[116]
currentsum 230 Char (r) value[114]
currentsum 327 Char (a) value[97]
currentsum 427 Char (d) value[100]
currentsum 496 Char (E) value[69]

496 496
496 496 tRade and tradE have matching sums

[ssanfu]

@ orange,
Interesting...

I did some testing also. As long as a word is only different by a capital letter (Steve and steVe or Trade or tRade) the sum will be the same.
The difference between an upper case letter and a lower case letter is 32. So as long as each word has 1 upper case letter the sum of the ASCII values will be the same.


I think the binary compare is the way to go unless code is written to compare the ASCII values of each of the letters of the words to determine where the difference is.

[orange]

Steve,

Yes I think you are correct. I'm not sure of the importance of this to the OP. Seems to me there may be some design or validation options, but the real requirement has to be presented clearly. I'm not sure why someone would pick Access if case sensitivity was a critical aspect of the application. Sure it can be done (binary compare), but are there options????