• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Sum of group with criteria

Hi,
I'm using Visual Studio 2005

I've attached a print out of the report I'm working on.  I would like to have totals of the shifts(for the day) below the totals for the day.  I've tried several things(add a group, add a subreport, summary field...) and have struck out so far.  I have a group #1 for the date and a group #2 for the Sorter(machine).  Then I have the Sums for the day showing in the group #1 footer. I also want the totals for each shift to show here.  What can I try to accomplish this?

Thanks,
Dave
crPrint.pdf
0
coperations07
Asked:
coperations07
  • 3
  • 2
1 Solution
 
mlmccCommented:
The easiest way to accomplish this is to use a subreport that just produces the summary you want.

Another way would be to use variables for each shift.

Can you show what you want the totals per shift to look like?

mlmcc
0
 
coperations07Author Commented:
Something like this for each day/page....

Totals      100,000      500      400      50      2500      96,550
Shift 1      50,000      400      100      20      1500      47,980
Shift 2      30,000      100      100      10      500      29,290
Shift 3      20,000      0      200      20      500      19,280
0
 
mlmccCommented:
Are the number of columns fixed?

Unless you use a subreport to get it you will need to create formulas for each total for display and calculation.

Do the columns change between runs?

One away to do this is to create formulas like
If ({ShiftField} = 1) then
    {ValueField}
Else
    0

You can then summarize the formula in the report footer.

You would need a formula like above for each total field then a summary field to display them.

mlmcc
0
 
coperations07Author Commented:
The number of columns is fixed.  The column names don't change...

I'm trying to get the summary in the group1 footer (date)

There's  a query on an Access database that I'm using as the datasource.  I included it in the code portion below.
SELECT tbl_sorter_perf.Date, Max(tbl_sorter_perf.Shift_Feed) AS MaxOfShift_Feed, Max(tbl_sorter_perf.Shift_Excess) AS MaxOfShift_Excess, Max(tbl_sorter_perf.Shift_All_Drop) AS MaxOfShift_All_Drop, Max(tbl_sorter_perf.Shift_No_Read) AS MaxOfShift_No_Read, Max(tbl_sorter_perf.Shift_Blk_Cht) AS MaxOfShift_Blk_Cht, Max(tbl_sorter_perf.Shift_Net) AS MaxOfShift_Net, tbl_sorter_perf.SORTID, tbl_sorter_perf.Shift
FROM tbl_sorter_perf
GROUP BY tbl_sorter_perf.Date, tbl_sorter_perf.SORTID, tbl_sorter_perf.Shift;

Open in new window

0
 
mlmccCommented:
If you need a summary,
Right click the field
Click INSERT --> SUMMARY
Put it in the group1 footer

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now