Making the primary key as the client code from the client name (1 Viewer)

[Smiley 3636]

Hi I have a problem, an would appreciate help with the folowing.
I need to extract the first three letters from a clients name and add a unique index number to it, as the primary key. For example

ID: FLI0001-BIZ
Type: Company
Name: Flintstone Pty Ltd

Type can be Individual (IND), while Sole Pro, Partnership, Company, Trust, SMSF & Non Profits are (BIZ)
Table:
[PK] Client Type: Client Name
FLI0001-BIZ Company Flintstone Pty Ltd
FLI0002-IND Individual Flintstone, Fred
CON0001-BIZ Partnership Connolly AM & JE


Code: (that i don't seem to get to work)
Function clientidPK()
Dim InB
Dim dbs As Database
Dim rstProducts As Recordset
Dim rstCustomers As Recordset
Dim strSeek As String
Dim varBookmark As Variant
Dim a As Integer

Set dbs = CurrentDb
Set rstProducts = dbs.OpenRecordset("dbtClientList")

InB = Left([Name1], 3) & Format(10000, "#0000")

a = 0

With rstProducts
.Index = "Client"
strSeek = .NoMatch

Do While True

If strSeek <> "" Then
strSeek = .NoMatch
a = a + 1
ElseIf strSeek = "" Then
Exit Do
End If
bSeekMatch rstProducts, strSeek, InB, a
Loop

.Close
End With
dbs.Close
End Function

Sub bSeekMatch(rstTemp As Recordset, intSeek As String, InB, a)

Dim varBookmark As Variant
Dim strMewssage As String

With rstTemp
varBookmark = .Bookmark
.Seek "=", InB

If .NoMatch Then
.Bookmark = varBookmark
intSeek = ""
[Clientrid] = InB
Else
InB = Left([Name1], 3) & Format(10000, "#0000") + a
End If
End With

End Sub

Public Function Form()

End Function

 

[XPS35]

I have a problem

No, you created a problem.

Just make a meaningless key, and store the info (name, type) in the fields where it belongs and nowhere else. No redundancy, no problem.

 

[Smiley 3636]

No, you created a problem.

Just make a meaningless key, and store the info (name, type) in the fields where it belongs and nowhere else. No redundancy, no problem.

gee thanks

 

[CJ_London]

Personally I would keep your PK as an autonumber and store the other data in other fields - just concatenate them together when required for presentation purposes

Simpler and safer

 

[GPGeorge]

Most experienced Access developers have come to a very similar conclusion regarding the creation of Primary Keys from bits and pieces of other fields.

That conclusion can be summed up as something like:

"This method creates more problems than it solves."

Therefore, the most commonly offered suggestion is the one just posted in #4 as I was typing here.

I suspect that many of us, having seen questions about this idea over and over again in the last 20 or 30 years, can be a little bit too abrupt in responding to a new request for helping in making what we interpret as a mistake. It's not personal. It's just a reaction conditioned by long experience.

 

[Smiley 3636]

Most experienced Access developers have come to a very similar conclusion regarding the creation of Primary Keys from bits and pieces of other fields.

That conclusion can be summed up as something like:

"This method creates more problems than it solves."

Therefore, the most commonly offered suggestion is the one just posted in #4 as I was typing here.

I suspect that many of us, having seen questions about this idea over and over again in the last 20 or 30 years, can be a little bit too abrupt in responding to a new request for helping in making what we interpret as a mistake. It's not personal. It's just a reaction conditioned by long experience.

Thanks, thats fine I'm ok with that, and i understand the frustration

 

[Smiley 3636]

Given the advice i have received, i think i will go with my original plan. of the table set up were a contact can be a client, contact, or protetential client, based upon weather or not they are business or individual.
which have relation ships, eg spouse, employee, director, partner, etc and then group them into account name as presented
i need adjust when a client stops being director an and share holder or becomes one etc
is there a better way to structure this?

 

[Gasman]

You might want to make them separate records, if you need to track when IND and when they became BIZ?

 

[Smiley 3636]

You might want to make them separate records, if you need to track when IND and when they became BIZ?

thanks - orginally that was my thoughts to, as shown in the table format with the contact table being the link to their status and relationships. but think i made to complex

 

[XPS35]

Complexity is not the point.

but think i made to complex

Complexity isn't the issue. What matters is: does the model reflect the reality of your situation and your information needs?
If a contact can change status and there's a need to maintain the history of those status changes, then you need a specific model.
That's also a reason not to include the status in the key.

 

[Smiley 3636]

Complexity is not the point.

Complexity isn't the issue. What matters is: does the model reflect the reality of your situation and your information needs?
If a contact can change status and there's a need to maintain the history of those status changes, then you need a specific model.
That's also a reason not to include the status in the key.

thanks, although the traking change in status is one object, the other is having a table with null fields, or repeating information. example an individual can be employee of a company but i don't require their TFN, Visas etc.. just their name and contact details, as they are not client but a contact or a protential.
As for a client wheather or not they are business, i will need greater detail information.
if i had one table there woud be some or most fields would be reduntant. I think, especially taking the difference between a business and individual.
How ever I do appreciate your input and advice

 

[Ken Sheridan]

if i had one table there woud be some or most fields would be reduntant. I think, especially taking the difference between a business and individual.

You are correct on this point. You have a type hierarchy, in which individuals and busineses are sub-types of type contacts. A sub-type is characterised by sharing all attributes of its (super) type, but not those of other sub-types. The one change I'd suggest making would be to drop the surrogate keys of the businesses and individuals tables, making the foreign keys referencing the contacts table the primary keys of the two referencing tables. The image below is of a simple model for a hypothetical academic institution which illustrates this.

 

[RonPaii]

You may find that an individual may become a business IE purchase something for there home business. Consider storing required information for a contact in the contact table and everything else in a property table. This will allow you to add properties in the future without changing your tables. It also allows you to contact to be an individual, Vendor, Customer at the same time.