Crosstab repor and total

[Newbie]

My total column does not restrict the sum to the data in the rest of the
report

eg I enter an end date of 12/12/02 the [Total Sales] column adds up the
following

record 1 date 18/12/01 value 100
record 2 date 9/1/ 02 value 100

However the mth column only shows
rec 2 date 9/1/02

How can I get the mth / total columns to report on the same data

Here is my query

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([OrigGrnValue],0)) AS Expr2
SELECT Grn.StockCode,
Round(Abs(Sum(([OrigReceiptDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtD
ateEnd) And
[OrigReceiptDate]<=forms!frmSalesDates!txtDateEnd)*[OrigGrnValue])),0) AS
[Total Sales]
FROM Grn
WHERE (((Grn.Warehouse)="T"))
GROUP BY Grn.StockCode
PIVOT "Mth" & DateDiff("m",[OrigReceiptDate],forms!frmSalesDates!txtDateEnd)
In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");

 

[Duane Hookom]

Try add to the Where clause
WHERE Grn.Warehouse="T" and
OrigReceiptDate BETWEEN DateAdd("yyyy",-1,forms!frmSalesDates!txtDateEnd)
and forms!frmSalesDates!txtDateEnd

_____________
Duane Hookom
MS Access MVP

My total column does not restrict the sum to the data in the rest of the
report

eg I enter an end date of 12/12/02 the [Total Sales] column adds up the
following

record 1 date 18/12/01 value 100
record 2 date 9/1/ 02 value 100

However the mth column only shows
rec 2 date 9/1/02

How can I get the mth / total columns to report on the same data

Here is my query

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([OrigGrnValue],0)) AS Expr2
SELECT Grn.StockCode,
Round(Abs(Sum(([OrigReceiptDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtD
ateEnd) And
[OrigReceiptDate]<=forms!frmSalesDates!txtDateEnd)*[OrigGrnValue])),0) AS
[Total Sales]
FROM Grn
WHERE (((Grn.Warehouse)="T"))
GROUP BY Grn.StockCode
PIVOT "Mth" & DateDiff("m",[OrigReceiptDate],forms!frmSalesDates!txtDateEnd)

In

("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");

 

[Newbie]

Thanks but that doesn't make any difference

Total = 49 but month = 25

Any other ideas?

Thanks again for your help

Al

Try add to the Where clause
WHERE Grn.Warehouse="T" and
OrigReceiptDate BETWEEN DateAdd("yyyy",-1,forms!frmSalesDates!txtDateEnd)
and forms!frmSalesDates!txtDateEnd

_____________
Duane Hookom
MS Access MVP

My total column does not restrict the sum to the data in the rest of the
report

eg I enter an end date of 12/12/02 the [Total Sales] column adds up the
following

record 1 date 18/12/01 value 100
record 2 date 9/1/ 02 value 100

However the mth column only shows
rec 2 date 9/1/02

How can I get the mth / total columns to report on the same data

Here is my query

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([OrigGrnValue],0)) AS Expr2
SELECT Grn.StockCode,

Round(Abs(Sum(([OrigReceiptDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtD

ateEnd) And
[OrigReceiptDate]<=forms!frmSalesDates!txtDateEnd)*[OrigGrnValue])),0) AS
[Total Sales]
FROM Grn
WHERE (((Grn.Warehouse)="T"))
GROUP BY Grn.StockCode
PIVOT "Mth" & DateDiff("m",[OrigReceiptDate],forms!frmSalesDates!txtDateEnd)

In

("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1

0","Mth11");

 

[Duane Hookom]

Your columns are summing values for the same month. You may be missing the
values from either the earliest or latest month. Try enter the ending date
of a month in the txtDateEnd text box.

--
_____________
Duane Hookom
MS Access MVP

Thanks but that doesn't make any difference

Total = 49 but month = 25

Any other ideas?

Thanks again for your help

Al

Try add to the Where clause
WHERE Grn.Warehouse="T" and
OrigReceiptDate BETWEEN DateAdd("yyyy",-1,forms!frmSalesDates!txtDateEnd)
and forms!frmSalesDates!txtDateEnd

_____________
Duane Hookom
MS Access MVP

My total column does not restrict the sum to the data in the rest of the
report

eg I enter an end date of 12/12/02 the [Total Sales] column adds up the
following

record 1 date 18/12/01 value 100
record 2 date 9/1/ 02 value 100

However the mth column only shows
rec 2 date 9/1/02

How can I get the mth / total columns to report on the same data

Here is my query

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([OrigGrnValue],0)) AS Expr2
SELECT Grn.StockCode,

Round(Abs(Sum(([OrigReceiptDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtD

ateEnd) And
[OrigReceiptDate]<=forms!frmSalesDates!txtDateEnd)*[OrigGrnValue])),0) AS
[Total Sales]
FROM Grn
WHERE (((Grn.Warehouse)="T"))
GROUP BY Grn.StockCode
PIVOT "Mth" & DateDiff("m",[OrigReceiptDate],forms!frmSalesDates!txtDateEnd)

In

("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1