Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Question Unanswered: Reporting sum on groups.

    My report groups:
    Campaign
    District

    Basic info:
    This is a mailing tracking database. We mail letters out and track results. The main form contains a field called "MailListSize" (number of letters sent), there is a subform that contains the results which can rusult in multimple entries (one-to-many with the main form), such as "appointment set", "contact management entered"...

    What I need:
    On the report I want a total for the campaign, by district of, the number of letters sent. Then I want a grand total of the letters sent.

    The problem:
    It seems the number of letters sent is being counted multiple times. The query results display a duplicate main form result for every subform entry. So if one main form entry contains two subform entries, the letters sent is counted twice when trying to sum the results in the report. This all seems to be a result of the one-to-many relationship of the two forms that are entered into.

    I thought grouping would solve the problem, but I can see to find the right grouping combination to get the results I need.

    Since I can't upload attachments, here is a link to a sample database...
    http://mygobles.com/DatabaseSample/New_MSRSample.mdb

    Any help would be appreciated!
    Last edited by Dangoble; 08-04-11 at 15:29.

  2. #2
    Join Date
    Aug 2011
    Posts
    4

    Bump...

    Bump...

    Any ideas?

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For security reasons please post your sample database here, not on an external site.
    Have a nice day!

  4. #4
    Join Date
    Aug 2011
    Posts
    4
    Uploaded as requested...thank you!
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Thank you! I'll have a look at it.
    Have a nice day!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the query SampleQuery the condition is:
    Code:
    WHERE (tblMSRequest.MSRID=11556) Or 
          (tblMSRequest.MSRID=11575) Or
          (tblMSRequest.MSRID=11576);
    Using GROUP BY and HAVING does not change anything here because there are only 3 rows in the table that fulfill the condition (i.e. there is nothing to group by at that level and the conditions concern the same table as the data column MailListSize - or if you prefer: both the condition column [MSRID] and the data column [MailListSize] resides in the same table). A simple query with those conditions:
    Code:
    SELECT tblMSRequest.MSRID, 
           tblMSRequest.MailListSize
      FROM tblMSRequest
    WHERE (tblMSRequest.MSRID=11556) Or 
          (tblMSRequest.MSRID=11575) Or
          (tblMSRequest.MSRID=11576);
    returns 3 rows:
    Code:
    MSRID	MailListSize
    11556	1000
    11575	750
    11576	1000
    These rows are on the Many size of a relationship in your query (through the link table tblEmployee), so it seems logical that they appear more than once in the resulting data set.

    When I open the report SampleReport, I don't see any duplicated value in the totals: Hawaï 750 (MSRID 11575) and 1000 (MSRID 11576), South Coast 1000 (MSRID 11556). What did I miss in your explanations?
    Have a nice day!

  7. #7
    Join Date
    Aug 2011
    Posts
    4
    Thanks again for all the help!

    If you open the report, you will see I have everything except the group footers which I would like to contain the totals. I would like totals by "Campaign", then by "District" and lastley for "SalesQTY" and "SalesDollar".

    I would like to total the "MailListSize" by "Campaign" (result should be 2,750 in my sample report) and then by District (Hawaii should be 1,750 and South Coast should be 1,000). Then I would like similar totals for "SalesQTY" and "SalesDollar" for each "tracking item".

    I hope I have presented my problem clearer.

Posting Permissions

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