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
coperations07Asked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.