Field Name in Cross Tab Query

[margaret]

I have a query as follows:

TRANSFORM Sum(tblSales.amount) AS SumOfamount
SELECT lessee.lessee
FROM (lessee INNER JOIN (gamemaster INNER JOIN tblSales ON (gamemaster.game
= tblSales.game) AND (gamemaster.game = tblSales.game)) ON lessee.lessee =
gamemaster.lessee) INNER JOIN tblControlFile ON tblSales.fairdate =
tblControlFile.Fairdate
WHERE (((lessee.lessee)<>"Z999"))
GROUP BY lessee.lessee
PIVOT tblSales.fairdate;

This query will always be two columns ... lessee and fairdate. When I run
the query, the fairdate column title comes up with the current date. For
example: lessee A08 has $30000 on fairdate 09/15/08 .. the column titles are
lessee and 09/15/08. Since I will run this everyday and have other queries
getting information from this query, I need the column title to be unchanged
from day to day. Is there a way I can set the column title to be ...
"amount"?

 

[Dale Fye]

Margaret,

From the looks of your query, and your comment that the query would only
return two columns, I can only assume that your tblControlFile table contains
only a single record. Is that Accurate?

In this case, I don't think you need a Crosstab Query. Why not just do an
aggregate query. Something like:

SELECT lessee.lessee, Sum(tblSales.amount) AS Amount
FROM (lessee INNER JOIN (gamemaster INNER JOIN tblSales ON (gamemaster.game
= tblSales.game) AND (gamemaster.game = tblSales.game)) ON lessee.lessee =
gamemaster.lessee) INNER JOIN tblControlFile ON tblSales.fairdate =
tblControlFile.Fairdate
WHERE lessee.lessee<>"Z999"
GROUP BY lessee.lessee


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[margaret]

Oh, you ARE good!!! Thanks so much that worked perfectly.

 

[Dale Fye]

Glad I could help

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.