Show remaining balance in same query

[annysjunkmail]

Hi,

I manage an awards database. Awards are paid in stages called payments,
which vary in amount. I have 2 main tables in my database called
tblAwards and tblPayments.
I am trying to produce a query to group Awards to show total payments
and, very importantly, to show balance remaining for each award. I can
show Awards and total payments but I can't work out how to show the
balance remaining in the same query - it has to be the same query.

Can it be done - it would be great it someone could help me here!

Thanks
Mick

 

[Steve Schapel]

Mick,

Well, how do you work out the balance remaining? Is there an Amount
field or some such in the Awards table? And is there an AwardID field
or some such in the Payments table so you know which Award each payment
relates to? If so, the idea would be to make a Totals Query based on
the Payments table to give you the total payments for each award, and
then make a second table which includes this first query, plus the
Awards table, joined on the AwardID field from each, which will allow a
calculated field in the query, being something like this...
Balance: [Amount]-[SumOfPayment]
Will this work for you? I am not sure what you mean by "it has to be
the same query", but in my suggestion it is all in the same query - a
query based on another query. Otherwise why does it matter?

 

[Pat Hartman\(MVP\)]

Why does it have to be done in the same query. Are you using a query when
you should be using a form or report? Reports are the best alternative for
the presentation of data. Queries should only be used as the RecordSource
for a form or report. They should never be viewed as an end product.

 

[annysjunkmail]

Thanks for the replies lads.

Preferably I would like it to be in the same query as I am asking this
question on behalf of my staff who are not very IT literate and I am
trying to make life easy for them. I was trying to avoid the situation
of running a query on a query as, trust me, it confuses them too much.
I was really asking experts like yourselves would it be possible to
keep it and run it within one query as I thought it was possible.

The outputs of this query is to be used as RecordSource to be outputted
to Excel for further analysis.

I understand what you are saying Steve and you are correct in your
statements. All fields are correctly set up to allow a balance to be
calculated as you have suggested. Looks like I will have to set up 2
queries, one to shows total payments and one to do the calculations
instead.

Thanks
Tony

 

[Tom Ellison]

Dear Tony:

You're almost there. Go ahead and create two queries to provide the answers
separately.

You can then combine these into one query, using a UNION ALL. First, both
queryies must have the exact same columns, and in the same order.

You will need to add a column I'll call OrderX. Make this column 0 in the
query that is for the detail and 1 in the query for the sums. When you sort
the information returned by the UNION ALL query, sort by this column last.
Then the totals will come after the detail. You could also put "detail" or
"total" in a column on the same basis as "explanations" of that you are
displaying.

There may be columns in the detail that have no place in the totals query.
Just put NULL or blank values in those columns in the totals query.

What you then have may be adequate to your purposes.

Please let me know if this helped, and if you need any other assistance in
this.

Tom Ellison

 

[annysjunkmail]

Tom,

Many thanks for your further suggestion - I had not considered this
angle and it seems to be what I need.
I am off work this weekend but I return tomorrow (Monday). I'll have a
crack at it then and will let you know how I get on.
I don't think it will be a problem - just a matter of constructing the
queries and 'bob's your uncle'.

Many thanks
Tony

 

[Pat Hartman\(MVP\)]

If you are making an application for non-IT literate people, it is even more
important to use forms and reports. Users should NEVER be given queries to
work with. In this case, the query won't be updatable so they won't be able
to do any damage with it but queries have no events so there is no place to
put edit code. Using a form gives you much more control over data entry
and using a report eliminates the need to make complicated nested union
queries. You can just add controls to the report footer or to group footers
if you want intermediate sums.

 

[annysjunkmail]

Yes Pat,
I agree and accept your point but we need the information for copying
and pasting into other reports.
Also, I inherited this 'reporting database' (which provides networked
access only to tables and queries only) so we don't have the time and
resources to design forms and reports. It is much easier and faster if
we run queries and then use Excel to pivot the information. This is a
regular request and I was looking at ways of speeding it up to make
life easier for all concerened.

Tony

 

[annysjunkmail]

Hi Tom,

Sorry had a busy day in work yesterday and never got a chance to try
out your suggestion until today.
I must admit I got a bit lost with the Union query method (I did have a
go at it) but I couldn't crack it.
Anyhow, I achieved the result by constructing 3 queries, sum of
totalawards, sum of total payments; and joining them together. I have
pasted their SQL below.

I would like to know is this the most efficient method for performing
this calculation.
You can see why I wanted to do it all in 1 query - this will difficult
in teaching my staff this.
Thanks for your help - much appreciated.

Thanks
Tony


Qry1 Total Awards

SELECT tblAwards.AwardID, Sum(tblAwards.Award) AS SumOfAward
FROM tblAwards
GROUP BY tblAwards.AwardID;

Qry2 Total Payments

SELECT tblPayments.AwardID, Sum(tblPayments.PaymentAmount) AS
SumOfPaymentAmount
FROM tblPayments
GROUP BY tblPayments.AwardID;

Qry3 Total Balance

SELECT qryTotalAwards.AwardID, qryTotalAwards.SumOfAward,
qryTotalPayments.SumOfPaymentAmount, [SumOfAward]-[SumOfPaymentAmount]
AS Balance
FROM qryTotalAwards INNER JOIN qryTotalPayments ON
qryTotalAwards.AwardID = qryTotalPayments.AwardID;