Crosstab repor and total


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

Advertisements

D

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

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)
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");
 
N

Newbie

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,
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)
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");
 
Ad

Advertisements

D

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


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,
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)
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top