Reportining top ## lines from a query.

[pafcpilgrim]

Guys,

We have a sales query for the transactions by each department asc by total sales for each line which at present we then export into Excel and take out the Top50 sales lines for each.

What we would like to know is how can we generate a report or query to show the top 50 lines for each department, and not include the additional lines.

Once this has been done i can then group the report by department and show those top 50 lines.

Thanks in advance.

Mike

 

[shadow9449]

In your query, just type: SELECT TOP 50 instead of SELECT.

If you are using the query building grid in Access, then right click, select Properties, and under "Top Values", type 50.

SHADOW

 

[pafcpilgrim]

Thanks Shadow, i thought of that to start with the only problem is that there several departments, and we need the top 50 sales for each department not in total and not have to do several queries.

Any ideas with this?

Thanks for you help.

 

[JoeCruse]

Shadow,

Thank you!!!! I'm not good with SQL, and did not know you could limit the recordset in the query like that. I've been going all over Perdition trying to limit the count of records returned in a query for a report, to keep it from being cumbersome, and this did what I needed.

Thanks again!

 

[shadow9449]

Thank you!!!! I'm not good with SQL, and did not know you could limit the recordset in the query like that

You are welcome.

SHADOW

 

[pbaldy]

we need the top 50 sales for each department not in total and not have to do several queries.

A couple of methods here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210039