display text box with calculated value with related table value.

[joshynaresh]

I have three Table. I want to create Form as exposed below.


1. Client Tabe

Client
Client_ID	Name	Address
61	Naresh Kumar Joshy	Bakdhuwa
62	HARI	KANCHANPUR
63	mohan	kanchanpur
64	shyam	sjfla

2. Limit Table

Limit Table
ID	Client_ID	loan_Date	Manual_Account	New_Limit	Outstanding_Bal
62	61	2070/05/17	1	12.00
66	62	2070/05/17	2	100.00
67	63	2070/05/17	3	10,000.00
68	64	2070/05/17	4	15,000.00

3. Txn Table

Txn List
ID	Txn_Date	Manual_Account	Principle_Debit	Principle_Credit	Principle_Balance
65	2070/05/17	1	5,000.00	0.00
66	2070/05/17	2	10,000.00	0.00
67	2070/05/17	2	0.00	60,000.00
68	2070/05/17	3	60,000.00	0.00
69	2070/05/17	1	13,000.00	0.00

4. In form, when I enter Manual_Account, Return Principle_Balance as below of releted Manual_ Account.

(sum of [Principle_Debit]-sum of [Principle_Credit])=(5000+13000)=18000 is it possible Sir? If Yes, Plz help me. Thanking You in Advance.

[June7]

Balance should not be saved to table but calculated whenever needed.

The sum is an aggregate calculation. This is accomplished with a GROUP BY (Totals) query.

Or you can build a report and use its Grouping & Sorting with aggregate calcs features.

[joshynaresh]

I have tried Dsum function but can't get result may be some query problem. Plz sir send me detail query.

Thank You sir.

[June7]

Not DSum. Do an aggregate query using Sum function. Access Help has guidelines on aggregate (Totals) query.

[joshynaresh]

I am new in access so plz send me query. sir, Thank You.

[joshynaresh]

I have not any help about aggregate (Totals) query. Plz help. by providing aggregate (totals) query.

[June7]

Showing aggregate calcs on forms can be tricky. Usually this is done on reports.

In the form Footer section, try a textbox with expression: =Sum(Principle_Debit - Principle_Credit)

[joshynaresh]

not fulfil my desire. i want to getting sum of total debit minus total credit only with releted manual account which we type in form. Thank You.

[June7]

The displayed sum will be based on whatever records are included in the form recordset.

Use the input value to filter the form records. For one method, review: http://datapigtechnologies.com/flash...tomfilter.html