Crosstab . . .again

[Newbie]

Hi,

I want to be able to limit the records returned to only customers where
records have been returned. i.e if there are no NetSalesValue records for
the customer I don't want it to appear on the report.
Any ideas how I can do this?

The query I have so far is:

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([NetSalesValue],0)) AS Expr2
SELECT dbo_ArTrnDetail.Customer, ArCustomer.Name, dbo_ArTrnDetail.StockCode,
Round(Abs(Sum(([InvoiceDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtDateE
nd) And [InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[NetSalesValue])),0)
AS [Total Sales]
FROM dbo_ArTrnDetail INNER JOIN ArCustomer ON dbo_ArTrnDetail.Customer =
ArCustomer.Customer
WHERE (((dbo_ArTrnDetail.StockCode)<>"") AND
((Sum(Round([NetSalesValue],0)))<>0))
GROUP BY dbo_ArTrnDetail.Customer, ArCustomer.Name,
dbo_ArTrnDetail.StockCode
PIVOT "Mth" & DateDiff("m",[InvoiceDate],forms!frmSalesDates!txtDateEnd) In
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");

Thanks

 

[Duane Hookom]

Without having to dig through this, could you just create another query
based on your crosstab that does what you want? Or, place >0 under the Total
Sales column.

--
Duane Hookom
MS Access MVP

Hi,

I want to be able to limit the records returned to only customers where
records have been returned. i.e if there are no NetSalesValue records for
the customer I don't want it to appear on the report.
Any ideas how I can do this?

The query I have so far is:

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([NetSalesValue],0)) AS Expr2
SELECT dbo_ArTrnDetail.Customer, ArCustomer.Name, dbo_ArTrnDetail.StockCode,Round(Abs(Sum(([InvoiceDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtDateE
nd) And [InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[NetSalesValue])),0)
AS [Total Sales]
FROM dbo_ArTrnDetail INNER JOIN ArCustomer ON dbo_ArTrnDetail.Customer =
ArCustomer.Customer
WHERE (((dbo_ArTrnDetail.StockCode)<>"") AND
((Sum(Round([NetSalesValue],0)))<>0))
GROUP BY dbo_ArTrnDetail.Customer, ArCustomer.Name,
dbo_ArTrnDetail.StockCode
PIVOT "Mth" & DateDiff("m",[InvoiceDate],forms!frmSalesDates!txtDateEnd) In("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
0","Mth11");

Thanks

 

[Newbie]

Thanks but , . . . . .
Adding <>0 on the Total Sales column has the error message
Cannot have aggregate function in Where clause

(((Round(Abs(Sum(([InvoiceDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtDa
teEnd) And
[InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[NetSalesValue])),0))<>0) AND
((dbo_ArTrnDetail.StockCode)<>""))

Is there a way round this within this query or do I have to have another
select query based on the crosstab?

Thanks again for your help

Al

Without having to dig through this, could you just create another query
based on your crosstab that does what you want? Or, place >0 under the Total
Sales column.

--
Duane Hookom
MS Access MVP

Hi,

I want to be able to limit the records returned to only customers where
records have been returned. i.e if there are no NetSalesValue records for
the customer I don't want it to appear on the report.
Any ideas how I can do this?

The query I have so far is:

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([NetSalesValue],0)) AS Expr2
SELECT dbo_ArTrnDetail.Customer, ArCustomer.Name, dbo_ArTrnDetail.StockCode,

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

nd) And [InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[NetSalesValue])),0)
AS [Total Sales]
FROM dbo_ArTrnDetail INNER JOIN ArCustomer ON dbo_ArTrnDetail.Customer =
ArCustomer.Customer
WHERE (((dbo_ArTrnDetail.StockCode)<>"") AND
((Sum(Round([NetSalesValue],0)))<>0))
GROUP BY dbo_ArTrnDetail.Customer, ArCustomer.Name,
dbo_ArTrnDetail.StockCode
PIVOT "Mth" & DateDiff("m",[InvoiceDate],forms!frmSalesDates!txtDateEnd) In

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

0","Mth11");

Thanks

 

[Duane Hookom]

I think the SQL should be:
HAVING (((Round(Abs(Sum(([InvoiceDate]>= DateAdd("yyyy", -1,
forms!frmSalesDates!txtDateEnd) And
[InvoiceDate]<=forms!frmSalesDates!txtDateEnd) * [NetSalesValue])),0)) <> 0

If that doesn't work then try another query.
--
Duane Hookom
MS Access MVP

Thanks but , . . . . .
Adding <>0 on the Total Sales column has the error message
Cannot have aggregate function in Where clause

(((Round(Abs(Sum(([InvoiceDate]>=DateAdd("yyyy",-1,forms!frmSalesDates!txtDa
teEnd) And
[InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[NetSalesValue])),0))<>0) AND
((dbo_ArTrnDetail.StockCode)<>""))

Is there a way round this within this query or do I have to have another
select query based on the crosstab?

Thanks again for your help

Al

Without having to dig through this, could you just create another query
based on your crosstab that does what you want? Or, place >0 under the Total
Sales column.

--
Duane Hookom
MS Access MVP

Hi,

I want to be able to limit the records returned to only customers where
records have been returned. i.e if there are no NetSalesValue records for
the customer I don't want it to appear on the report.
Any ideas how I can do this?

The query I have so far is:

PARAMETERS [forms]![frmSalesDates]![txtDateEnd] DateTime;
TRANSFORM Sum(Round([NetSalesValue],0)) AS Expr2
SELECT dbo_ArTrnDetail.Customer, ArCustomer.Name, dbo_ArTrnDetail.StockCode,

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

nd) And [InvoiceDate]<=forms!frmSalesDates!txtDateEnd)*[NetSalesValue])),0)
AS [Total Sales]
FROM dbo_ArTrnDetail INNER JOIN ArCustomer ON dbo_ArTrnDetail.Customer =
ArCustomer.Customer
WHERE (((dbo_ArTrnDetail.StockCode)<>"") AND
((Sum(Round([NetSalesValue],0)))<>0))
GROUP BY dbo_ArTrnDetail.Customer, ArCustomer.Name,
dbo_ArTrnDetail.StockCode
PIVOT "Mth" &

DateDiff("m",[InvoiceDate],forms!frmSalesDates!txtDateEnd)
In

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