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

Microsoft Access

I basically need a sum for [Amount] (this is the amount drawn) but for each [Facility]

see query1

Database1Rev.accdb

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

open first the query Q1

see Q2 and query2

Database1Rev.accdb

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.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

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

thank you

thank you