Help for beginner?

[rasghar12]

Hi,

I'm new to this forum and fairy new to Access. I have AS level (UK) understanding of MS Access and i need help!

I need to create a database for a solicitors firm and it needs to do the following:

1) Open a new file (using their current file numbering system)
2) Store client details
3) Link timesheets(on the database) to files
4) Search for a client using their file number

I've tried to do this, but the relationships are really starting to bug me!

So far i created 3 tables

1) File open table
2) Client details
3) Timesheet

and i keep creating relationships and deleting them.

Anyways, can anyone guide me in the right direction to what to do? For example what to make the primary key etc?
One problem i came across is that the file numbers have text in them and cannot be restarted so autonumber cannot be used for the field type!!

Please can someone help me?

P.S. i'll need to create forms to enter these details and i'll need to create forms for the timesheets and there it will need calculations.

 

[GPGeorge]

You've given a pretty good HIGH-LEVEL summary of the requirements. Now you need to get down to the task of identifying the details.

You will have at least three tables before you're through, but let's just talk about what these three proposed tables are all about. Then we'll identify at least two additional tables you'll need.

CLients are probably the most straightforward. They are people and there are things common to identifying all people: FirstName, LastName, DateofBirth, etc. Whether you need to include certain details about them (DateofBirth springs to mind) depends to a large extent on whether you NEED that detail as part of the work process. Ask your employer to list ALL of the relevant details they need to work effectively with their clients. Maybe you can get examples of their current paper documents with that information. In any event, you'll want to identify each client with a proper Primary Key. Access' Autonumber is an excellent choice for that.

Next, you need to define "File open table". I have only a vague idea what that means, nor what details you need to add to it. I would NOT call it "File open table", though. Maybe "ClientFiles"?

Again, ask the client what details they need to track with regard to "ClientFiles". I can imagine you'll want things like "DateOpened", to reflect the date the file was opened, and perhaps, "OpenedBy" to identify the person in the client's office who opened the file. Beyond that, only your client can tell you. Ask them. Perhaps you can get samples of current paper "Open Files" as a guide.

As you say there is an existing File Numbering system, you can jsut use that for the Primary Key. It won't be as easy to work with as an Autonumber would be, but it will work. ALso, you could add an Autonumber field, use it as the Primary Key, and keep the existing File Number as an attribute field. That's not always considered a best practice, but it might be simpler for you in this case.

Timesheets. Well, I GUESS this means the hours recorded by individual solicitors working on open files? It will need fields to capture the identity of the file, the identity of the solicitor, the date work was done, the start and end times for that work, the type of work done, and whatever else is important to your client. (Ask them, they know.)

And we just identified a fourth table there in that last paragraph: Solicitor. You need to have a table that identifies the solicitors working in the client's firm. Ask the client what information they need to track about them. Certainly first and last names, but perhaps specialities as well? Maybe their hourly rate? Only the client can tell you all that.

That brings up the need for another table: Specialities. This will be a lookup that just lists the kinds of specialities a solictor can have: criminal defense, taxes and accounting, etc.

How long did you tell the client this is going to take? You may be spending several days just understanding enough about their business to design a reasonably adequate set of tables. And then you'll be ready to start talking forms....

 

[MBB]

You've given a pretty good HIGH-LEVEL summary of the requirements. Now you need to get down to the task of identifying the details.

You will have at least three tables before you're through, but let's just talk about what these three proposed tables are all about. Then we'll identify at least two additional tables you'll need.

CLients are probably the most straightforward. They are people and there are things common to identifying all people: FirstName, LastName, DateofBirth, etc. Whether you need to include certain details about them (DateofBirth springs to mind) depends to a large extent on whether you NEED that detail as part of the work process. Ask your employer to list ALL of the relevant details they need to work effectively with their clients. Maybe you can get examples of their current paper documents with that information. In any event, you'll want to identify each client with a proper Primary Key. Access' Autonumber is an excellent choice for that.

Next, you need to define "File open table". I have only a vague idea what that means, nor what details you need to add to it. I would NOT call it "File open table", though. Maybe "ClientFiles"?

Again, ask the client what details they need to track with regard to "ClientFiles". I can imagine you'll want things like "DateOpened", to reflect the date the file was opened, and perhaps, "OpenedBy" to identify the person in the client's office who opened the file. Beyond that, only your client can tell you. Ask them. Perhaps you can get samples of current paper "Open Files" as a guide.

As you say there is an existing File Numbering system, you can jsut use that for the Primary Key. It won't be as easy to work with as an Autonumber would be, but it will work. ALso, you could add an Autonumber field, use it as the Primary Key, and keep the existing File Number as an attribute field. That's not always considered a best practice, but it might be simpler for you in this case.

Timesheets. Well, I GUESS this means the hours recorded by individual solicitors working on open files? It will need fields to capture the identity of the file, the identity of the solicitor, the date work was done, the start and end times for that work, the type of work done, and whatever else is important to your client. (Ask them, they know.)

And we just identified a fourth table there in that last paragraph: Solicitor. You need to have a table that identifies the solicitors working in the client's firm. Ask the client what information they need to track about them. Certainly first and last names, but perhaps specialities as well? Maybe their hourly rate? Only the client can tell you all that.

That brings up the need for another table: Specialities. This will be a lookup that just lists the kinds of specialities a solictor can have: criminal defense, taxes and accounting, etc.

How long did you tell the client this is going to take? You may be spending several days just understanding enough about their business to design a reasonably adequate set of tables. And then you'll be ready to start talking forms....

well written.

 

[rasghar12]

GP George thanks for your reply,

At the moment i have 6 tables which are as follow:

[Client Details]: name, address, telephone numbers, file number, the unique ID(autonumber) called Family ID

[File info]: which has the file number and type of work

then there are 4 different Rates tables, which are effectively four different ledger tables.
These specify the work done and the rate for a pariticular type of work for example
£50 per hour for travelling in a legal aid rate.
All the Legal Aid rates are in their own table.
All the legal help rates are in their own table.
and ofcourse the private rates are in their own table.
The above are the rates and information for the timesheets.
Each tables primary key is a long the lines of "legal aid Ledger ID" which is autonumber,
each different table has a similiar primary key.

Then there is the financial ledger, which simply records money in and out of the account of that particular client.

I need everything to link back to file number, like one ledger should only be specific to one file number

I need the following, one Family ID can have many file numbers.
One file number can have many different ledger ID's.


I know what is needed to be captured.


At the end I need a system where once you enter the file number, it brings you to the client profile.
In the client profile you can add information to the profile, update the time and financial ledgers and produce "invoices" for each ledger which a total etc which is specific to that file.

I have created a system, it doesn't look the best.
I would appreciate it if someone looked at it and got back to me?

The final point is that the employer is my father, i told him it would be done ASAP but i also told him that it can be a few days or few weeks. Time is not an issue.

Thanks for the helps, it is most appreciated.