School project help please!

[djcmalvern]

I have to build an Access Database for a project. One requirement is to do this :

"the generation of a unique student ID, which combines letters from the student’s
name with a sequential number that is always one more than the highest currently


in use"

but the scenario presented says that 'programming will not be required for this project'!!!

Can anyone offer a method for doing the above using native Access commands and/or running queries? IE how to strip letters from the input student name and then get the previous sequential number used, and then concatenate the 2 bits into a new generated user name for adding to the register table?

Currently confused!

djcmalvern

[MFS]

You stated that 'programming will not be required for this project'!! but are you allowed???
What letters of the person's name are you wanting to 'strip'

[djcmalvern]

Hi MikSpeck,

I guess that a piece of code could be used but I was hoping to explore the idea without cutting code. However, all ideas would be welcome. The letters to strip from the name are open to choice, I was thinking the first letter of both the Forename and Surname.

To expand the detail for the routine:

Obtain the latest record in the Register table - extract the last username generated - separate the alpha characters that were used - record the numerical part and increment the number by one - concatenate the new number to the first letters of the new registered person Forename and Surname - insert new generated username into the new registrant tuple and update into Register Table.

The solution need not be this sequence but I hope this illustrates what needs to occur. All help welcome! Thanks.

[MFS]

Okay, with programming.
On the table that has the student names make sure you have a field for the Student's First name and a field for the student's last name. (In this example fields named, Student and Student LastName.)
Create three calculated text fields on the same table. In this case Field1 and Field2 and Field3.

Let's start with Field2, right click your mouse over Field2 go to Modify Expression and paste the following . . . Left([student],1)
Field3 go to Modify Expression and paste the following . . . Left([student lastname],1)
Now Field1 go to Modify Expression and paste the following . . . [field2] & [field3] & [id]
Add some students first and last names on the tables fields for testing.
Create a query and reference Field1.
Open Query, is this what you are looking for??

[djcmalvern]

Brilliant!! MikSpeck.

I did not know that these functions were available. This is exactly what I needed. Thank you.

Okay, with programming.
On the table that has the student names make sure you have a field for the Student's First name and a field for the student's last name. (In this example fields named, Student and Student LastName.)
Create three calculated text fields on the same table. In this case Field1 and Field2 and Field3.

Let's start with Field2, right click your mouse over Field2 go to Modify Expression and paste the following . . . Left([student],1)
Field3 go to Modify Expression and paste the following . . . Left([student lastname],1)
Now Field1 go to Modify Expression and paste the following . . . [field2] & [field3] & [id]
Add some students first and last names on the tables fields for testing.
Create a query and reference Field1.
Open Query, is this what you are looking for??

djcmalvern