Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Storing a Sum (A2K SR-1)

    I'm developing a Purchasing database. Purchasing information is collected in 2 tables - one table holds the basic information related to a purchase (Table A) and the other table holds the line item information for each purchase (Table [img]/forums/images/smilies/cool.gif[/img]. Table B can contain multiple line items for each purchase and each table has it's own form. The form for Table B has a 'header' section that shows basic purchase information from, and is populated by a query of, Table A and a subform, tied to Table B, into which the line item data is entered. The primary key is the Purchase Requisition number. In Table A, I would like to store the sum of all the line item values in Table B associated with a particular purchase requisition number. I have a field in Table A for that purpose. Unfortunately, after completing the entry of the line items in the Table B form, I can't get the sum of those line items into Table A. The form for Table B contains an unbound control that presents the sum of the line item values, but I can't 'transfer' that sum into Table A. I realize this is confusing, but I'm hoping someone can help. Thank you.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Storing a Sum (A2K SR-1)

    You should really think twice before storing any calculated value like this. This violates one of the basic Rules of Normalization. The reasoning is simple, you can NEVER be 100% sure that your calculated field will be correct. It is far better to get the total when you need it in a query. This is not to say that sometimes you will store such information, just be sure you have a very, very good reason.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing a Sum (A2K SR-1)

    Mark,

    As usual, you provided good advice. After I read your response, I figured out how to write a query to get the information and my problem is solved. Thanks, again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •