I have an 'Attendees' table that has the company name and room price. I
need a txtTotal field to calculate all the Room Prices in attendees
where company=current record on the form. I tried adding the following
in the Control Source of the textbox:

=DSum("[Room Price]","Attendees","[Company]") - which gives me a some
of all room price in the attendees table, as does:

=DSum("[Room Price]","Attendees","[Company]=[Form]![Company]")

how do I just get the company for the current form I am on??

Thanks in advance,
Lisa

Lisa,
Try
=DSum("[Room Price]","Attendees","[Company] = [Forms]![frmYourFormName]![Company]")

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have an 'Attendees' table that has the company name and room price. I
> need a txtTotal field to calculate all the Room Prices in attendees
> where company=current record on the form. I tried adding the following
> in the Control Source of the textbox:
>
> =DSum("[Room Price]","Attendees","[Company]") - which gives me a some
> of all room price in the attendees table, as does:
>
> =DSum("[Room Price]","Attendees","[Company]=[Form]![Company]")
>
> how do I just get the company for the current form I am on??
>
> Thanks in advance,
> Lisa
>

Al,
Ok I tried
=DSum("[Room Price]","Attendees","[Company] =
[Forms]![Registrations]![Company]") and it gave me a sum of all prices

I tried:
=DSum("[Room Price]","Attendees","[Company] =
[Forms]![frmRegistrations]![Company]") and it gave me a #Error

I tried:
=DSum("[Room Price]","Attendees","[Company] =
[Forms]![frmForm]![Company]")and it gave me a #Error

any idea?

Lisa

Lisa,
If there is more than one room price associated with a Company, then you will get the
sum of all those prices.
> =DSum("[Room Price]","Attendees","[Company] =
> [Forms]![Registrations]![Company]") and it gave me a sum of all prices
Well, that's what your asking for with your DSum...
If there are 4 rooms rented to XYZ company, your DSum will add up the price for all 4
rooms.

Give us some example data... what you have vs. what you want to extract from data, with
examples.

(A minor point... please don't delete the previous posts from the thread. That way
anyone can see the flow of the problem, and what steps have been covered already.)
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Al,
> Ok I tried
> =DSum("[Room Price]","Attendees","[Company] =
> [Forms]![Registrations]![Company]") and it gave me a sum of all prices
>
> I tried:
> =DSum("[Room Price]","Attendees","[Company] =
> [Forms]![frmRegistrations]![Company]") and it gave me a #Error
>
> I tried:
> =DSum("[Room Price]","Attendees","[Company] =
> [Forms]![frmForm]![Company]")and it gave me a #Error
>
> any idea?
>
> Lisa
>

Al,
here is sample date in table 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 form -
the txtTotal be $930. Then when we click the next record button (which
is Americheer) the txtTotal = $700.

Make sense?
Lisa




Al Campagna wrote:
> Lisa,
> If there is more than one room price associated with a Company, then you will get the
> sum of all those prices.
> > =DSum("[Room Price]","Attendees","[Company] =
> > [Forms]![Registrations]![Company]") and it gave me a sum of all prices
> Well, that's what your asking for with your DSum...
> If there are 4 rooms rented to XYZ company, your DSum will add up the price for all 4
> rooms.
>
> Give us some example data... what you have vs. what you want to extract from data, with
> examples.
>
> (A minor point... please don't delete the previous posts from the thread. That way
> anyone can see the flow of the problem, and what steps have been covered already.)
> --
> hth
> Al Campagna
> Candia Computer Consulting - Candia NH
> http://home.comcast.net/~cccsolutions
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Al,
> > Ok I tried
> > =DSum("[Room Price]","Attendees","[Company] =
> > [Forms]![Registrations]![Company]") and it gave me a sum of all prices
> >
> > I tried:
> > =DSum("[Room Price]","Attendees","[Company] =
> > [Forms]![frmRegistrations]![Company]") and it gave me a #Error
> >
> > I tried:
> > =DSum("[Room Price]","Attendees","[Company] =
> > [Forms]![frmForm]![Company]")and it gave me a #Error
> >
> > any idea?
> >
> > Lisa
> >

Lisa,
Given that data, and given that Company is on the Main form, and it's equal to "Ohio
Health",
and the Main form name is Registrations then...
=DSum("[Room Price]","Attendees","[Company] = [Forms]![Registrations]![Company]")
should yield $930. (I tested)

You wrote...
>> > Ok I tried
>> > =DSum("[Room Price]","Attendees","[Company] =
>> > [Forms]![Registrations]![Company]") ** and it gave me a sum of all prices **
In your example data, would that mean it returned $1630? If so then make sure you...
1. Use the exact NAME of the form
2. Use the exact NAME of the text control that contains the Company value.

You wrote...
> Company Name Room Price(this depends on what package they picked)

Are you saying that the total you want for Ohio Health may depend on a particular
Package?

Are you picking up some other Ohio Health Room Prices in your sum, or are other
company totals being included?

If not, and all is as you indicated, then the DSum above is correct for total Room
Price against Ohio Health.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Al,
> here is sample date in table 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 form -
> the txtTotal be $930. Then when we click the next record button (which
> is Americheer) the txtTotal = $700.
>
> Make sense?
> Lisa
>
>
>
>
> Al Campagna wrote:
>> Lisa,
>> If there is more than one room price associated with a Company, then you will get
>> the
>> sum of all those prices.
>> > =DSum("[Room Price]","Attendees","[Company] =
>> > [Forms]![Registrations]![Company]") and it gave me a sum of all prices
>> Well, that's what your asking for with your DSum...
>> If there are 4 rooms rented to XYZ company, your DSum will add up the price for all
>> 4
>> rooms.
>>
>> Give us some example data... what you have vs. what you want to extract from data,
>> with
>> examples.
>>
>> (A minor point... please don't delete the previous posts from the thread. That way
>> anyone can see the flow of the problem, and what steps have been covered already.)
>> --
>> hth
>> Al Campagna
>> Candia Computer Consulting - Candia NH
>> http://home.comcast.net/~cccsolutions
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Al,
>> > Ok I tried
>> > =DSum("[Room Price]","Attendees","[Company] =
>> > [Forms]![Registrations]![Company]") and it gave me a sum of all prices
>> >
>> > I tried:
>> > =DSum("[Room Price]","Attendees","[Company] =
>> > [Forms]![frmRegistrations]![Company]") and it gave me a #Error
>> >
>> > I tried:
>> > =DSum("[Room Price]","Attendees","[Company] =
>> > [Forms]![frmForm]![Company]")and it gave me a #Error
>> >
>> > any idea?
>> >
>> > Lisa
>> >
>

You wrote:

> In your example data, would that mean it returned $1630? If so then make sure you...
> 1. Use the exact NAME of the form
> 2. Use the exact NAME of the text control that contains the Company value.

Ok, the name of the form is Registrations and the name of the txt field
on the form is Company (from the Registrations Table) This is why I am
at a loss.

>
> You wrote...
> Are you saying that the total you want for Ohio Health may depend on a particular
> Package?

This is hard to explain via this medium but the Registration form has
an Attendees sub form. In that form, there's a drop down for packages
(among other stuff). When selected, it enteres the company name (from
registrations) and room price in the attendees table for that record.
The reason I need this to total on the regsitration form and not the
sub form is because another sub form in getting prices for something
else (and putting it in a seperate table) The client wants all these
totals to sum on the registration form (broken out by type). Therefore
my total on the form looks like:

Total Deposits (This is a seperate table)
Discounts - (this is entered manually)
Packages + (this is the Attendees Table)

Total Amount: (this will be the total of everything)


You wrote:
> If not, and all is as you indicated, then the DSum above is correct for total Room
> Price against Ohio Health.

I agree - that's why I have no idea why it's not working

~L~

> --
> hth
> Al Campagna
> Candia Computer Consulting - Candia NH
> http://home.comcast.net/~cccsolutions
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Al,
> > here is sample date in table 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 form -
> > the txtTotal be $930. Then when we click the next record button (which
> > is Americheer) the txtTotal = $700.
> >
> > Make sense?
> > Lisa
> >
> >
> >
> >
> > Al Campagna wrote:
> >> Lisa,
> >> If there is more than one room price associated with a Company, then you will get
> >> the
> >> sum of all those prices.
> >> > =DSum("[Room Price]","Attendees","[Company] =
> >> > [Forms]![Registrations]![Company]") and it gave me a sum of all prices
> >> Well, that's what your asking for with your DSum...
> >> If there are 4 rooms rented to XYZ company, your DSum will add up the price for all
> >> 4
> >> rooms.
> >>
> >> Give us some example data... what you have vs. what you want to extract from data,
> >> with
> >> examples.
> >>
> >> (A minor point... please don't delete the previous posts from the thread. That way
> >> anyone can see the flow of the problem, and what steps have been covered already.)
> >> --
> >> hth
> >> Al Campagna
> >> Candia Computer Consulting - Candia NH
> >> http://home.comcast.net/~cccsolutions
> >>
> >> <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Al,
> >> > Ok I tried
> >> > =DSum("[Room Price]","Attendees","[Company] =
> >> > [Forms]![Registrations]![Company]") and it gave me a sum of all prices
> >> >
> >> > I tried:
> >> > =DSum("[Room Price]","Attendees","[Company] =
> >> > [Forms]![frmRegistrations]![Company]") and it gave me a #Error
> >> >
> >> > I tried:
> >> > =DSum("[Room Price]","Attendees","[Company] =
> >> > [Forms]![frmForm]![Company]")and it gave me a #Error
> >> >
> >> > any idea?
> >> >
> >> > Lisa
> >> >
> >

Lisa,
We're just going in circles here...
There are only two possibilities here. Either you're not expressing what you need from
the Dsum properly, or there is some misconception about where the DSum is trying to get
the values it needs to return the proper sum value.

One more try... please answer specifically...
1. Is the calculated field (the DSum) on the Main Form? (I assume yes) What is that
field's Name?
2. Is Company field on the Main form, or the Subform form?
3. The Main form is named Registrations. If Company is on the subform, what is the
subform Name?
4. Given the sample data you provided would you expect the DSum you have now to return
$1630
rather than the correct $930.
5. Is the DSum you're looking for independent of any Package differences? Yes or No.

Next to Last resort... if I still have questions after the above are answered.
Can you send me the .mdb? (front and backend if needed/confidentiality assured/no
charge)
1. Zip the file/s and send using my web Contact address. (address below-must be
10MB or less)
2. Include "Newsgroup" in your subject.
3. Indicate what Access version you're using..
4. Post back here that you have/ have not sent the file.
Last Resort... if sending not possible...
A completely new post.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You wrote:
>
>> In your example data, would that mean it returned $1630? If so then make sure
>> you...
>> 1. Use the exact NAME of the form
>> 2. Use the exact NAME of the text control that contains the Company value.
>
> Ok, the name of the form is Registrations and the name of the txt field
> on the form is Company (from the Registrations Table) This is why I am
> at a loss.
>
>>
>> You wrote...
>> Are you saying that the total you want for Ohio Health may depend on a particular
>> Package?
>
> This is hard to explain via this medium but the Registration form has
> an Attendees sub form. In that form, there's a drop down for packages
> (among other stuff). When selected, it enteres the company name (from
> registrations) and room price in the attendees table for that record.
> The reason I need this to total on the regsitration form and not the
> sub form is because another sub form in getting prices for something
> else (and putting it in a seperate table) The client wants all these
> totals to sum on the registration form (broken out by type). Therefore
> my total on the form looks like:
>
> Total Deposits (This is a seperate table)
> Discounts - (this is entered manually)
> Packages + (this is the Attendees Table)
>
> Total Amount: (this will be the total of everything)
>
>
> You wrote:
>> If not, and all is as you indicated, then the DSum above is correct for total Room
>> Price against Ohio Health.
>
> I agree - that's why I have no idea why it's not working
>
> ~L~
>
>> --
>> hth
>> Al Campagna
>> Candia Computer Consulting - Candia NH
>> http://home.comcast.net/~cccsolutions
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Al,
>> > here is sample date in table 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 form -
>> > the txtTotal be $930. Then when we click the next record button (which
>> > is Americheer) the txtTotal = $700.
>> >
>> > Make sense?
>> > Lisa
>> >
>> >
>> >
>> >
>> > Al Campagna wrote:
>> >> Lisa,
>> >> If there is more than one room price associated with a Company, then you will get
>> >> the
>> >> sum of all those prices.
>> >> > =DSum("[Room Price]","Attendees","[Company] =
>> >> > [Forms]![Registrations]![Company]") and it gave me a sum of all prices
>> >> Well, that's what your asking for with your DSum...
>> >> If there are 4 rooms rented to XYZ company, your DSum will add up the price for
>> >> all
>> >> 4
>> >> rooms.
>> >>
>> >> Give us some example data... what you have vs. what you want to extract from
>> >> data,
>> >> with
>> >> examples.
>> >>
>> >> (A minor point... please don't delete the previous posts from the thread. That
>> >> way
>> >> anyone can see the flow of the problem, and what steps have been covered already.)
>> >> --
>> >> hth
>> >> Al Campagna
>> >> Candia Computer Consulting - Candia NH
>> >> http://home.comcast.net/~cccsolutions
>> >>
>> >> <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > Al,
>> >> > Ok I tried
>> >> > =DSum("[Room Price]","Attendees","[Company] =
>> >> > [Forms]![Registrations]![Company]") and it gave me a sum of all prices
>> >> >
>> >> > I tried:
>> >> > =DSum("[Room Price]","Attendees","[Company] =
>> >> > [Forms]![frmRegistrations]![Company]") and it gave me a #Error
>> >> >
>> >> > I tried:
>> >> > =DSum("[Room Price]","Attendees","[Company] =
>> >> > [Forms]![frmForm]![Company]")and it gave me a #Error
>> >> >
>> >> > any idea?
>> >> >
>> >> > Lisa
>> >> >
>> >
>

Al,
First of all I want to thank you for your patience - I know this is
becoming frustrating and I appreciate you trying to help me figure this
out. Here are the answers to your question:

> 1. Is the calculated field (the DSum) on the Main Form? (I assume yes) What is that
> field's Name?
Yes. The main form is named Registrations. The text field that I am
using on the main form is txtPackageTotal (and I'm putting the DSum in
it's Control Source)

> 2. Is Company field on the Main form, or the Subform form?
Registrations has a textbox named Company. The sub form (Attendees)
does not have a textbox named Company - but there is a package drop
down that adds the company name along with the room price (from the
package table) into the Attendees Table.

> 3. The Main form is named Registrations. If Company is on the subform, what is the
> subform Name?
see above

> 4. Given the sample data you provided would you expect the DSum you have now to return
> $1630 rather than the correct $930.
No, this is what it is doing now - what I need is If I have two records
- Ohio Health & Americheer. When I am in the first record (Ohio Health)
of the Registration form - the txtPackageTotal should be $930. If I go
to the next record (Americheer), txtPackageTotal should be $700.

> 5. Is the DSum you're looking for independent of any Package differences? Yes or No.
No. There is a different price per package - but the Attendees table
just has the price from the package they picked.

So, in other words:
Main Form=Registrations (Registrations Table)
Sub Form=Attendees (Attendees Table)

Main form has txtbox Company
Sub form has combobox Packages

Attendees Table has Company & Room Price
Registrations Table has Company
Package Table has Package Name & Room Price

txtPackageTotal is on the Main Form

Hope this is what you need?

~L~

Lisa,
OK... that's really clear now! We're getting there...
Everything appears OK from what you've given me so far.

Can you send the file? (see my previous instructions)

I think that would be best, otherwise I'm still guessing as to why a very simple DSum
isn't working.

**Also, on my second post I wrote...
(A minor point... please don't delete the previous posts from the thread. That way
anyone can see the flow of the problem, and what steps have been covered already.)
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Al,
> First of all I want to thank you for your patience - I know this is
> becoming frustrating and I appreciate you trying to help me figure this
> out. Here are the answers to your question:
>
>> 1. Is the calculated field (the DSum) on the Main Form? (I assume yes) What is
>> that
>> field's Name?
> Yes. The main form is named Registrations. The text field that I am
> using on the main form is txtPackageTotal (and I'm putting the DSum in
> it's Control Source)
>
>> 2. Is Company field on the Main form, or the Subform form?
> Registrations has a textbox named Company. The sub form (Attendees)
> does not have a textbox named Company - but there is a package drop
> down that adds the company name along with the room price (from the
> package table) into the Attendees Table.
>
>> 3. The Main form is named Registrations. If Company is on the subform, what is the
>> subform Name?
> see above
>
>> 4. Given the sample data you provided would you expect the DSum you have now to
>> return
>> $1630 rather than the correct $930.
> No, this is what it is doing now - what I need is If I have two records
> - Ohio Health & Americheer. When I am in the first record (Ohio Health)
> of the Registration form - the txtPackageTotal should be $930. If I go
> to the next record (Americheer), txtPackageTotal should be $700.
>
>> 5. Is the DSum you're looking for independent of any Package differences? Yes or No.
> No. There is a different price per package - but the Attendees table
> just has the price from the package they picked.
>
> So, in other words:
> Main Form=Registrations (Registrations Table)
> Sub Form=Attendees (Attendees Table)
>
> Main form has txtbox Company
> Sub form has combobox Packages
>
> Attendees Table has Company & Room Price
> Registrations Table has Company
> Package Table has Package Name & Room Price
>
> txtPackageTotal is on the Main Form
>
> Hope this is what you need?
>
> ~L~
>