Set design with 2 tables,and 2 forms

[lcgreene]

The parent table and child tables are linked by date,amount and first name. There is a single record for each record in the parent. The child has some records that corresponds to which are duplicates. The duplicates have the same linking fields, IE amount, which may be "0" and or a non "0", this is due to the specific record being issued 2 or 3 times and the content of a field has changed.

In a one to one link, only the first record is displayed with the parent. In a one to many link only the first child is displayed with the parent. I need t have the parent displayed and each child that is linked to the parent displayed. The child table is predominantly single records linked to the parent, so this linking and display requirement is not frequent. I have tried to use the embedded browse, but that shows 2 instances for all child records linked to the parent as vs. only one and multiple when there are more then one child record.

How can one parent form when incremented by record, remain on screen and the 2nd child be incremented to the screen ?

[DaveM]

Re: Set design with 2 tables,and 2 forms

I would question your linking first. Maybe I am "Old School", but I always use one field as a linking field. It is always incremented in the main table to keep all the other regular links straight. example: id, n,10,0 and each of the child tables has the same field. This makes for a bullet proof link.

After the set is made, you can put your parent records on a form and (how shall I say this) = Imbed a form and possibly a browse of the child records on the same form. Maybe use tabs??

I am trying to make this simple as possible for you. There are many other scenarios possible.

[Ted Giles]

Re: Set design with 2 tables,and 2 forms

LCG.
Post a copy of the Set and where you have got to.
I'm not clear on what you are really after.
As Dave mentioned, linking on more than one field will restrict what shows, as ALL the fields have to correspond exactly.

Date, Amount and First Name will all have to match.

First Name only will show all dates and amounts for that name
Date will show First Name and Amounts for the selected Date

Try the attached for examples. Could be way out, but it's raining here and I can't get out so I had a fiddle.

[lcgreene]

Re: Set design with 2 tables,and 2 forms

Ted,

The reason I have three fields in my set link is due to the fact the parent table contains many records with the same date and name of client but different amounts charged. There are also records for other clients with the same date and often the same charged amounts. I am working with medical claims data. The parent records are specific for a charge and represent the primary insurance carrier. The child records are the secondary insurer and often there are two or more child records for the same single parent record.

The parent is single and the child can be single or mixed where mixed is 2 for 1 parent. So the one to one set relationship will only show the first child that matches the parent and not the second. The reason for 2 children is the secondary may process to records breaking the cost by medical code where as the primary processes one record for the whole.

I use cdate to definitively know the dates match, amount charged and first name. To date first names have not been repeated so that is not an issue at this time. I hope this gives a better perspective of the issue.

Thanks

Lou

[DaveM]

Re: Set design with 2 tables,and 2 forms

Lou,

A date field is one of the worst linking fields possible. There are other ways to get to where you want to be. Think if a date is changed, link is broken?? Not to say it cannot work sopmetimes, just not the best idea.

Ideally, a patient is entered one time. Child records can be many and varied.

[Ted Giles]

Re: Set design with 2 tables,and 2 forms

Lou, I understand a bit more now, but I'm with Dave again on this one.
Was the attached DBase of no use?

Logically, the Claimant (insured) will/should have an ID.
Children may have an ID of their own, so,
Mother ID = 123
Child 1 ID = 234

Child Table would have Parent ID = 123, Child 1 ID = 234, Child 2 ID = 456

Set View =

Parent
--Child 1
--Child 2

The amounts claimed should I suggest be in a separate Table just keyed on the ID's so they can be sorted and analysed.

Example;
You could create a Browse with a "Total" field for the claims linked to a Claimant over say the past 12 months, or within the Insurance Period. Show in RED if the budget is exceeded.
This would require another Set with Claims linked via ID to the Parent/Child details.

Do you have a Visio Logical Diagram we could look at?

[DaveM]

Re: Set design with 2 tables,and 2 forms

I have a names table as master

names mid,n,10,0
connected to money by mid,n,10,0 one to one
connected to trades by mid,n,10,0 one to many
other parts of the set are done from other fields such as the car sold is connected by stock number one to one.

No user ever sees the mid field. It is always hidden completely. They do not need to use it, so why confuse them?

Make sense?

[lcgreene]

Re: Set design with 2 tables,and 2 forms

Thank you for your suggestions. The records by client do not have iD for eac person. Even if they did, they would not be matched since the originating data is from different sources. I could of course do an update and post ID's to both files, that would only result in the same anomaly. The inability to have the parent table which has single records match a child where there are single and more than one record for the same instance of service.

Ex: Parent Child
---------------- ----------------------
Claim No Date Fnm Lnm Cost Type Claim No date Fnm Lnm Cost Type

2299000` 1/1/11 John Jones 20.00 medical 34567 1/1/11 John Jones 10.00 Medical

34567 1/1/11 John Jones 10.00 Outpatient

3245555 1/1/11 Paul Thomas 45.00 45454 1/1/11 Paul Thomas 45.00 Outpatient


How would you do a one to one or one to many ? Keep in mind often the names in the child data base are not equal character to character due to how the input was done by the owner of the system and also truncation when downloaded. At present I am bale to catch the 2nd child record based on learned inconsistency,however it is not an efficient method.

Lou

[Ted Giles]

Re: Set design with 2 tables,and 2 forms

If the data is rubbish then it needs to be cleaned up. Forget developing a system until you get this sorted. You will go mad and get blamed.
You cannot easily match records in a Set without some sort of handle to link them.
You could start by matching data using a constructed field comprising name, age, gender, DoB, etc whatever you have and using the Soundex Function.
Build a Joined table and get the user/system owner to confirm what is correct.
Once the data in the imported tables is correct you can start to create appropriate links.