DSum question

[peashoe]

here is sample data in table my Attendees

Company Name Room Price(this depends on what package they picked)
Ohio Health $350
Ohio Health $450
Ohio Health $130
Americheer $300
Americheer $400

what I need is for when we are at the Ohio Health record on the
Registrations form -
the txtTotal textbox to total $930. Then when we click the next record
button (which
is Americheer) the txtTotal = $700.

I tried entering the following in the txtTotal Control source:
=DSum("[Room Price]","Attendees","[Company] =
[Forms]![Registrations]![Company]")
but it gave me a sum of all prices ($1630)

any ideas on how do get what I need?
Thanks in advance
Lisa

 

[Guest]

Try this ---
SELECT YourTable.[Company Name], Sum(YourTable.[Room Price]) AS [Total Price]
FROM YourTable
GROUP BY YourTable.[Company Name];

 

[Vinayak N]

Hi,

Why don't you try putting a simple text box in the footer of the form
and then just putting in the control source
=Sum([RoomPrice]).

Regards,
Vinayak

 

[peashoe]

Vinayak,
The form is pulling from the Registration Table. My data is in the
Attendees table so unfortunately, that will not work

~L~

 

[peashoe]

Karl,
I just tried it and got a #Name? in the textbox. One thing I forgot to
mention, the form is pulling from the Registration Table, but my data
is in the Attendees table (both have a company column) - is this why
I'm having the problem? I also assumed that you still ment for me to
put this in the Control Source of the textbox.

This is what I have:
SELECT Attendees.[Company], Sum(Attendees.[Room Price]) AS [Total
Price] FROM Attendees GROUP BY Attendees.[Company];


~L~

 

[Van T. Dinh]

Not sure but try:

=DSum("[Room Price]","Attendees",
"[Company] = """ & [Forms]![Registrations]![Company] & """")

I assumed the [Company] Field is a Text Field and the value of the Control
[Forms]![Registrations]![Company] is also of Text DataType corresponding to
the Filed [Company].

Note that if you use ComboBox or ListBox, the value of the Control may be
entirely different from what you see on the Form. If this is the case, make
sure the BoundColumn of the Control corresponds to the Field [Company]

 

[peashoe]

Van,
Ok now the pop-up window appears and asks me for the Company, then the
package -and if I click "Cancel" the packages in my Attendees subform
are now gone. I'm guessing my problem is the form is pulling from
Registrations, but the info I need is from a subform Attendees, but
needs to be displayed in the Registration form. Form looks like this:

Form: Registrations (pulling from table Registrations)
Company
Address
Phone #, etc

there are several sub forms in the main form.

Sub Form: Attendees within Registration Form (pulling from Attendees
table)
Contact Name, Check-In Date, Arrival Date, Package, Extra Days,
etc...for as many people that are coming to the event

package is a drop down pulling from a package table that has the name
and the room price, when they select a package, it enters the room
price in the Attendees Table.

Then on the Registration form, there is a Finance section that has a
total of all the sub forms:
Total Deposit
Total Discounts
Total Packages (which is the one that is not working)

Total Amount Owed

nothing I try works. I keep getting a total of all the records in the
Attendees table, not just the one for the current company.

IM at a loss