Sum of group with criteria

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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?

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
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

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

If you need a summary,
Right click the field
Put it in the group1 footer

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.