Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Posts
    78

    Unanswered: Calculate YTD totals

    I am helping a friend of mine with a database. OS = XP, Office 2K

    Her organization received donations.

    Currently there are two tables.
    tblSupporters
    tblDonations.

    Donations is a subform in the supporters form.

    As a single donor can give many donations she would like a YTD total on the form for reference.

    tblSupporters is related to the tblDonations by the mailingAddressID field.

    Can someone clue me in to this calculation in the control or do you need further information.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do you want your total in the subform or in the main form?

    In the main form, you can create a control and set its source to the DSUM() function.

    In the subform, you can place a Summary total in either the header or the footer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2002
    Posts
    78
    Originally posted by blindman
    Do you want your total in the subform or in the main form?

    In the main form, you can create a control and set its source to the DSUM() function.

    In the subform, you can place a Summary total in either the header or the footer.
    Thank you for the information. . . I will give it a shot. Probably the Mainform is where the control should be but will discuss it with my friend.

  4. #4
    Join Date
    Aug 2002
    Posts
    78
    Ok I am not suer why I can't figure this out, howver I have never had to SUM things, Please pardon me.

    Table Set up

    tblSupporters
    SupporterID
    Prefix
    FName
    LastName
    Address
    City
    State
    Zip

    etc

    tbl Donations
    MailingListID (Rekated to SupporterID)
    Donation ID
    Current Donation
    Date
    Receipt Sent


    I have a mainform of Supporter information with sub form of Donation
    I would like a text box on the Mainform which will calculate the total donations for that particular person active in the main form. Can someone give me a hand?

    It is muchly appreciated.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Just create a new control box on your form and set it's datasource to:

    SUM([YourField])
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2002
    Posts
    78
    Originally posted by blindman
    Just create a new control box on your form and set it's datasource to:

    SUM([YourField])
    Thank you will give that a shot

  7. #7
    Join Date
    Mar 2004
    Posts
    82
    Hi,

    I've been following this discussion and had requested something similar earlier.

    To recap, I have queries that has data of contracts with customers, each starting at some date. I also have some statistic on these contracts (sales of products by month).

    My report can group and summarize by QTD, YTD across all customers - but this summary uses the calendar year quarter.

    I have been trying to get a summary by customer and QTD/YTD, defined by their respective contract StartDate. If a contract starts in May, <its> quarter would be as follows: Q1: May-July; Q2: Aug-Oct; Q3: Nov-Jan; Q4: Feb-Apr. Each contract StartDate, thus, should have its own defined quarter and the summary would give sales aggregation for every three months from its StartDate.

    Short of writing extensive code, is there a way out? I've tried IIF(), SWITCH(), etc but can't seem to crack this one.

    Thanks,

    Jay

Posting Permissions

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