Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Calculated fields in Access


  • Please log in to reply
4 replies to this topic

#1 lizi

lizi

  • Members
  • 33 posts
  • OFFLINE
  •  
  • Local time:07:16 AM

Posted 28 February 2009 - 05:58 PM

Hi,

I am currently building a shop type database...

I have made a 'transaction form' On the form is things such as transaction ID, customer details, staff details etc, then there is a subform, where the products on the transaction get entered into.

On the subform (Transaction/Product link Subform), there are the fields (among others...) 'Product ID', 'Price' and 'Quantity', I have then made an unbound field to calculate the subtotal which has '=[QuantityOrdered]*[p_Price]'

On the main form, I then wanted to have a 'total' field, whereby it adds up all of the subtotals from the subform, the problem is is that through the various methods i have used i keep on getting #Error or it will only show me the subtotal of one record, when I need it to show me the total subtotals from all records.

Any solutions? please :thumbsup:

Edited by lizi, 28 February 2009 - 05:58 PM.


BC AdBot (Login to Remove)

 


#2 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,574 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:03:16 AM

Posted 04 March 2009 - 10:08 PM

... it will only show me the subtotal of one record, when I need it to show me the total subtotals from all records.


If I understand correctly what you said, I think you need to make a calculated field on the form which will say something like
=Sum(the name of the calculated field on the subform)
and put that total field in the footer of the form. The footer should contain form totals.

#3 lizi

lizi
  • Topic Starter

  • Members
  • 33 posts
  • OFFLINE
  •  
  • Local time:07:16 AM

Posted 05 March 2009 - 04:02 PM

Hey, thanks for the reply. I did do that

I had =sum([Subtotal]) on the subforms footer

Then =[Name of subform].Form![Text16] or whatever the formula is... on the main form

But it just returns #Error or #Name etc :thumbsup:

#4 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,574 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:03:16 AM

Posted 05 March 2009 - 07:29 PM

I had =sum([Subtotal]) on the subforms footer

I don't have Access in front of me. And don't recall what the subforms footer might be.
I suggested putting it on the form footer, meaning main form. Would that help?

By 'main form' I mean the one that's related to the sub-records forms in a one-to-many relation.

Post your sql query or whatever is behind that expression, perhaps we can see what is going on, though no promises :thumbsup:

Edited by tos226, 05 March 2009 - 07:31 PM.


#5 lizi

lizi
  • Topic Starter

  • Members
  • 33 posts
  • OFFLINE
  •  
  • Local time:07:16 AM

Posted 06 March 2009 - 12:05 PM

Hey again,

I figured it out earlier on today :thumbsup: But thank you for your help!

x




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users