Automatic Data Creator

[andycambo]

Hi,

First, apologies about the title. I had no idea how to describe my problem properly.

I'm currently at the beginning of creating a database. I think I have my tables, more or less, sorted and correct but I have a problem which I'm not sure how to get around. I'm pretty new at creating databases in access but have got a programming background which will hopefully help a little.

The database is for a small solicitors where there are several clients who may have many matters (cases) on going. Each new matter that is created has its own unique reference number which consists of:-

First 5 letters of the surname.
First letter of the first name
UFN in the middle.
The first date of contact
and at the end a unique number (which runs in order).

So for example if John Smith got arrested on the 28/07/09 and he was the first client we saw that day then his unique reference for this matter would be, SMITH/J/UFN/280709/001. If Peter Potter then got arrested on the same day his unique reference for this matter would be POTTE/P/UFN/280709/002. See how the last digit increases each time there is a new matter on this day?

So what I want to happen is this.

The user creates a client profile in the ClientProfile Table. This stores the usual information (names, numbers etc.). Then the user will press a button and will be asked for 'first date of contact'. They then enter the date of contact (without any '/') and then a new matter is opened for this client (in the Matters table) with a unique reference number for this matter that consists of the above (i.e. SMITH/J/UFN/280709/001).

Is this possible at all?

Thanks,
Andy.

 

[Mr. B]

andycambo,

To give a short answer, Yes, all of what you describe is possible.

Now, for my thoughts. Although what yo describe is possible I am just wondering why you don't just use an AutoNumber field for each of your tables to create the unique Id for each record. I would seem that you are working much too hard to do something that is actully built right into Access.

If you record the information about first contact and the actual data of each incident, that you and alway retrieve the data for one individual for a spcific data or a range of dates. You can even set the default for a field in one or more of your tables to record the current date and time right down to the second for the creation of that record. This field would always give you the ability to retrieve records in the order that they were created.

If I am missing something about what the requirements for the data are, then so be it, but I just do not see anything at this point that would require the hard work that you are mapping out.

 

[andycambo]

Thanks for your reply.

I was thinking of something similar. I was thinking of having a table called FileCreator in which this kind of information was stored.

Points I need to clear up on though is. The file isn't always opened on the first date of contact (it may be a couple of days after the event) so the date will always have to be entered in. Also each new date will begin at 001 again. So on the 28/07/09 the number may go up to 006 but on the 29/07/09 it will start again at 001.

I understand what you are saying but I'm unsure on how to implement it.
If I have a table consisting on the following fields..

1, PartOfSurname (e.g. SMITH)
2, FirstNameInitial (e.g J)
3, DateOfContact (e.g. 280709)
4, UniqueMatter (AutoNumber?)
5, UFNID (3 + 4. e.g 280709/001)
6, FileNumber (SMITH/J/UFN/280709/UNIQUEMATTER

I suppose the user could manually input 1,2,3,6. But how do I get the unique matter to increase by 1 by refering to the date of contact?

Thanks
Andy.

 

[Mr. B]

You need to use a field in your table that you name whatever you want to name it but select "AutoNumber" as the type of field. This field will automatically increment to the next number for each new record. A number is never repeated.

 

[andycambo]

But with AutoNumber, as you said, doesn't repeat a number whereas I do need to repeat numbers but not always. That's the part I'm finding difficult. Maybe I'm not being clear on what I need to achieve so I will try and explain as clear as possible.

___________________________________________________

1. The first client of the day contacts me on the 28/07/09. Therefore I open a file with the UFN of 280709/001 - DateOfContact/UniqueMatter.​

Another client contacts me on the same day. Therefore I open a file with the UFN 280709/002.
​

2 more clients ring on the same day (28/07/09) and are thefore given a UFN 280709/003 & 280409/004.


​

2. The next day, 29/07/09, a client contacts me. Therefore I open a file with the UFN 290709/001.
​

2 more clients contact me on the same day and therefore are given the UFN 290709/002 & 290709/003
​

___________________________________________________

Is that a little clearer? It is a strange way of doing things I know!

So back to what you said a AutoNumber wouldn't suffice as the UniqueMatter numbers are repeated, just on different days.

So what I need to do is, take the DateOfContact, see if there are any other records with this date, if there are check the what the highest UniqueMatter is and then add one onto this. If not use 001

I'm not sure how to go about this though.

Thanks for your responses,
Andy.

 

[Mr. B]

I guess I just do not get why you need the type of identifier as you describe. All you need for unique record identification is the unique value of an AutoNumber field. Then you simply records the appropriate information about each client and the activity.'

What does the creation of all of the pieces of data that you are trying to put together buy you that you do not get with the AutoNumber when is comes to the unique record question?

 

[andycambo]

What does the creation of all of the pieces of data that you are trying to put together buy you that you do not get with the AutoNumber when is comes to the unique record question?

It's for the purpose of identification. Not for the solicitors but for the Legal Services Commision (who record all this data) They need a reference for each matter which shows the date we first made contact with the client and a unique number at the end hence 280709/002. The Surname and Initial is so the reference relates to a client for billing purposes.

It know it isn't the best way to do things but unfortunatly it's the way it has to be done.

Andy.