Database design - beginner! (1 Viewer)

[gconeuk]

Hi,

I have beginner knowledge of Access but have been tasked with creating a new database by combining four existing databases but making them better.

Our area of business is insurance. Currently have the following tables which will become more
tbl_clients
tbl_policies
tbl_claims

frm_database

What I want from our database is a main form (based on tbl_clients) with sub forms on the main form which show the information from tbl_policies, tbl_claims

I am just not sure how a record for tbl_clients and how that would link to tbl_policies and tbl_claims bearing in mind that one client could have several policies or several claims so several records on each of those tables. Each record in tbl_clients would have a unique user reference field client_number (not an autonumber but it would be a primary key as we don't want duplicates)

An added problem is ensuring that all users in our office (there are about 10 employees) can access the database at any one time either data entry into the form or in the way of MS word linking to the database for mail merge purposes (to produce letters, policy documents based on fields in the database).

I don't know if this is enough information but if someone could kindly give me some pointers on where I should be starting with this please.

I have read a lot of information but I'm still trying to get my head around it all.

Many thanks.

 

[jdraw]

What exactly does this mean

making them better

Who designed and developed the existing databases?
What documentation for the design(s) exists?
Has anyone considered a commercial package rather than in-house development?

 

[CJ_London]

bearing in mind that one client could have several policies or several claims

Your policies table will need to have a field for the clientID (a unique user reference field client_number) and assuming any claim is made against a single policy, your claims table will need a policyID field.

You would then have a one to many relationship between clients and policies and a one to many relationship between policies and claims.

An added problem is ensuring that all users in our office (there are about 10 employees) can access the database at any one time

Depends how your network is set up, but the principle is you will need to split your database to front end and back end. The backend contains tables only and is located in a suitable shared area on your network. Each user will have a copy of the front end on their local machine.

Technically Access will support 255 concurrent users although there could be a serious performance issue with this number. I have developed systems where there are 30-40 concurrent users which have worked perfectly well, but the db does need to be designed correctly to minimise the impact of slow networks etc.

 

[gconeuk]

This is great thank you. So assuming I set it up like you say, the relevant client - policy or policies and claim or claims data will only appear for that particular client under ClientID?

Thanks

 

[gemma-the-husky]

this will be extremely difficult for a beginner.

Are these big database? how many tables/queries/forms in each?

 

[gconeuk]

Hi
The existing four databases only have one table in each. There is no rationalisation. I would say I'm a beginner only because I don't do this every day but I did build two of the databases and am ok with running queries, reports and designing forms. My real issue is getting all the data to link up in one form so pulling in data from a number of tables that I set up which I've never done before.

The Main one is tblClients - name, address, contact number etc

tblpolicies - this will be Insurer, limitofindemnity, excess, premium, tax, total, net to insurer for each type of policy product so the prefix for each field will be e.g. PL is public liability, PLInsurer, OF is office so OFInsurer

tblClaims so each policy could have any number of claims. Each claim will need relevant info like Claim date, amount paid, settlement date

Then we have tables will enable us to complete word documents. For example

TblFileCheck - basically a list of dates when certain information has been provided to the client and what type of cover they have

TblProposal - a table used (in conjunction with tblClients and tblPolicies) to populate answers on a Ms word proposal form.

We will need a query so that we can produce a spreadsheet to pay the relevant insurer each month. I think I'll be ok with this as I have set up queries before.

That's pretty much the gist of it. The end result is to be able to view the Client record and all their policies and claims in one view so getting the links right so we aren't looking at everyone's claims for example.

Thanks.

 

[CJ_London]

policy or policies and claim or claims data will only appear for that particular client under ClientID?

You would need to use left joins between clients and policies and policies and claims - otherwise you would only see clients with claims.

See this link for info on the different type of joins.

http://www.w3schools.com/sql/sql_join.asp

Depending on what you are trying to achieve, rather than worrying about the joins, you may be better using a mainform for clients with a subform for policies and another for claims