Relationships (HELP)

[Jace]

After years of doing data base design from FM 2.1 to current 5.5, I thought I was doing pretty well until recently I have hit a brick wall. I called the 900 number for help and found myself only getting deeper in the mud.

I have a two part question.... both relationships

SITUATION ONE -

I have a client who is a LANDLORD and I have created a check book for him. He pays the water bill on several apartments and makes out one check per month. $200.00. However, he needs to know which apartment should be charged with the allocated portion of the bill. Apt. 1 should be charged $55.00, Apt. 2 should be charged $40.00, etc. and all total they should come out to $200.00.

I need to know (if someone could tell me in ENGLISH) how to get the portions of the water bill to go each apartment number for record keeping?

The Second Portion of the question is...

I actually have another data base that keeps all the files on the apartments with the exception of the check book. The customer does not want (2) summary reports (1) from the check book and (1) from the other data base. He wants the data from the check book to relate over to the other data base and fall into place.

That way, it saves him from double entry...once in the check book, once in the other data file.

Again, I called the 900 FM number and I felt I even slipped a little further than before.

Relationships are very difficult to understand

and how they relate to each other. I can and do achieve direct relationship between two data bases, but it is used primarily for bringing over simple data from one to the other using a common key number.

(I would even consider paying someone to help me write the part that allows for this to happen) so that I can take care of this URGENT REQUEST from the client.

Any HELP OUT THERE?

Thanks for your time IN ADVANCE.

Jace

[Fitch]

Prepare to experience the illumination of MANY to MANY Relationships...

When you have many items that can relate to many other items, you generally will need to create a 3rd file to contain the relationship between them. A common example is an invoice file relating to a products file, you create a line items file to go between them.

And we're not just talking about lookups -- this information is "live."

In your case, you'll need a file called, say, Allocations, which will need 3 fields:

Check Number

Amount

Apartment

Use the Allocations file to allocate portions of a check. In this way, you won't end up with duplicate check numbers in your checkbook. Create a relationship in your checkbook file to the allocations file, by check number. Create a field that calculates

Sum(allocations::amount)

As far as your second question, I'm not sure if your issue is reports so much as the duplicate data entry. There's no need for that. Just make a relation in the apartments file to the checkbook, by apt. no., and allow records to be created in the checkbook. Put a portal for check entry in the apt. file, and that's about it.

Is this the kind of help you need? I can't do much more without knowing more about your situation. If you can make your question more specific, I can give you a more specific answer.

[Jace]

Thanks Fitch.....

I will attempt to follow the steps.... Relationships are difficult for me and I guess you might say I need all the help I can get.

I am OK with about everything else until then.