Master Child Links Form / Sub Form

[gconeuk]

Hi there,

I have a database with several tables which include the following tables

Main Table is: tblClient
Fields:
ClientID (PK) - Autonumber
ClientRef - text
GenClientRef - text
Name, Address etc.

Other Related Tables:
tblPI
fieldsIID (PK) - Autonumber
ClientRef - text
GenClientRef - text
Insurer, Policy Number etc

tblPL
fieldsLID (PK) - Autonumber
ClientRef - text
GenClientRef - text
Insurer, Policy Number etc

The relationship between tblClient and the other tables are linked (one to many) by the following fields (which are in all tables)
ClientRef - text
GenClientRef - text

The join type is currently set to 1.

A record in tblClient will only have one corresponding record in tblPI, tblPL etc. I have set it up this way as the fields in each related table can vary and there are several fields in each table.

User Input is via Forms and SubForms

Main Form: frmClient

And each related table has it's own subform so

frmPI
frmPL

These are linked
Master Fields: ClientRef, GenClientRef
Child Fields: ClientRef, GenClientRef

Note:
ClientRef may be blank
GenClientRef may be blank

But both ClientRef and GenClientRef cannot be blank

(Subform ClientRef and GenClientRef fields are "locked" so cannot be altered)

-----------
Ok, I hope that explains the structure. Using Access 2013 btw

My question is that when testing, I have inputted information into one of the subforms (frmPI) which has passed the data through to tblPI.

The data in tblPI is ok where the fields in frmClient for ClientRef and GenClientRef have been completed on the mainform frmClient.

On entering data into (subform) frmPI for a new record where only ClientRef has been completed on the mainform frmClient (and GenClientRef is blank on the mainform frmClient), the data has been passed through to tblPI BUT when exiting the database and opening it again, the (subform) frmPI does not show the data but it is there in tblPI.

Can anyone explain what I'm doing wrong here? :banghead:

Many thanks in advance.

 

[Minty]

If ClientID is unique and the combination of ClientRef, GenClientRef is also unique, why not simply refer to the ClientID?
If I'm not mistaken you seem to have overcomplicated things a bit?

 

[gconeuk]

Hi there and thanks for replying.

I probably have!
Can I ask, would I need ClientID in each subform? And link these in the relationship manager?

Thanks!

 

[arnelgp]

If ClientID is unique and the combination of ClientRef, GenClientRef is also unique, why not simply refer to the ClientID?

your correct! the tables should be:

Main Table is: tblClient
Fields:
ClientID (PK) - Autonumber
ClientRef - text
GenClientRef - text
Name, Address etc.

Other Related Tables:
tblPI
fieldsIID (PK) - Autonumber
ClientID - Long (FK)
Insurer, Policy Number etc

tblPL
fieldsLID (PK) - Autonumber
ClientID - Long (FK)
Insurer, Policy Number etc

then the links:
Master Fields: ClientID
Child Fields: ClientID

 

[jdraw]

gconeuk,

Work through this tutorial to learn some basic concepts of design. You will learn (by experience) a process that you can use with any database design. You have to work through the tutorial, but you will learn.

see this for more Normalization

Get a clear, concise description of the business for your reference and for communicating with others. The database you are designing is intended to support your business(issue/opportunity), so for readers to help, they really need some info on your business.
Good luck.

 

[gconeuk]

Ok many thanks for that.

I've changed the tables to show ClientID in each of the sub (related) tables.

Just one thing,

If a new record is created in the Mainform, how does this filter the results in the Subform to just show that record's subdata? What I want to happen is that when a new record is created in the mainform (frmClient) a new record is created automatically in each of the subforms (so they are blank ready for user input).

Previous use of ClientRef and GenClientRef meant that the Subforms were only showing the records with matching references in each table / form.

Thanks again.

 

[gconeuk]

Hi jdraw,

Thanks for posting and I'll certainly read through that information you provided.

For background, I work in an insurance brokerage which has recently merged with another company and I have been tasked with merging four databases into one.

With this new database, it is centred around a Client (basic info like name address etc) and is sub-divided if you like between different policy types (client may have several policies), transaction information per policy (so when certain information has been received or sent to client), claims (could be several for each policy).

What slightly complicates matters is that we have two different departments who offer some same policies and some are unique to each department - these departments have different references (hence the ClientRef and GenClientRef fields dependant on the department). I believed that using these references as the link between the different policies and claims would be the easiest solution.

Thanks

 

[jdraw]

Before you get too deeply involved in Access (or other dbms) get a clear description of the business. Start at the 30000 foot level and gradually add detail. Get a data model and some test data that reflects your business.

see my stump the model approach and here

This will help you with a strategy and plan. Don't try to build the "ultimate database" on the fly. Make a plan, check the plan and model out with others who will have to use the database, reconcile every issue, make it work on paper, THEN build according to the approved plan and model.

good luck.

see the data models here -- check for insurance claims, policies etc. These are generic and free. They may help you or at least give you a starting point independent of your current set up.

Just another thought from experience. Use a naming convention that is meaningful to your latest business, and that can grow with you if you do more acquisitions etc.
Get some data management --naming, edit/validation, definition, representation and procedures -- standard practices established. This is an opportunity for some positive change. And remember, you can have a longer term plan, and develop what you need based on business priorities -knowing that the pieces fit in the bigger plan.