sum of union queries

G

Guest

Hello everyone,

I seem to have backed myself into a corner here, ive got a form called
ReceiptForm which asks the user what a customer can be charged for from a
drop down list. The selection opens a popup form that the user can fill in
and save and each form is based on a table for that type of payment, eg
deposits popup form is based on the deposits table etc, there are 5 different
payments.

To find out the total of what is owed for that receipt, i have devised a
Union query to list the different amounts that are paid in called
PaymentList, this is shown in a listbox on the main ReceiptForm so the user
can view the different forms that have been filled in. What i need to do is
get the total and put it into a text box in the footer of the main
ReceiptForm called [totalcharges].

I have a query called PaymentlistSum which works as follows:

SELECT DISTINCTROW Sum([PaymentList].[Amount]) AS [Sum Of Amount]
FROM PaymentList;

but how do i put this total into a text box??? Also when do i do it, ideally
id like to update the value everytime i submit a new payment. The listbox is
updated everytime a popup form is closed and works well, i cant use the
afterupdate on the listbox as its updated programatically but that would be
ideal!

With much thanks

form a stumped Amit
 
M

Marshall Barton

DowningDevelopments said:
I seem to have backed myself into a corner here, ive got a form called
ReceiptForm which asks the user what a customer can be charged for from a
drop down list. The selection opens a popup form that the user can fill in
and save and each form is based on a table for that type of payment, eg
deposits popup form is based on the deposits table etc, there are 5 different
payments.

To find out the total of what is owed for that receipt, i have devised a
Union query to list the different amounts that are paid in called
PaymentList, this is shown in a listbox on the main ReceiptForm so the user
can view the different forms that have been filled in. What i need to do is
get the total and put it into a text box in the footer of the main
ReceiptForm called [totalcharges].

I have a query called PaymentlistSum which works as follows:

SELECT DISTINCTROW Sum([PaymentList].[Amount]) AS [Sum Of Amount]
FROM PaymentList;

but how do i put this total into a text box??? Also when do i do it, ideally
id like to update the value everytime i submit a new payment. The listbox is
updated everytime a popup form is closed and works well, i cant use the
afterupdate on the listbox as its updated programatically but that would be
ideal!


Get rid of that query and use an expression in the text box.
I can't be sure at this point, but from what you've said so
far I think this will do it:

=DSum("Amount", "PaymentList")

You didn't say how the list box is "updated", but however
you're doing it, the next thing would be to requery the text
box:

ReceiptForm.totalcharges.Requery
 
G

Guest

Hi Marshall,

Thanks that makes a lot of sense but Im a little confused, by changing the
textbox to an expression would this alter the control source? the value has
to be stored in the receipt as its the final all important total! I've been
looking for a way to change it without changing the control source and i was
thinking of using VBA code to do it, or use that expression as the control
source of another text box and then reference it from the [totalcharges] text
box using VBA code.

or are you suggesting something else?

Again thanks for looking at this problem its really appreciated
 
M

Marshall Barton

DowningDevelopments said:
Thanks that makes a lot of sense but Im a little confused, by changing the
textbox to an expression would this alter the control source? the value has
to be stored in the receipt as its the final all important total! I've been
looking for a way to change it without changing the control source and i was
thinking of using VBA code to do it, or use that expression as the control
source of another text box and then reference it from the [totalcharges] text
box using VBA code.

or are you suggesting something else?


I hadn't realized that you wanted to save that total in a
records. If I had realized it, I would have argued that you
should not do that. The discussion goes along the lines of:
any value that can be calculated from other saved values
should never be saved. The reason is that it violates the
fundamental rules of relational databases (look up Normal
Forms and Normalization). Violating the rules can lead to
inconsistent data where your saved total may not equal the
sum of the values. I.E. changing one piece of data would
require you to also change other data, which is impossible
to guarantee actually happens. The "correct" way to keep
your data consistent is to recalculate the total every time
you need to display it.

If you still feel that you must take the risk of
inconsistent data, then you can use something like the DSum
I posted earlier in a VBA procedure to set the value of the
text box.
 
G

Guest

Yeah id figured out using the Dsum in a vba statement earlier but about why
this value has to be stored.

This part of the database is an accountacy section and the transactions have
to be saved hence even though i can use calculated values in some places my
client wishes to be able to follow the financial trail of any tenant. I just
realised that i could do this if i start factoring in date calculations into
future queries but it complicates things for my client if they cant see a
list of reports in front of them.

Id have to change my table structure a little to accomodate that but i could
still do it.

Ill have to talk to my client about it.

Once again many thank you for your suggestions and help on this,

Amit
 
M

Marshall Barton

DowningDevelopments said:
Yeah id figured out using the Dsum in a vba statement earlier but about why
this value has to be stored.

This part of the database is an accountacy section and the transactions have
to be saved hence even though i can use calculated values in some places my
client wishes to be able to follow the financial trail of any tenant. I just
realised that i could do this if i start factoring in date calculations into
future queries but it complicates things for my client if they cant see a
list of reports in front of them.

Id have to change my table structure a little to accomodate that but i could
still do it.

Ill have to talk to my client about it.

Once again many thank you for your suggestions and help on this,


Good luck with the client education program ;-)

If you never displayed the data outside a form or report
(where you can do the calculation on the fly), the client
would never know whether it was saved or not. At least you
would be able to guarantee that it was accurate.

If the client insists on looking a the table's sheet view,
then hide the table and give them a query instead ;-)
 
G

Guest

lol,
Cheeky but do-able.

Thank you again Mr Barton, youve been really helpful on this topic.

with kind regards

Amit
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top