Crosstab . . .again


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
 
Ad

Advertisements

D

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


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
 
N

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

Advertisements

D

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


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

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

Similar Threads

Debtors Ledger 3
Crosstab repor and total 3
count 1
Relative months In reports with cross tab query 1

Top