YTD, PTD & Week results in 1 report

[Guest]

I am trying to create a report which will show YTD, PTD & week results. I
would like the user to be able to enter the year, period & week #.

The SQL for the query which contains all the data is:

SELECT tblLOMMaster.Year, tblLOMMaster.Period, tblLOMMaster.Week,
tblLOMMaster.Dept, Sum(tblLOMMaster.MW) AS SumOfMW, Sum(tblLOMMaster.SE) AS
SumOfSE, Sum(tblLOMMaster.TotalSubsidy) AS SumOfTotalSubsidy,
Sum(tblLOMMaster.TotalIncAmt) AS SumOfTotalIncAmt, Sum(tblLOMMaster.MWINC) AS
SumOfMWINC, Sum(tblLOMMaster.SEINC) AS SumOfSEINC
FROM tblLOMMaster
GROUP BY tblLOMMaster.Year, tblLOMMaster.Period, tblLOMMaster.Week,
tblLOMMaster.Dept;

I would like the results to look similiar to:

Dept. (Grocery, Frozen, Dairy, Combined)
Current Week
Expense Income Shortfall/income
Midwest
Southeast
MWSE

Period to Date
Expense Income Shortfall/income
Midwest
Southeast
MWSE

Year to Date
Expense Income Shortfall/income
Midwest
Southeast
MWSE

I hope this is enough information. Any help would be greatly appreciated.
Thanks.

 

[Al Camp]

It seems as if you have all your data... (I don't see any Region field
indicated...Midwest, Southeast...)
If your problem is how to get it to report the way you indicated... and you have all
the corect data delivered to the report via the query...
Use the Sorting and Grouping to set it up...
Year - Asc - Header & Footer (yes)
Period - Asc - H & F (yes)
Week - Asc - H & F (yes)
Region - Asc - (Detail section)

You can then set your fields in those Headers and Footers to yield
Year
Period
Week
Region Midwest 100.00
Region SouthEast 400.00
Week Totals 500.00
Period Totals...
Year Totals...

 

[Guest]

Thanks for the response.

Almost there but still some problems.

The region field info are the summed fields: Sum(tblLOMMaster.MW) AS
SumOfMW, Sum(tblLOMMaster.SE) AS SumOfSE, Sum(tblLOMMaster.MWINC) AS
SumOfMWINC, and Sum(tblLOMMaster.SEINC) AS SumOfSEINC

Part of the problem is I don't have all the correct data going to the
report. The query contains ALL the information from my table. If I run the
report as is I get all the data (ie 2006 and 2007 records and all the weeks).
I need to limit it to a specific Year, Period and Week. However, if I use
them as parameters, I only get the Week information. What I want is, for
example, if the user selects Year 2007, Period 2, Week 2, I want the YTD to
include all of 2007, all of Period 2 and then only Week 2.

Here is an example of the data the query returns:

SumOfMW SumOfSE SumOfTotalSubsidy SumOfMWINC SumOfSEINC
SumOfTotalIncAmt Dept Year Period Week
-10295.47 -952.49 -11247.96 2520.41 700.4 3220.81 Dairy 2007 1 1
-1372.92 -105.07 -1477.99 0 2360.88 2360.88 Frozen 2007 1 1
-20312.2 -3577.8 -23890 11561.14 12811.46 24372.6 Grocery 2007 1 1
-20567.75 -1315.14 -21882.89 8476.2 0.42 8476.62 Dairy 2007 1 2
-1112.12 -95.92 -1208.04 0 0 0 Frozen 2007 1 2
-19712.25 -7447.46 -27159.71 7342.32 2678.34 10020.66 Grocery 2007 1 2
-5756.79 -450.26 -6207.05 11382.4 3511.66 14894.06 Dairy 2007 1 3
-2095.61 -2889.25 -4984.86 1279.37 3260.45 4539.82 Frozen 2007 1 3
-20690.27 -17032.57 -37722.84 35082.49 17647.4 52729.89 Grocery 2007 1 3

Hope I made this clear.

 

[Al Camp]

Given the data you provided...
With criteria of Year = 2007, Period = 1, and Week = 1 you should see just 3 records
returned....
-10295.47 -952.49 -11247.96 2520.41 700.4 3220.81 Dairy 2007 1 1
-1372.92 -105.07 -1477.99 0 2360.88 2360.88 Frozen 2007 1 1
-20312.2 -3577.8 -23890 11561.14 12811.46 24372.6 Grocery 2007 1 1

You wrote...

I want the YTD to include "all" of 2007, "all" of Period 2 and then "only" Week 2.

That's really not logical... It's "apples and oranges."
If you really must do it this way, then you'll need 3 subreports on your report.
Sub1 to display ALL of 2007 records
Sub2 to display all of Period2
Sub3 to display just Week2