Find A Relation Between 2 Diff Tables

[NiceMan331]

hi
i have a database to manage utility bill payment , it consist of
- Bills :
- billID
- Benificiary Name
-Cost Center
- Bills Transaction :
-TransID
-bILLid
-BillDate
-BillAmount
-Payment Transactions :
-PayTranID
-bILLid
-PayAmount
-PayDate
-BankRef

i made a union query from Bills Transaction and Payment Transactions to calculate bills balances which is : billid,sum(Bills Transaction.BillAmount)-sum(Payment Transactions.PayAmount)
all is working well , but the problem is i cannot find any relation between billtransaction
and billpayment ( per bill ) , cause i wish to payment details for each single bill transaction
the normal case is : bills issued as monthly basis but may fully or partially pay as the following cases :
- each bill transaction may fully pay one time
- in some cases : each bill transaction may fully pay but in multi settle
- multi bill transaction (per BILLID) may fully pay one time
IN CONCLUSION : each bill transaction should be stteled fully within one or two or maximum 3 months , say bill balance for each bILLID shall be zero.
how i could find a relation between this two transaction ( bills and payment ) to preview
payment information for each single bill transaction
exapmle :

billID : 39
BILL Transaction           BillPayment                  BillsBalance
Bill Date - Amount        PayDate-    Amount
jan08      - 1000           1-1-2008   1000                0
feb08       -1200           5-2-2008    800                400
                            15-2-2008   400                 0
mar08        1900                                           1900
apr08        1100           30-04-2008   3000               0
may08        1200           05-05-2008   900               300
jun08        1300           30-06-2008   1600               0

 

[georgedwilkinson]

A new table:
BillTransaction:
ID (surrogate PK)
billID (FK)
PaymentID (FK)
Amount
DateApplied
Notes
etc.

When a payment is made, you apply the payment to one or more bills, along with the amount of each bill which the payment covers.

Straight out of the data modeling book.

 

[NiceMan331]

this will help only if one or more than one payment is closed to one bill id
but as i decribed above in the example :
one payment may be done for one or more than one bill transaction , not that only , but also
it is not necessary payment will cover all bill due amount
in addition , there is a possibility for prepaid payment , say : payment to the bill acount may done in advance in spite of the bill not yet received and sure not yet posted
in this case , bill balance will be negative , and when bill receive it will be poste to cover that balance

 

[neileg]

Then you will have an entry in Payment Transactions and no entry in Bills, or vice versa. In that case you have no entry in BillTransaction because there is nothing to link.

 

[georgedwilkinson]

this will help only if one or more than one payment is closed to one bill id
but as i decribed above in the example :
one payment may be done for one or more than one bill transaction , not that only , but also
it is not necessary payment will cover all bill due amount
in addition , there is a possibility for prepaid payment , say : payment to the bill acount may done in advance in spite of the bill not yet received and sure not yet posted
in this case , bill balance will be negative , and when bill receive it will be poste to cover that balance

I totally disagree...this solution is straight out of the data modeling bible and has worked for me in every system that I've built. See what Neil said for a better explanation.

Good luck!

 

[NiceMan331]

ok neil said that there is no link in this two tables , i accept it
georgedwilkinson , how u can solve this case where we have an entry in Payment Transactions and no entry in Bills, or vice versa ?

 

[georgedwilkinson]

If either is missing, you have no record in the associative table I pointed out earlier. When the user is reconciling (during a new Bill event or or PaymentTransaction), you need to calculate the "amount remaining" for the user, allowing him/her to "create" BillTransaction (I probably should have named it "BillPayments") records.

This is no different than what you said you wanted to do and it is straight out of the data modeling body of knowledge.

I must say that I'm a little disappointed that you joined the group and immediately presented a problem you are having and 2 professionals answered your question precisely: and you told us we were wrong. You have no idea of the value of what you received. That's not a good way to get responses.

 

[NiceMan331]

I must say that I'm a little disappointed that you joined the group and immediately presented a problem you are having and 2 professionals answered your question precisely: and you told us we were wrong. You have no idea of the value of what you received. That's not a good way to get responses.

this talk is totally rejected , because
first of all i start posting my question with respect to all members here including u
second , let any memebr read the total thread , to highlite any un_respectable sentense in my 3 posts , and i hope u to refer to me in which exactly sentenc i said u & neil were wrong ? i challenge u to do that , it is just discussion to reach to the correct way for my problem , if u think u are a proffesional programmer , as u said above , u have to give me an absolte soltion as Neil post the reslut in a short answer without any missues of talking as u post in your last post ( too musch talk with no benifit )
finally , i am here to learn , to get a knowledge from the experts , only for that , hope this is the best site to present
the proffesional knowlege for all members
thanx

 

[georgedwilkinson]

I stand corrected and apologize.

Did the answer resolve your question?

 

[NiceMan331]

actually i get it from Neil when he post " In that case you have no entry in BillTransaction because there is nothing to link. "
ok , it is understood
thanx to u all
regards

 

[neileg]

Thank you for the thanks.

I suspect that English is not your first language, NiceMan331. You have to understand that although you have made your meaning clear, the way you have used the language can give the wrong impression. Nobody gets paid in these forums, we do it to help each other. If what you say seems ungrateful or rude, then this can get in the way. We need to be tollerant and patient with each other, that's helps the world go around!

Happy days.

 

[NiceMan331]

u r right Neileg , english is my second language , i appriciate what u typed here
but , georgedwilkinson , has no right to say what he posted above
any how , it is ok now , i learned
hope i will get more and more from u
thanx

 

[georgedwilkinson]

u r right Neileg , english is my second language , i appriciate what u typed here
but , georgedwilkinson , has no right to say what he posted above
any how , it is ok now , i learned
hope i will get more and more from u
thanx

Again, please accept my apologies.

I'm glad you were able to get it working.

 

[NiceMan331]

Again, please accept my apologies.

I'm glad you were able to get it working.

georgedwilkinson , when a proffisional programmer as u , make sorry to one bigginer as me , I Admit that u r a very good person
i respect u all , georgedwilkinson , Neiling ,,, and all other experts here
i hope u accept me as a student to learn alot from u
regards