Crosstab repor and total

  • Thread starter Thread starter Newbie
  • Start date Start date
N

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");
 
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

Newbie said:
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
Click to expand...
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");
Click to expand...
 
Thanks but that doesn't make any difference

Total = 49 but month = 25

Any other ideas?

Thanks again for your help

Al
Duane Hookom said:
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

Newbie said:
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,
Click to expand...
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
Click to expand...
Click to expand...
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");
Click to expand...
Click to expand...
 
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


Newbie said:
Thanks but that doesn't make any difference

Total = 49 but month = 25

Any other ideas?

Thanks again for your help

Al
Duane Hookom said:
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

Newbie said:
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,
Click to expand...
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
Click to expand...
 
Back
Top