Finding TOP 3 SUMs

[Stolman]

I have an ACTIVITY table with Date, Zip, Category (from lookup table CAT) and Sub-Category (from lookup table SUBCAT).

I have successfully created a Pivot-Table query that shows activity counts by Zip for each Category and Sub-Category within a date range.

The SQL for the above is:
SELECT Activity.Zip, Count(Activity.Zip) AS CountOfZip, Activity.Cat, Activity.SubCat
FROM Activity
WHERE (((Activity.Date) Between [Enter Beginning Date] And [Enter Ending Date]))
GROUP BY Activity.Zip, Activity.Cat, Activity.SubCat;

I need to create a similar Pivot-Table query that shows the TOP 3 most active Zips (the highest counts) within a date range.

Might someone please assist with a code sample to accomplish this?

Thanks in advance,
-bill

 

[jpiscit1]

Try this:


SELECT Top 3 Activity.Zip, Count(Activity.Zip) AS CountOfZip, Activity.Cat, Activity.SubCat
FROM Activity
WHERE (((Activity.Date) Between [Enter Beginning Date] And [Enter Ending Date]))
GROUP BY Activity.Zip, Activity.Cat, Activity.SubCat
Order BY Activity.Zip;


Your limits are only as far as you set your boundries......

 

[spongie1]

I might not have read your post close enough...

Can't you just do

SELECT TOP 3 Activity.Zip, Count(Activity.Zip) AS CountOfZip, Activity.Cat, Activity.SubCat
FROM Activity
WHERE (((Activity.Date) Between [Enter Beginning Date] And [Enter Ending Date]))
GROUP BY Activity.Zip, Activity.Cat, Activity.SubCat;

 

[jpiscit1]

Actually I read it wrong. But I think he will need the order by clause to get the top three highest counts.

As follows:

SELECT Top 3 Activity.Zip, Count(Activity.Zip) AS CountOfZip, Activity.Cat, Activity.SubCat
FROM Activity
WHERE (((Activity.Date) Between [Enter Beginning Date] And [Enter Ending Date]))
GROUP BY Activity.Zip, Activity.Cat, Activity.SubCat
Order By CountOfZip DESC;

Your limits are only as far as you set your boundries......

 

[Stolman]

Hi jpiscit1

I tried that already (Order By CountOfZip) and Access grumbles at me, asking for the parameter value of CountOfZip.

I don't know how to get around that one.

Thanks for your prompt post.
-bill

 

[lespaul]

you do:

ORDER BY Count(Activity.Zip)

instead of using the alias.

HTH

Leslie

 

[Stolman]

Thanks Leslie,

Alas, it works, but not correctly. It only provides the counts for one ZIP code (not the TOP 3). Any other thoughts?

Thanks,
-bill