How to generate a unique number on the fly?

[Ed Dror]

Hi there,

I have a query with two columns

VendorID and FileName
Look like this

9287 C:\myfilename.txt
9287 C:\myfilename.xls
9287 C:\myfilenamepdf
9287 C:\myfilename2.txt
9287 C:\myfilename.jpg
9283 C:\myfilename.jpg
9283 C:\myfilename.jpg

As you can see some vendors has 5 files and some 2 or 3
All of the VendorID has 4 digit only

Because VendorID is duplicate I need to create a unique ID
So I'm thinking to add a letter at the end of the vendorId
Looks like this

9287A
9287B
9287C
9287D
9287E
9283A
9283B

How do I add a lettrer in a query or with VBA function that assign the exact
letter based on count VendorID

Thanks,
Ed Dror
Andrew Lauren Co.

 

[BruceM]

You haven't described your database's structure or relationships, but I
expect vendor informtion should be in its own table, and filename
information in a separate related table, linked through the VendorID. That
is, VendorID is the primary key in the vendor table, and the foreign key in
the filename table. The filename table could have its own primary key, or
it could have a primary key consisting of VendorID and FileName.
You say you "need to create a unique ID". Changing the VendorID will cause
any relationships to stop working, so you need to find another way to make
the record unique. Is there a reason for adding a letter suffix
specifically, or are you just trying to assure the record is unique?

 

[Charles Wang [MSFT]]

Hi Ed,
I am not sure if I totally understand your meaning. Did you mean that you
just wanted to find a query to produce unique VendorID in the query result?

I saw that you wanted to use a letter A, B,..., etc to append to VendorID
value, however what is the postfix letter if one VendorID has more than 26
files matched?

Regarding your requirement, I think that using number as postfix instead of
letter would be better. You can write a function to do this. For example:
Public Function VendorUniqueID(currentVendorID As Integer) As String
Static t_vendorId As Integer
Static t_nCount As Long
Dim strUniqueId As String

If IsEmpty(t_nCount) Or IsEmpty(t_vendorId) Then
t_vendorId = 0
t_nCount = 0
End If

If t_vendorId <> 0 And t_vendorId = currentVendorID Then
t_nCount = t_nCount + 1
Else
t_vendorId = currentVendorID
t_nCount = 1
End If

strUniqueId = CStr(t_vendorId) & "." & CStr(t_nCount)

VendorUniqueID = strUniqueId
End Function

Then you can save the following query as "qryVendorFile":
SELECT VendorUniqueID(VendorID) AS NewVendorID, FileName FROM VendorFile;

Then you can query the records as following:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryVendorFile")

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Ed Dror]

Bruce,

I don't need relationship or keep the original VendorID
I found a function from Excel look like this
=A1&CHAR(64+COUNTIF($A$1:A1,A1))
And I'm wondering if I can convert this to an Access format that I can use
with me query

Thanks,
Ed Dror

 

[Ed Dror]

Charls,

I found a formula in Excel look like this
=A1&CHAR(64+COUNTIF($A$1:A1,A1))
And I'm wondering if we can convert this to an Access
or
Option Explicit
Sub testme()
Dim myRng As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
With wks
.Columns(2).Insert
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

With myRng.Offset(0, 1)
.NumberFormat = "General"
.Formula = "=A1&CHAR(64+COUNTIF($A$1:A1,A1))"
.Value = .Value
End With

' .Columns(1).Delete

End With

How to convert this to Access VBA

Or how to call this function from access

Thanks,
Ed Dror

Hi Ed,
I am not sure if I totally understand your meaning. Did you mean that you
just wanted to find a query to produce unique VendorID in the query
result?

I saw that you wanted to use a letter A, B,..., etc to append to VendorID
value, however what is the postfix letter if one VendorID has more than 26
files matched?

Regarding your requirement, I think that using number as postfix instead
of
letter would be better. You can write a function to do this. For example:
Public Function VendorUniqueID(currentVendorID As Integer) As String
Static t_vendorId As Integer
Static t_nCount As Long
Dim strUniqueId As String

If IsEmpty(t_nCount) Or IsEmpty(t_vendorId) Then
t_vendorId = 0
t_nCount = 0
End If

If t_vendorId <> 0 And t_vendorId = currentVendorID Then
t_nCount = t_nCount + 1
Else
t_vendorId = currentVendorID
t_nCount = 1
End If

strUniqueId = CStr(t_vendorId) & "." & CStr(t_nCount)

VendorUniqueID = strUniqueId
End Function

Then you can save the following query as "qryVendorFile":
SELECT VendorUniqueID(VendorID) AS NewVendorID, FileName FROM VendorFile;

Then you can query the records as following:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryVendorFile")

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================

 

[BruceM]

You could make a ranking query. I probably don't know enough about such
things to advise you, but it may look something like this:

SELECT T1.VendorID & Chr(Count(*)+64) AS ListOrder, T1.FileName

FROM YourTable AS T1 INNER JOIN YourTable AS T2 ON T1.VendorID = T2.VendorID

WHERE (((T2.VendorID)<=[T1].[VendorID]) AND
((T2.FileName)<=[T1].[FileName])) OR (((T2.FileName)<=[T1].[FileName]) AND
((T1.FileName)<>[T2].[FileName]))

GROUP BY T1.FileName, T1.VendorID

ORDER BY T1.VendorID, Count(*);



This assumes you want to sort by VendorID, then by FileName in alphabetical
order. These are the only two fields you mentioned, so I used them in the
SQL.



I have to say I can't even imagine what purpose it serves to use the
VendorID in a flat database (i.e. no relationships)

 

[Ed Dror]

Bruce,

Thank you very much for your help, This solution works fine for me
I'm using Access as a tool (staging) for migration from one database to
another
I needed to perform a lot of text manipulation and I found that access can
do this job except the question that I asked
Which I did not know how to do.

Ed Dror

You could make a ranking query. I probably don't know enough about such
things to advise you, but it may look something like this:

SELECT T1.VendorID & Chr(Count(*)+64) AS ListOrder, T1.FileName

FROM YourTable AS T1 INNER JOIN YourTable AS T2 ON T1.VendorID =
T2.VendorID

WHERE (((T2.VendorID)<=[T1].[VendorID]) AND
((T2.FileName)<=[T1].[FileName])) OR (((T2.FileName)<=[T1].[FileName]) AND
((T1.FileName)<>[T2].[FileName]))

GROUP BY T1.FileName, T1.VendorID

ORDER BY T1.VendorID, Count(*);



This assumes you want to sort by VendorID, then by FileName in
alphabetical order. These are the only two fields you mentioned, so I
used them in the SQL.



I have to say I can't even imagine what purpose it serves to use the
VendorID in a flat database (i.e. no relationships)

Bruce,

I don't need relationship or keep the original VendorID
I found a function from Excel look like this
=A1&CHAR(64+COUNTIF($A$1:A1,A1))
And I'm wondering if I can convert this to an Access format that I can
use with me query

Thanks,
Ed Dror

 

[Charles Wang [MSFT]]

Hi Ed,
I tried similar SQL statement like Bruce's before. However I recommended
you a way of using function since the SQL statement will cause record
missing for those duplicated records. In your demo, you showed two
duplicated records for VendorID=9283. The result from the query will
produce only one record for 9283.

I am not sure if you have noticed this or if this satisfied your
requirements. Could you please clarify this?

In my former response, I supposed that you would not want to change your
underlying table and you also need to keep the duplicated records. In this
case, it is hard to use a SQL statement to work around this issue. If you
still want to use Bruce's suggestion and can accept table definition
changes, I recommend that you add a column with AutoNumber type to your
table.



Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[BruceM]

I have to say I did not notice that, and my suggestion did not take it into
account. Now that I see it I expect it was because of copying and pasting
for the purposes of the question. In any case a duplicated file name
(including path) is not possible unless it is on another computer, so I
expect it is not a problem.

 

[BruceM]

Glad to help. This is rather new territory for me, and I have to say I
adpated an answer I received here not that long ago. A lot of my puzzlement
was removed when I understood at last that your apparently unconventional
(and probably non-relational) approach was for the purpose you described in
your most recent posting.
Good luck with the project.

Bruce,

Thank you very much for your help, This solution works fine for me
I'm using Access as a tool (staging) for migration from one database to
another
I needed to perform a lot of text manipulation and I found that access can
do this job except the question that I asked
Which I did not know how to do.

Ed Dror

You could make a ranking query. I probably don't know enough about such
things to advise you, but it may look something like this:

SELECT T1.VendorID & Chr(Count(*)+64) AS ListOrder, T1.FileName

FROM YourTable AS T1 INNER JOIN YourTable AS T2 ON T1.VendorID =
T2.VendorID

WHERE (((T2.VendorID)<=[T1].[VendorID]) AND
((T2.FileName)<=[T1].[FileName])) OR (((T2.FileName)<=[T1].[FileName])
AND ((T1.FileName)<>[T2].[FileName]))

GROUP BY T1.FileName, T1.VendorID

ORDER BY T1.VendorID, Count(*);



This assumes you want to sort by VendorID, then by FileName in
alphabetical order. These are the only two fields you mentioned, so I
used them in the SQL.



I have to say I can't even imagine what purpose it serves to use the
VendorID in a flat database (i.e. no relationships)

Bruce,

I don't need relationship or keep the original VendorID
I found a function from Excel look like this
=A1&CHAR(64+COUNTIF($A$1:A1,A1))
And I'm wondering if I can convert this to an Access format that I can
use with me query

Thanks,
Ed Dror


You haven't described your database's structure or relationships, but I
expect vendor informtion should be in its own table, and filename
information in a separate related table, linked through the VendorID.
That is, VendorID is the primary key in the vendor table, and the
foreign key in the filename table. The filename table could have its
own primary key, or it could have a primary key consisting of VendorID
and FileName.
You say you "need to create a unique ID". Changing the VendorID will
cause any relationships to stop working, so you need to find another
way to make the record unique. Is there a reason for adding a letter
suffix specifically, or are you just trying to assure the record is
unique?

Hi there,

I have a query with two columns

VendorID and FileName
Look like this

9287 C:\myfilename.txt
9287 C:\myfilename.xls
9287 C:\myfilenamepdf
9287 C:\myfilename2.txt
9287 C:\myfilename.jpg
9283 C:\myfilename.jpg
9283 C:\myfilename.jpg

As you can see some vendors has 5 files and some 2 or 3
All of the VendorID has 4 digit only

Because VendorID is duplicate I need to create a unique ID
So I'm thinking to add a letter at the end of the vendorId
Looks like this

9287A
9287B
9287C
9287D
9287E
9283A
9283B

How do I add a lettrer in a query or with VBA function that assign the
exact letter based on count VendorID

Thanks,
Ed Dror
Andrew Lauren Co.

 

[Charles Wang [MSFT]]

Hi Bruce,
I do not know the usage of the table, so I cannot assume that the
duplicated records here are not possible. It is often to see some tables
for tracking having duplicated records, but in most cases, those tracking
tables having an identity/autonumber column. If duplicated records are not
possible for Ed, it does not matter here, however the query still need to
consider if there are over 26 files matched to one VendorID, what are the
postfix letters should be after 'Z' generated? I hope that Ed can post back
and clarify if the two points are problems for him.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[BruceM]

There was a lot I didn't know about the database because the information
given was rather sketchy. I don't know if there is an autonumber or other
PK column. I based my reply on the limited information available, and
acknowledged as much. However, it is reasonable enough to assume a listing
of files that lists the same path and file name twice is either a mistake or
refers to different computers. If the former, it can be avoided by a unique
index on the combination of VendorID and FileName, or by other data
validation procedures. If the latter, the information is of questionable
value at best if it does not list the computer.
As for having more than 26 items on the list, I have a way of continuing
with AA, AB, etc. through AZ, but again the OP provided little information.
Rather than going into a lot of details for a situation that may not arise I
went with a relatively simple approach. If the OP is still watching the
thread he can post again. If not, he will have to address that situation
when it arises.

 

[Charles Wang [MSFT]]

Hi Bruce,
Yes, I totally agree with you. If Ed would post back, we may know more of
his scenario and requirements, and then we can provide more accurate
answer. Glad to talk with you!

Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================