Crosstab . . .again

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

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
 
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


Newbie said:
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
Click to expand...
 
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
Duane Hookom said:
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


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

Thanks
Click to expand...
Click to expand...
 
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


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


Newbie said:
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,
Click to expand...
Click to expand...
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" &
Click to expand...
DateDiff("m",[InvoiceDate],forms!frmSalesDates!txtDateEnd)
In
Click to expand...
("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth1
Click to expand...
 
Back
Top