Query Calculation

[Haytham]

Hi All...
I have a table with BillNo and amount. Another table for Payment Vouchers. Both linked by BillNo.
In a query, I have Voucher Table Sum the amount.
So if payment is in two different days, customer will have 2 vouchers etc.
The query will calculate the difference betwen the Bill Amount and Sum of Payment to find the Balance which the customer is to pay. This query is tabular, and got everything perfect.
Now, in a new column in query, I want to put the Voucher No which the customer paid. Is it possible to do it...
By other way I can say that:
A Query calculates a difference between Bill Amount and Sum of Voucher Receipts. Is't possible to get the No of the Receipts in a new columns in a query.....

 

[Pat Hartman]

You said that there may be multiple vouchers. Including voucher in the query will return multiple rows and eliminate any summing. Once you create the query that sums the vouchers, you may make use of the code from this kb article to obtain the voucher numbers.

ACC: How to Concatenate a List of Items from a Many-Side Table

 

[Haytham]

Hi Pat Hartman,
Indeed I'm very thankful for you. I applied in my db and it works like a dream.

 

[Pat Hartman]

Glad it worked. I just noticed your location field. I spent a year working not too far away in Kuwait some years ago. I was part of a team that developed a human resources system for the civil service commission.

 

[Haytham]

Kuwait is very near... Hope and welcome to visit us anytime.