An important question is why do you want to generate your serial numbers in this fashion? What problem does that solve for you?
And how will you use it? If this field is to uniquely identify each customer record and will be used as a match field in relationships, it's not an optimal design for your database, A simple auto-entered serial number where all customers get an ID from the same series of values without regard to the city they are from is a better option for that function in your database.
That said, it IS possible to generate such a series of serial number values with a self join relationship that matches records by the value in City such that the relationship matches to all other records in the same table with the same city. Then Max ( Table 2::SerialNumb ) + 1 will return the value to be assigned to the next new customer from the specified city. Care must be taken if two or more users might be generating these values for the same city at the same time or you can get to client records with the same city and serial number.
first thanks for your replay
The problem is etch city have their own Serial # for there new customer ,so accounting can identify these customer by Their ID
the current way is the city accounted received the new customer request and based on his City excel sheet he enter the next serial # in FM
so we would like to update our software to achieve that throw FM without the need to enter that Manuel
all salesman sometime enter new record in same time
we have additional 1 main Serial # for all the City record to show the current Customer Count and its auto from the FM ( if it can help)
if there a way to achieve your idea if 2 salesman enter record in same time ?
and thanks again for your help
Yes but it's much simpler to simply include an identifier column for the city separate from the client's ID so that you can identify the client's city by their city ID (can be a name, but since city names are not unique, a city ID number is a better option). This add simplicity avoids a number of implementation issues needed to ensure that the client ID is always unique.
Dear Phil ,
thanks for the replay and sorry for the late answer ( changing the internet provider toke much time than expected )
how can i implement your idea in the solution
i have table for Customer Code and it include the field
SN , City Code , City Name
which the the New Customer Table will request the code from
Can you guide me throw the necessary setup or point me to where to look
thanks in advance again
Ideally, especially to use as a match field in relationships, your Customer code field should just be an auto-entered serial number with no added auto-enter details. If you have multiple users collecting this data on multiple copies of your database that you then merge into a single table of data, I suggest using a text field with Get ( UUID ) as the auto enter calculation and that you investigate one of the third party Sync tools available such as those offered by SeedCode and 360Works.
I would then recommend that you set up a related table with one record for each city where you record at least the name of the city and define a similar ID field for the city ID there though this table is a good place to record any other data you may need specific to a particular city. You'll also need to consider whether you need to include additional design details to handle situations where records for different cities with the same name might be entered into such a table.
You'd then link your current table of records to this City table by CityID fields defined in both tables.
When you export this data, you can include not only the cityID field as a column of exported data, you can also include any other fields from the related city record--such as the city name if you need that info.
"Encoded meaning" identifiers should really only be used for two purposes as far as I can imagine when it comes to possible uses for them:
a) for printing labels where some people reading the info need to be able to "decode" key details from a label where there isn't enough space to simply print out all of the information in plain text.
b) to support "legacy systems" out side of the immediate function of your database--such as interfacing with another DB system that requires this code or to keep users happy that are requiring it as this is how they did things in the past.
For both a) and b) you'd set up such a field and put it where needed on layouts, but you wouldn't use such an ID field as the primary key for use in your relationships to link records in this table to related records.
Thank You So much Phil
i checked both of them and i think i will go with 360 work demo and read the manual , before buying it
and then start what you suggest to me