Account ID - Generator

[sschrader1]

Is it possible to Query seperate tables, such as Students, and Courses and then create an Account Code by selecting the first 2 characters of the Course Name, the first 4 characters of the student's Last Name, the first 3 characters of the Student's First Name, and lastly the Course Date and creating an Account ID with that information ? Example (TN-SCHRHEA04032012)
Thank You;


Scott

[Robeen]

Yes - this is possible.
Are the Student's First Name and Last name in the same field or are they in different fields?
You could use a combination of the following functions [Len, Left, Right, Instr . . .] in your query to get the pieces of the various strings to concatenate them.
If you haven't used these functions in sql before, it can seem difficult, so perhaps get ONE part going first [Eg: the first two characters of the Course Name] and then build on that.

For instance:

SELECT TableName.ID, TableName.Preference, TableName.Weight, TableName.Description, Left([Preference],3) & "-" & Left([Description],4) AS Code
FROM ID_Preference_Weight;

This is a simple example. The part in red is where you would get different characters from different fields and concatenate them with the '&'.
In the example the first 3 characters of Preference are concatenated with a hyphen and the first 4 characters of Description.
You can also pull in fields from different tables and use them to create your concatenated AccountID.

Hope this helps.
Let us know if you need further help.

[June7]

You can but why bother? Do you want to save this calculated value to table? What if student changes name (women still do that sometimes when they marry/divorce).

[sschrader1]

Thank you for this solution. It works. Only question I have is . . . How do you make all the characters capalitized ?

Is it possible to Query seperate tables, such as Students, and Courses and then create an Account Code by selecting the first 2 characters of the Course Name, the first 4 characters of the student's Last Name, the first 3 characters of the Student's First Name, and lastly the Course Date and creating an Account ID with that information ? Example (TN-SCHRHEA04032012)
Thank You;
Scott

[Robeen]

Try the UCase function.

SELECT TableName.ID, TableName.Preference, TableName.Weight, TableName.Description, UCase(Left([Preference],3)) & "-" & UCase(Left([Description],4)) AS Code
FROM ID_Preference_Weight;

SQL not tested . . .