Displaying data using crosstab query or other method

[Guest]

I used this SQL:

SELECT
ID,
SalesDate,
Sales,
Amount,
(SELECT
COUNT(*)
FROM
tblSales AS T
WHERE
T.ID = tblSales.ID
AND
T.Amount >= tblSales.Amount
AS Rank
FROM
tblSales
ORDER BY
ID,
SalesDate;

to derive the following

ID SalesDate Sales Amount Rank
103 9/13/2004 5 200 1
103 9/14/2004 8 30 2
104 9/3/2004 12 50 2
104 9/14/2004 11 350 1
105 9/13/2004 10 70 1

Now, I want to display the results as follows:

ID TotalSales 1 2 (where 1 and 2 are the rankings)
103 13 200 30
104 23 350 50
105 10 70

HOWEVER. When I created a crosstab query , I get the following message:
" Microsoft Jet database does not recognize 'tblSales.ID' as a valid field
name or expression"

(I am not using any input parameters, so i should not have to set parameter
values)

Any ideas of how to solve the crosstab query problem? Or, there may be a
way to obtain the result without using crosstab query.

 

[Allen Browne]

You have not posted the failing crosstab query statement, so it's a bit hard
to diagnose.

Presumably you have saved the first query with a name such as "Query1", and
it works fine. Now you are trying to create a crosstab query, using Query1
as an input table. If that's the idea, try replacing:
tblSales.ID
with:
Query1.ID
since the ID field is coming into the crosstab from Query1 if I understand
correctly.