Summing by group?

[DanG]

Hello,
I should know how to do this by now, but reporting is one of my weak spots

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.

Any help would be appreciated!

 

[Beetle]

Can you upload a copy of your db with just some bogus data, so we can get a better idea of what you've got going on?

 

[cooh23]

Create a footer for both groups to get the count.

Since you want to group it by district, you would want to create the header for District first and then by Campaign.

To get the grand total, create a field to calculate the letters sent in the Report Footer.

Your report should look like this:

District Header
--Campaign Header
---Campaign Footer
--District Footer
Report Footer

 

[DanG]

Thanks for the help guys!

I have tried the structure as suggested with no change in results

I have attached a sample database to make it easier as suggested.
If you run the report you will see what I mean I hope!

I will also want to total the "SalesQTY" and the "SalesDollar" fields by group if it makes a difference.

Note: I alos need to query on the "StartDate" & "CampaignID" field, which is way I didn't sum the SalesDollar and SalesQTY fields to make the results show as one line in the query.


Thanks again

 

[cooh23]

Thanks for the help guys!

I have tried the structure as suggested with no change in results

I have attached a sample database to make it easier as suggested.
If you run the report you will see what I mean I hope!

I will also want to total the "SalesQTY" and the "SalesDollar" fields by group if it makes a difference.

Note: I alos need to query on the "StartDate" & "CampaignID" field, which is way I didn't sum the SalesDollar and SalesQTY fields to make the results show as one line in the query.


Thanks again

I can't seem to open your file. Can you reupload without zipping the file?

 

[DanG]

Here you go...

 

[cooh23]

Here you go...

I looked at your file and I guess the reason why you're getting multiple results is that your district is not linked anywhere in the MSRequest. So I added the district there somewhere. The reason for this is because you want to identify which districts it was sent to and what size.

So in the table you should have identified the following:
- What is the region?
- What district?
- Mailsize
- Date
- Sale - You have sales attached to this but i'm not sure how or where it should go.

Look at the new query I created.If you look at your original tables, district is not linked to your main table so your main table doesn't know where to look.

The report is now divided by campaign and district with a total for each campaign, district and a grand total.


I will have very limited access to my computer in the next few days so I won't be able to reply to any of your questions but I will do so once I get back. Probably around Sunday. Good luck and I hope this helped.

John

 

[DanG]

Thank you John,

I don't think that works. While your report shows the correct total there are not totals for the "SalesQTY" and "SalesDollar". Thes feilds are the "many" part of the one-to-many relationship which should show up in the query results and also causes multiple results in the query. There are other concerns with your solution as well.

The District in my method is derived from the the "AdvisorNum" in the main table. AdvisorNum is the advisor number which uses the "tblEmployee" table to capture the advisor details (I am only showing txtEmpNum and txtDistFk fields and have deleted the advisor name and other details for obviouse reasons). Part of the advisor detail is the Distrct which is the txtDistNum field which links to tblDistLU, which provides all the detail about the district the advisor is assigned to. So in the end I don't think I need to have a distrct field in the MSRequest table because that information is captured through the "AdvisorNum" in the MSRequest table.

I hope this makes sense

Thanks for the help!

 

[DanG]

OK, I'm going to start over here and try to make it more simple...

I have uploaded a new database for review and I think it will make it easier to relay what I need, please see attached.

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 "District" and "Campaign"

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

I hope I have presented my problem clearer.

Thanks for the help!

 

[DanG]

Bump...
any help would be appreciated

 

[DanG]

I have been a member here since 2004 and have received help and given help as well.

This is the first time my plea for help has gone unanswered

Thanks anyway.