pdvsa

asked on

# Sum in a Query (Balance)

Experts,

I have a query with [FacilityAmount] and [Amount].

There are 3 Facilities and each has an [Amount] (this is the amount drawn)

I need to show the [AvailableAmount] for each Facility, which is found by [FacilityAmount] and [Amount]

I think it is quite simple but I dont know how to do it.

I think it will be more clear if you open the attached db and look at the query.

Database1.accdb

I have a query with [FacilityAmount] and [Amount].

There are 3 Facilities and each has an [Amount] (this is the amount drawn)

I need to show the [AvailableAmount] for each Facility, which is found by [FacilityAmount] and [Amount]

I think it is quite simple but I dont know how to do it.

I think it will be more clear if you open the attached db and look at the query.

Database1.accdb

see query1

Database1Rev.accdb

Database1Rev.accdb

ASKER

Hi Rey,

I am not sure if the calc is correct because the FacilityAmount is as follows:

Uncovered $2,915,000,000.00

Nexi $2,000,000,000.00

JBIC $2,500,000,000.00

but in the query1 (below) it shows a sum of the FacilityAmount (basically doubling it):

ie: JBIC shows 5Billion and it should be 2.5Billiion (as above) and a SumOfAvailable should be: 2.5 - the sum tblDisbursement_Amounts where it = JBIC

Type SumOfFacilityAmount SumOfAvailable

JBIC $5,000,000,000.00 $4,877,000,000.00

NEXI $4,000,000,000.00 $3,905,000,000.00

Uncovered $5,830,000,000.00$5,690,000,000.00

thank you

I am not sure if the calc is correct because the FacilityAmount is as follows:

Uncovered $2,915,000,000.00

Nexi $2,000,000,000.00

JBIC $2,500,000,000.00

but in the query1 (below) it shows a sum of the FacilityAmount (basically doubling it):

ie: JBIC shows 5Billion and it should be 2.5Billiion (as above) and a SumOfAvailable should be: 2.5 - the sum tblDisbursement_Amounts where it = JBIC

Type SumOfFacilityAmount SumOfAvailable

JBIC $5,000,000,000.00 $4,877,000,000.00

NEXI $4,000,000,000.00 $3,905,000,000.00

Uncovered $5,830,000,000.00$5,690,00

thank you

open first the query Q1

see Q2 and query2

Database1Rev.accdb

Database1Rev.accdb

ASKER

Rey: thank you sir...Can you look at that query2 once more?

example: JBIC available should be : 2.5B - sum(25MM+98MM) = 2.377B

The facility amount doesnt appear to be correct. Ie: JBIC shows 5B and it should be 2.5B. The sumOfAmount is correct though.

example: JBIC available should be : 2.5B - sum(25MM+98MM) = 2.377B

The facility amount doesnt appear to be correct. Ie: JBIC shows 5B and it should be 2.5B. The sumOfAmount is correct though.

ASKER

all of the [FacilityAmounts] appear to be doubled and not just JBIC

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Wow that works. Now I need to figure out how you did it.

thank you

thank you

ASKER