Need to make a Top 250 revenue with a crosstab

[Bokazoit]

Sorry if this is the wrong forum...I'm a n00b in this forum so plz don't flame me ^^

I have a access database which contains revenue for 2000+ stores. I have revenues for the 4 quarters - Q22004, Q12004, Q42003 and Q32003.

I need to show revenue for each quarter in the report as well as the total for all 4 revenues. So I made a crosstab query (maybe that's not the right solution?).

When I make a simple query I can choose to sort by revenue and then let the query only show the top 250 stores. But what to do with a crosstab query?

I don't mind if I sort it in the query or that the report sort it for me so that the store with highest revenue is in the top etc. How to do this?

I can tell that my access crosstab query has the following coloums:

StoreID, Storename, Total for quarters, Q204, Q104, Q403, Q303

Any help is appreciated, and plz tell if you need more info

 

[M8KWR]

if you have all the fields in the same table then you culd use somethig like

SELECT Stores.Storename, Sum(Stores.Q204) AS SumOfQ204, Sum(Stores.Q104) AS SumOfQ104, Sum(Stores.Q403) AS SumOfQ403, Sum(Stores.Q303) AS SumOfQ303
FROM Stores
GROUP BY Stores.Storename;

Just change the table/fields with what you have called, them, if you are unsure, post the exact names and I will create it for you.

I do not think you need a crosstab, just a simple group by, and sum the values within the Q-fields.

 

[Bokazoit]

Yes I do need the crosstab since I need to have the quarters horizontal and the StoreID and Storename vertical.

Still usefull then? (your suggestion I mean )

 

[M8KWR]

you could include the storeID number, because i am assuming that the StoreID and Storename, are going to be the same, so they will group properly.

If you include the storeID in my query above, it would act the same as a crosstab query.

 

[dhookom]

I assume you are sorting by the total revenue, not one of the quarters. I hope you have included the column headings in the Column Headings property. If the crosstab doesn't allow you to specify the "Top" property, you should be able to create a select query based on your crosstab query and set the Top property to 250 making sure the final query is sorting by total revenue descending.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[M8KWR]

You could try

SELECT TOP 250 Stores.StoreID, Stores.Storename, Sum(Stores.Q204) AS SumOfQ204, Sum(Stores.Q104) AS SumOfQ104, Sum(Stores.Q403) AS SumOfQ403, Sum(Stores.Q303) AS SumOfQ303, Sum([q204]+[q104]+[q403]+[q303]) AS Total
FROM Stores
GROUP BY Stores.StoreID, Stores.Storename
ORDER BY Sum([q204]+[q104]+[q403]+[q303]) DESC;

 

[Bokazoit]

Tx very much for your help

It works nicely ;-)