Grouping Top Values

[bg1907 via AccessMonster.com]

I am trying to group the top 5 values in a query that is reading off two
different tables and I am not having much luck.

In one table entitled "Stock List" I have one field I would like to be the
primary grouping field (Industry Group). In the other table,
"DailyCommissions" I have two fields that are needed to group as well:
"Security" (which is the stock symbol) and "Shares" (which is the volume or #
of shares traded). I am wanting to group by Industry Group and list the top
5 Securities ("Security") for each Industry Group by volume "Shares". The
primary key is "Shares" in both tables (I'm not sure if that has bearing or
not...I am new to all of this).

Here is my SQL so far:

SELECT DailyCommissions.Shares, DailyCommissions.Security, [Stock List].
[INDUSTRY GROUP]
FROM (BrokerName RIGHT JOIN DailyCommissions ON BrokerName.BrokerFk =
DailyCommissions.BrokerFk) LEFT JOIN [Stock List] ON DailyCommissions.
Security = [Stock List].SYMBOL
WHERE (((DailyCommissions.Shares) In (SELECT Top 5 T.Shares FROM
DailyCommissions as T WHERE T.Security = DailyCommissions.Security ORDER By T.
Shares Desc)));

This is basically giving me the top 5 shares for each security and then
listing the corresponding Industry Group out beside it...not exactly what I
am looking for! Thanks for any help and remember, I am sort of new to all
this stuff.

 

[Marshall Barton]

I am trying to group the top 5 values in a query that is reading off two
different tables and I am not having much luck.

In one table entitled "Stock List" I have one field I would like to be the
primary grouping field (Industry Group). In the other table,
"DailyCommissions" I have two fields that are needed to group as well:
"Security" (which is the stock symbol) and "Shares" (which is the volume or #
of shares traded). I am wanting to group by Industry Group and list the top
5 Securities ("Security") for each Industry Group by volume "Shares". The
primary key is "Shares" in both tables (I'm not sure if that has bearing or
not...I am new to all of this).

Here is my SQL so far:

SELECT DailyCommissions.Shares, DailyCommissions.Security, [Stock List].
[INDUSTRY GROUP]
FROM (BrokerName RIGHT JOIN DailyCommissions ON BrokerName.BrokerFk =
DailyCommissions.BrokerFk) LEFT JOIN [Stock List] ON DailyCommissions.
Security = [Stock List].SYMBOL
WHERE (((DailyCommissions.Shares) In (SELECT Top 5 T.Shares FROM
DailyCommissions as T WHERE T.Security = DailyCommissions.Security ORDER By T.
Shares Desc)));

This is basically giving me the top 5 shares for each security and then
listing the corresponding Industry Group out beside it...not exactly what I
am looking for!

I've read this about four times and I still can't figure out
what's wrong with the result you are getting. You did say
"not exactly what I am looking for", but I don't see where
you've said exactly what you are looking for.

If the issue is that the records all have industry name
(instead of what?), then you need to understand that this is
normal. If you want the data formatted in some other way,
then use a report to do the formatting. Be sure to learn
how to use the Sorting and Grouping features in reports.
Sorting and Grouping is available in the View menu when the
report is open in design view.