Make a query of Balance/Outstanding Amount Display only

[Shyamdharam2]

pdate	Branch	Bnos	customerid	place	agentname	grossamt	taxes	disc	finalamt
01-Apr-24	India	1	24	New Delhi	Abc	1542	77	5	1614
05-Apr-24	India	2	36	Bihar	Xyz	2151	87	15	2223
06-Apr-24	India	3	54	UP	Xyz	2514	103	25	2592
20-Apr-24	India	4	26	New Delhi	Abc	1864	79	6	1937
24-Apr-24	Bangladesh	5	32	Dhaka	Xyz	3611	115	30	3696
30-Apr-24	Bangladesh	6	35	Dhaka	Xyz	3650	120	35	3735
01-May-24	Bangladesh	7	52	Dhaka	Xyz	5411	135	51	5495
01-May-24	India	8	52	UP	Xyz	3000	118	25	3093
07-May-24	India	9	36	Bihar	Xyz	3500	128	35	3593
08-May-24	Nepal	10	49	kathmandu	Xyz	3000	118	26	3092
12-May-24	Srilanka	11	53	Columbo	Abc	5600	139	52	5687
Branch	pdate	customerid	billnos	amt	agent	narration
India	05-Apr-24	24	1	1614	Abc	Full Paid
India	07-Apr-24	36	2	2223	Xyz	Full Paid
India	07-Apr-24	54	3	2000	Xyz	Paid 1
India	07-Apr-24	54	3	592	Xyz	Paid 2
India	20-Apr-24	26	4	1000	Abc	Paid 1
Bangladesh	24-Apr-24	32	5	1500	Xyx	Paid 1
Query Result should show below item
Branch	Customer	Billno	Billamt	Payment	Balance
India
Bangladesh
Bangladesh
Bangladesh
India
India
Nepal
Srilanka

[td]

26​

[/td]​

[td]

4​

[/td]​

[td]

1937​

[/td]​

[td]

1000​

[/td]​

[td]

937​

[/td]​

[td]

32​

[/td]​

[td]

5​

[/td]​

[td]

3696​

[/td]​

[td]

1500​

[/td]​

[td]

2196​

[/td]​

[td]

35​

[/td]​

[td]

6​

[/td]​

[td]

3735​

[/td]​

[td]

3735​

[/td]​

[td]

52​

[/td]​

[td]

7​

[/td]​

[td]

5495​

[/td]​

[td]

5495​

[/td]​

[td]

52​

[/td]​

[td]

8​

[/td]​

[td]

3093​

[/td]​

[td]

3093​

[/td]​

[td]

36​

[/td]​

[td]

9​

[/td]​

[td]

3593​

[/td]​

[td]

3593​

[/td]​

[td]

49​

[/td]​

[td]

10​

[/td]​

[td]

3092​

[/td]​

[td]

3092​

[/td]​

[td]

53​

[/td]​

[td]

11​

[/td]​

[td]

5687​

[/td]​

[td]

5687​

[/td]​

[td]

0​

[/td]​

 

[Shyamdharam2]

 

[arnelgp]

put it in excel workbook and attached it as .zip, then it would be easy to manipulate the data.

 

[Shyamdharam2]

put it in excel workbook and attached it as .zip, then it would be easy to manipulate the data.

In Excel it can be Easy but in Access its not that easy
Multple Condition Balancing... if Possible then try it

 

[arnelgp]

In Excel it can be Easy but in Access its not that easy

i said, put your data in excel not in image, so it will be easy to manipulate.

 

[mike60smart]

View attachment 117750

Hi
I believe you need to have Data Input Forms so that you can relate 1 Customer to 1 or More Loan Amounts and also a related Form to allow you to enter payments made.

Doing your process just by entering data in two tables creates errors.

The attached screenshot is what I believe your relationships should be.

Also, based on the data provided you state that CustomerID 36 is Fully Paid when if fact the Customer has only paid some of the
money.

See Screenshot of Data Input Form for CustomerID 36