Help with Crosstab Report

  • Thread starter Thread starter Al Newbie
  • Start date Start date
A

Al Newbie

Access 2003 - 2000 format mdb
I have the following

TRANSFORM Sum(tblSalesYTD.TotalSales) AS SumOfTotalSales
SELECT tblSalesYTD.Customer, tblSalesYTD.CustName, tblSalesYTD.Area,
tblSalesYTD.ADesc, tblSalesYTD.Salesperson, tblSalesYTD.Branch,
Sum(tblSalesYTD.TotalSales) AS TotSales
FROM tblSalesYTD
WHERE (((tblSalesYTD.Customer)="ACNO"))
GROUP BY tblSalesYTD.Customer, tblSalesYTD.CustName, tblSalesYTD.Area,
tblSalesYTD.ADesc, tblSalesYTD.Salesperson, tblSalesYTD.Branch
PIVOT tblSalesYTD.TrnMonth In
("1","2","3","4","5","6","7","8","9","10","11","12");

If I remove the WHERE clause then the query returns the expected results but
if I try to limit it to a particualr customer no results get returned

What am I doing wrong?
 
The table that this query is run on gets populated using ADO - I have
noticed that the Customer field is being padded with a space at the
beginning of the field

I have tried using LTrim(rsSales.fields("Customer") as part of the insert
statement but this doesn't make any difference

How can I get rid of these spaces?
 
Found the problem
Al Newbie said:
The table that this query is run on gets populated using ADO - I have
noticed that the Customer field is being padded with a space at the
beginning of the field

I have tried using LTrim(rsSales.fields("Customer") as part of the insert
statement but this doesn't make any difference

How can I get rid of these spaces?
Click to expand...
 
Back
Top