Looping through records and creating ID field

[rob4465]

Hi there - I'm doing a database that will need to link with a Paradox
database. To accomplish this I need to make a certain field in Access.

I basically have a table that contains a persons first name and surname.

I need to create another field called ID Make that takes the first six
characters of the first name, adds in a hyphen and then adds in the first
character of the surname.

I can do this easily with a query like:

Left([First Name],6) & "-" & Left([Surname],1)

For example the name William Gates would be turned into:

Willia-G

Now here comes the tricky bit, if there is another William Gates I would get
another Willia-G reference but at this point I need it to add a '1' at the
end so I would get:

Willia-G1

If there was a third I would need Willia-G2.... and so on.

What I could do with is some kind of script that looks at each record in the
table and then makes the appropriate ID.

I'd appreciate very much any help anyone can give me.

At the moment I just have a test table called tbl_test

Field: First Name
Field: Surname
Field: ID Make

Thanks very much -

 

[Alex Dybenko]

Hi,
you can make a function which builds your IDMake like this:

Left([First Name],6) & "-" & Left([Surname],1) & dcount("*", "tbl_test",
"[ID Make] Like '" & Left([First Name],6) & "-" & Left([Surname],1) & "*')+1

then open recordset on tbl_test and loop through it updating field IDmake
using this function

it will generate ids as Willia-G0, Willia-G1, etc
you can test for 0 and remove it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[XPS350]

Hi there - I'm doing a database that will need to link with a Paradox
database.  To accomplish this I need to make a certain field in Access.

I basically have a table that contains a persons first name and surname.

I need to create another field called ID Make that takes the first six
characters of the first name, adds in a hyphen and then adds in the first
character of the surname.

I can do this easily with a query like:

Left([First Name],6) & "-" & Left([Surname],1)

For example the name William Gates would be turned into:

Willia-G

Now here comes the tricky bit, if there is another William Gates I would get
another Willia-G reference but at this point I need it to add a '1' at the
end so I would get:

Willia-G1

If there was a third I would need Willia-G2.... and so on.

What I could do with is some kind of script that looks at each record in the
table and then makes the appropriate ID.

I'd appreciate very much any help anyone can give me.

At the moment I just have a test table called tbl_test

Field: First Name
Field: Surname
Field: ID Make

Thanks very much -

You could build something like this:

Dim rs As Recordset
Dim IDcount As Integer
Dim currFirstName As String
Dim currSurname As String
Dim prevFirstName As String
Dim prevSurname As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_test ORDER BY
[First Name], Surname")

prevFirstName = "ZZZZZZ"
prevSurname = "Z"

With rs
While Not .EOF
currFirstName = Left(![First Name], 6)
currSurname = Left(!surname, 1)

If (currFirstName = prevFirstName) And (currSurname =
prevSurname) Then
IDcount = IDcount + 1
.Edit
![ID Make] = currFirstName & "-" & currSurname & IDcount
.Update
Else
IDcount = 0
.Edit
![ID Make] = currFirstName & "-" & currSurname
.Update
End If

prevFirstName = currFirstName
prevSurname = currSurname
.MoveNext
Wend
.Close
End With

MsgBox "Ready!", vbInformation



Groeten,

Peter
http://access.xps350.com

 

[rob4465 via AccessMonster.com]

Hi Peter,

That is excellent, thank you very much.

To keep the ID Make field unique could the dcount function be used so there
aren't any duplications of this field.

For example, I've tried your example on about 10,000 records and it very
quickly creates entries in the ID Make field which is fantastic.

However, if there are two names like:

John Smith and John Smithson (for example) you get:

John Smith = John-S
John Smithson = John-S

To make the ID Make field unique is there a way to count previous occurances
of this and if there are any add '1' to this entry so we'd have John-S1 and
John-S2 etc.

I may well be able to sort this myself but if you have a solution I'd be very
grateful.
Thanks again for your help !
Rob

Hi there - I'm doing a database that will need to link with a Paradox
database.  To accomplish this I need to make a certain field in Access.

[quoted text clipped - 33 lines]

Thanks very much -

You could build something like this:

Dim rs As Recordset
Dim IDcount As Integer
Dim currFirstName As String
Dim currSurname As String
Dim prevFirstName As String
Dim prevSurname As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_test ORDER BY
[First Name], Surname")

prevFirstName = "ZZZZZZ"
prevSurname = "Z"

With rs
While Not .EOF
currFirstName = Left(![First Name], 6)
currSurname = Left(!surname, 1)

If (currFirstName = prevFirstName) And (currSurname =
prevSurname) Then
IDcount = IDcount + 1
.Edit
![ID Make] = currFirstName & "-" & currSurname & IDcount
.Update
Else
IDcount = 0
.Edit
![ID Make] = currFirstName & "-" & currSurname
.Update
End If

prevFirstName = currFirstName
prevSurname = currSurname
.MoveNext
Wend
.Close
End With

MsgBox "Ready!", vbInformation

Groeten,

Peter
http://access.xps350.com

 

[XPS350]

Hi Peter,

That is excellent, thank you very much.

To keep the ID Make field unique could the dcount function be used so there
aren't any duplications of this field.

For example, I've tried your example on about 10,000 records and it very
quickly creates entries in the ID Make field which is fantastic.

However, if there are two names like:

John Smith and John Smithson (for example) you get:

John Smith = John-S
John Smithson = John-S

To make the ID Make field unique is there a way to count previous occurances
of this and if there are any add '1' to this entry so we'd have John-S1 and
John-S2 etc.

I may well be able to sort this myself but if you have a solution I'd be very
grateful.
Thanks again for your help !
Rob

Hi there - I'm doing a database that will need to link with a Paradox
database.  To accomplish this I need to make a certain field in Access.

[quoted text clipped - 33 lines]

Thanks very much -

You could build something like this:

Dim rs As Recordset
Dim IDcount As Integer
Dim currFirstName As String
Dim currSurname As String
Dim prevFirstName As String
Dim prevSurname As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_test ORDER BY
[First Name], Surname")

prevFirstName = "ZZZZZZ"
prevSurname = "Z"

With rs
   While Not .EOF
       currFirstName = Left(![First Name], 6)
       currSurname = Left(!surname, 1)

       If (currFirstName = prevFirstName) And (currSurname =
prevSurname) Then
           IDcount = IDcount + 1
           .Edit
           ![ID Make] = currFirstName & "-" & currSurname& IDcount
           .Update
       Else
           IDcount = 0
           .Edit
           ![ID Make] = currFirstName & "-" & currSurname
           .Update
       End If

       prevFirstName = currFirstName
       prevSurname = currSurname
       .MoveNext
   Wend
   .Close
End With

MsgBox "Ready!", vbInformation

Peter
http://access.xps350.com

Hi Rob,

I am not sure what you mean. Running the code gives me:
John-S as ID for John Smith
John-S1 as ID for John Smithson

That is diffrent from what you suggest as result.
You also suggest that the IDs should be John-S1 and John-S2 instead of
John-S and John-S1.

Groeten,

Peter
http://access.xps350.com

 

[rob4465 via AccessMonster.com]

Sorry Peter,

I wasn't very clear here - I have it all working thanks to your code.

What I wanted was for the ID Make field to be unique.

With names like below

Andrea Reeves
Andrea Russo

This gives you Andrea-R for both.

I think however if I amend your code to look at more than the 1st character
of the surname this will get around the problem.

Thanks again for your help - it's been of great assistance.

Rob -

Hi Peter,

[quoted text clipped - 77 lines]
Hi Rob,

I am not sure what you mean. Running the code gives me:
John-S as ID for John Smith
John-S1 as ID for John Smithson

That is diffrent from what you suggest as result.
You also suggest that the IDs should be John-S1 and John-S2 instead of
John-S and John-S1.

Groeten,

Peter
http://access.xps350.com