A few further points to consider:
1. An autonumber is designed only to guarantee unique values not sequential
ones. If an uninterrupted sequence is important then don't use an autonumber
bit a straightforward long integer number data type and generate the next
number in sequence when a record is inserted, e.g. in the BeforeInsert event
procedure of a form:
Me.[Case Number] = Nz(Dmax("[Case Number]", "Cases"),0)+1
where Cases is the table name.
This is fine in a single user environment, but could give rise to conflicts
in a multi-user environment where two or more users are inserting records
simultaneously. There are various ways of handling this, one of which, and
which also allows the next number in the sequence to be reset, you'll find at
the following link:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps
2. As the Handling Agent's name is being stored in a column in the table
you don't need to include the initials in an 'extended case number' column.
Nor should you do so as it introduces redundancy and the possibility of
inconsistent data being entered. The 'extended case number' should be
computed on the fly at runtime. You could use the following expression as
the ControlSource property of an unbound control in a form or report for
instance:
Left([Handling Agent],1) & Mid([Handling Agent],InstrRev([Handling Agent],"
")+1,1) & Format([CaseNumber],"000000") & Left([Case Source],1)
You can of course use a similar expression for a computed column in a query.
Using the InstrRev function does cater for the possibility of the name
having a middle name or initial, or there being two spaces before the last
name, but would for a name such as 'Victoria de los Angeles' the last name
initial would be returned as 'A' rather than 'd'. Its always best to have
separate FirstName and LastName columns in a table.
3. A further point about names is that they can be duplicated, so you
should really have a separate table for the handling agents, with one row per
person and a primary key, which can be an autonumber, such as
HandlingAgentID. You'd then have a foreign key HandlingAgentID column (not
an autonumber this time) in your cases table. You'd base your form or report
on a query joining the Cases and HandlingAgents tables of course to pull in
the names.
4. You should also have a separate Sources table with one row per source
type. As the text descriptions will be unique values this can be the primary
key. The Source column in the cases table will be a foreign key referencing
this. By enforcing referential integrity and cascade updates in the
relationship between Cases and Sources you ensure that only valid Source
values can be entered in Cases.
Ken Sheridan
Stafford, England
