Summing up Text Boxes that are based on expressions

[cuboulderpizzle]

Hello.

I have a little experience with Access, but not enough to make some really
cool dynamic stuff. But enough to know what I'm getting myself into.

I am an Administrative Assistant for a Conference coming up and I have a
question about summing Text Boxes that are themselves based on an expression.
I have a form that has a Text Box called [Amount Owed]. This Text Box is
dependent on multiple check boxes littered throughout my form. Here is the
code for it:

=IIf([Cancelled] Or [Replaced],"$0.00",IIf([Adult Staff Member] Or [A-Team
Member],IIf([Extended Stay] Or [Staff Training & Extended] Or [Early Staff
Training and Extended],"$105.00","$75.00"),IIf([Youth Staff
Member],IIf([Extended Stay] Or [Staff Training & Extended] Or [Early Staff
Training and Extended],"$160.00","$130.00"),IIf([Conference Staff Member] Or
[Adult Leader Free Tuition],"$0.00",IIf([Alternate Tuition]>0,[Alternate
Tuition],IIf([Early],IIf([Commuter],"$270.00",IIf(Parent!Charter,IIf([Extended
Stay],"$328.50","$301.50"),IIf([Extended
Stay],"$365.00","$335.00"))),IIf([Regular],IIf([Commuter],"$285.00",IIf(Parent!Charter,IIf([Extended
Stay],"$337.50","$310.50"),IIf([Extended
Stay],"$375.00","$345.00"))),IIf([Late],IIf([Commuter],"$320.00",IIf([Extended Stay],"$415.00","$385.00")),"Need Info"))))))))

Basically what it does is calculate how much money they owe based on the
check boxes I click.

Anyway: My problem is this. I'm trying to do a running total of these
amounts to find the total amount of money that we're bringing in. I have
tried to do a running total in a form and a report. Every time it comes up
$0.00. And I know that I have the correct values in there, but there is
something else that is troubling me. The values aren't passed to the table
that this form is based off of.

My question then is how do I do a running sum of all of my participant's
amounts that they owe and not have it come up $0.00? If I override the
amounts in the table and put in the actual amount that they owe as opposed to
using the form, then the amounts are summed up. But I have so many
participants that I want the form to do it for me.

HELP! Thanks in Advance!

~cuboulderpizzle

 

[Allen Browne]

The complexity you are struggling with arises from the fact that this is not
how you design a relational database. You need to break the data down into
related tables. It will then be a breeze to sum, compare with previous,
query payments over time, and whatever else you might want to report on.

Conference table (one record for each conference you have to book):
ConferenceID AutoNumber primary key
ConferenceDate Date/Time when the conference starts
Location
...

Client table (one record for each person):
ClientID AutoNumber primary key
Surname Text
...

FeeType table (one record for each type of fee):
FeeTypeID Text primary key
FeeAmount Currency usual current amount for this fee.

Booking table (one record for each registration):
BookingID AutoNumber primary key
ConferenceID Number what conference the person booked into
ClientID Number who is booking into this
conference
BookingDate Date/Time when the booking was received.

BookingFee table (one record for each fee associated with a booking):
BookingFeeID AutoNumber primary key
BookingID Number which booking this entry applies
to.
FeeTypeID Text which fee is being charged.
FeeAmount Currency how much for this item

For the interface, you will have a main form bound to the Booking table,
with a subform for the BookingFee table. The main form will have drop-down
boxes for selecting the conference and client. The subform will have a
drop-down box for selecting the fee type, and you can use the AfterUpdate of
that combo to look up the usual fee and offer that in the FeeAmount text
box.

It is now very simple to sum the fees for a booking.

If this seems strange, open the Northwind sample database that comes with
Access. Open the Relationships window (Tools menu.) Look at how the
Customers table relate to Orders: one customer can have many orders over
time, just as one person can attend many conferences over time. Look at the
Orders and Order Details: one order can have many line items, each with
different products. Similarly, one of your bookings can have many fees
associated, each with a different fee type.

Once that's clear, open the Orders form in Northwind, to see how the
interface looks, and how the order total is summed.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Hello.

I have a little experience with Access, but not enough to make some really
cool dynamic stuff. But enough to know what I'm getting myself into.

I am an Administrative Assistant for a Conference coming up and I have a
question about summing Text Boxes that are themselves based on an
expression.
I have a form that has a Text Box called [Amount Owed]. This Text Box is
dependent on multiple check boxes littered throughout my form. Here is the
code for it:

=IIf([Cancelled] Or [Replaced],"$0.00",IIf([Adult Staff Member] Or [A-Team
Member],IIf([Extended Stay] Or [Staff Training & Extended] Or [Early Staff
Training and Extended],"$105.00","$75.00"),IIf([Youth Staff
Member],IIf([Extended Stay] Or [Staff Training & Extended] Or [Early Staff
Training and Extended],"$160.00","$130.00"),IIf([Conference Staff Member]
Or
[Adult Leader Free Tuition],"$0.00",IIf([Alternate Tuition]>0,[Alternate
Tuition],IIf([Early],IIf([Commuter],"$270.00",IIf(Parent!Charter,IIf([Extended
Stay],"$328.50","$301.50"),IIf([Extended
Stay],"$365.00","$335.00"))),IIf([Regular],IIf([Commuter],"$285.00",IIf(Parent!Charter,IIf([Extended
Stay],"$337.50","$310.50"),IIf([Extended
Stay],"$375.00","$345.00"))),IIf([Late],IIf([Commuter],"$320.00",IIf([Extended
Stay],"$415.00","$385.00")),"Need Info"))))))))

Basically what it does is calculate how much money they owe based on the
check boxes I click.

Anyway: My problem is this. I'm trying to do a running total of these
amounts to find the total amount of money that we're bringing in. I have
tried to do a running total in a form and a report. Every time it comes up
$0.00. And I know that I have the correct values in there, but there is
something else that is troubling me. The values aren't passed to the table
that this form is based off of.

My question then is how do I do a running sum of all of my participant's
amounts that they owe and not have it come up $0.00? If I override the
amounts in the table and put in the actual amount that they owe as opposed
to
using the form, then the amounts are summed up. But I have so many
participants that I want the form to do it for me.

HELP! Thanks in Advance!

~cuboulderpizzle

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George