Numbering System

[dazoloko via AccessMonster.com]

Dear All

This one is a belter, Im hoping somebody out there can help.........

What I want to be able to do is...............

I have a database which holds information on a client and the type of service
they have. I want the primary key to be made up of a combination of fields eg.


Each person has a unique 6 digit identity number but I cant use that as the
primary key because they could be on the system more than once because they
could have more than one service.

There are four Service Types Weekly, Short Break, Equipment, Talking Books

So the first part of the unique identifier for Example would be 111111W for a
Weekly Service
The same person could also have a Talking book there fore there would also be
a record 111111T

That I think I can manage. Where Im having the difficulty is They could end
their service and start it again, there for I would like to add some sort of
count so the first time a record is created for a particular service it has
the suffix of 1 and if that service is ended and then started again the
second time the number and service is created it would give it a suffix of 2.

Does this make sense ? If so, is it possible and if it is possible, any
pointers ?

Heres hoping.

D

 

[Falty]

That sounds really over complicated and suggests that you would end upw itha
database that is not normalised.

Read up on the great man Allen Browne's website here

http://www.allenbrowne.com/casu-23.html

This allows you to have more than one type of service for each client,
hopefully this is more suited to what you would like to do

 

[BruceM]

You should have a Client table and a related Services table (one client >>
several services). I expect hat's what the previous poster meant by
normalization. You can combine the ClientID with the ServiceCode (or the
first letter of the ServiceType, although that could lead to problems if you
ever add a new ServiceType that starts with the same letter as an existing
one). The combination, or concatenation, of fields can happen in a query,
in the Control Source of a text box, or through VBA code, but in any case
the point is that they are separate fields.