Incremental Letter within a group

[fsict]

Hello All,

Long time browser but new member.. and I hope someone can help me.

Basically I have to create a database which when a new person joins a GroupNumber they get their own letter.

e.g.

A person joins group 1 and is assigned letter A. They then join group 2 and again are assigned a letter A within that group. A second person joins group 1 and are assigned a letter B. This would give the following in the table:

GroupNumber GroupLetter
1 A
2 A
1 B

I have tried the following, but with incrememtal numbers instead of letters, on a form as a BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord = True Then
Me.CounterField = Nz(DMax("CounterField", "MyTable", GroupNumber = Me.GroupNumber), 0) + 1
End If

End Sub

But the whilst the form does increase the counterfield number it does not take into account the me.GroupNumber which has been entered into the form.

Can anyone help?

Thanks

 

[jdraw]

Hmmm? Can you tell us more about the requirement?

What does a Group represent? How many Groups might you have?

In many data base situations, your Person would get an identifier (unique). Groups would have some unique identification, and you would have a junction/linking table to show which Person(s) belong to which Group(s).

Person----->PersonBelongsToGroups<----Group

 

[fsict]

Basically what it is, is that a GroupNumber will be an activity say the following:

Table-tennis
Cricket
Netball
etc

What I want is for when a new person joins group number 1 i.e. table-tennis to be given a different letter. And so when the data is stored in a table it might look like this:

GroupNumber GroupLetter
1 A
1 B
1 C
2 A
2 B
3 A
3 B
4 A

What I want is to create a form where I can assign people to a group and they are automatically assigned a unique letter in that group (there will be no more than 26 people in a group)

 

[jdraw]

And in your proposed set up, who is person 1 or 3? How would you contact them if necessary?

I think you have isolated 1 small piece of what you are dealing with. I recommend you build a bigger picture, if only for clarifying concepts.

Let me change the terminology slightly (to highlight the database aspect)

There are a number of people/persons in our "community". From discussions, we have found that many people share the same hobby(ies)/sports. As a project, I have decided to create a small database to record this information. To date our hobbies/sports include: table tennis, cricket, netball, ice hockey, field hockey, lacrosse and hurling.
The people include:
Porky Pig, Delta Dawn, John Dough, Cyan Arrah and Kody Akkbear.

Porky Pig could play Cricket and Lacrosse
Delta Dawn enjoys ice hockey
Cyan Arrah likes table tennis, ice hockey, field hockey
Kody Akkbear enjoys all the sports except netball.
(nobody is interested in netball
John Dough doesn't play any sport)

Here is a sample data model that reflects this description.

 

[arnelgp]

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.NewRecord Then _
        Me.CounterField = Chr(Asc(Nz(DMax("CounterField", "MyTable", "GroupNumber=" & Me.GroupNumber), Chr(64))) + 1)
End Sub

 

[fsict]

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.NewRecord Then _
        Me.CounterField = Chr(Asc(Nz(DMax("CounterField", "MyTable", "GroupNumber=" & Me.GroupNumber), Chr(64))) + 1)
End Sub

What a star you are!! Thank you so much... this was doing my head in!