Totals in Datasheet View only shows COUNT

[nypedestrian]

Hi! I am redoing my current employer's database (selling collectible coins). In the OrderDetails form (Datasheet view) I created an unbound field to compute Qty*Unit Price per Item ordered. I would ideally like to use Access 2007's Totals option to get the total of the whole order but when I click on the dropdown, the only option available is COUNT. Anyone else encounter this? Thank you in advance for any help.

[ketbdnetbp]

Haven't encountered that which you have described and don't know which dropdown you are referring to "w/Count Only".

However, as an alternative, you could add another unbound textbox to the footer of the form/subform whose control source sums the first calculated control, i.e. =Sum([NameOfFirstUnboundTextbox]) which should get the amount of the total order.

Just a thought.

Jim

[nypedestrian]

Haven't encountered that which you have described and don't know which dropdown you are referring to "w/Count Only".

However, as an alternative, you could add another unbound textbox to the footer of the form/subform whose control source sums the first calculated control, i.e. =Sum([NameOfFirstUnboundTextbox]) which should get the amount of the total order.

Just a thought.

Jim

Thanks for the reply! I'm attaching a screenshot of my problem, maybe it'll be easier to understand. I tried the sum in the footer but I don't know if it will work when the form view is Datasheet.

Item Total is the unbound control that calculates Qty*UnitPrice

[rpeare]

Why would you total them in a query? If you are printing an invoice you can do the totalling on the report that prints the invoice, or if it's on a form you can perform the total on the form. Is there a specific reason why you're trying to put a total in a query?

[nypedestrian]

Why would you total them in a query? If you are printing an invoice you can do the totalling on the report that prints the invoice, or if it's on a form you can perform the total on the form. Is there a specific reason why you're trying to put a total in a query?

It's not a query. It's a form based on a table in datasheet view - not in single or continuous form. Attached is the reason why I wanted to put a total -- so that the person would know what the order total is. The other columns (Quantity & Unit Price) total properly.

[rpeare]

Ahhh gotcha.

Have you tried putting an unbound text box on your main form that is something like

=sum(forms!mainformname.subformname!currencyfield)

so let's say in your case your main form was called

ORDERINFORMATION
your subform (data sheet) was called
ORDERDETAIL
and you want to total the field
ITEMTOTAL

your formula would be

=sum(forms!orderinformation.orderdetail!itemtotal)

I haven't tested as I rarely use forms/subforms but it should give you what you want

[nypedestrian]

Ahhh gotcha.

Have you tried putting an unbound text box on your main form that is something like

=sum(forms!mainformname.subformname!currencyfield)

so let's say in your case your main form was called

ORDERINFORMATION
your subform (data sheet) was called
ORDERDETAIL
and you want to total the field
ITEMTOTAL

your formula would be

=sum(forms!orderinformation.orderdetail!itemtotal)

I haven't tested as I rarely use forms/subforms but it should give you what you want

Didn't really want the total of the items to be a part of the main form. I found this on another forum

http://www.access-programmers.co.uk/...d.php?t=201438

Looks like I will have to resort to a query.