"group by" option not on short-cut menu

The "group by" option is not on short-cut menu when I right click on diagram
pane of query designer. I think this is what I need but am not sure. Here
is the task: I have a table consisting of records have two fields: "zone"
and "connection". I want to generate a report that summarizes the table. It
should list the zones and then for each zone list the connections and total
number of connection. Am I headed in the right direction with "group by".
Why is "group by" not displaying when I right click on the diagram pane?

dennis@gate wrote:
The "group by" option is not on short-cut menu when I right click on
diagram pane of query designer. I think this is what I need but am
not sure. Here is the task: I have a table consisting of records
have two fields: "zone" and "connection". I want to generate a
report that summarizes the table. It should list the zones and then
for each zone list the connections and total number of connection.

This is a contradiction. You can list either the number of connections
for each zone (an aggregation: count) or the individual connections. You
cannot do both unless you have duplicate connections, like this, and you
want to see how many of each connection are in each zone. Like this:

zone connection
1 a
1 b
1 a
2 a
2 b
2 b

query result:
zone connection count
1 a 2
1 b 1
2 a 1
2 b 2

This would be a very strange design indeed so I suspect your table is
more like this:

zone connection
1 a
1 b
1 c
2 a
2 b

and the result you want is more like this:
1 3
2 2


Am I headed in the right direction with "group by".

If it's the aggregation you want, then yes.

Why is "group by"
not displaying when I right click on the diagram pane?

You have to click the "Totals" button in the toolbar to enable that.
Alternatively, you can select it from the View menu.
Or, you can simply switch to SQL View and input it directly:

select [zone],count(*) as connections
from yourtable
group by [zone]
--
HTH,
Bob Barrows

Try Selecting View: Totals from the menu. And see if that helps.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

dennis@gate wrote:
The "group by" option is not on short-cut menu when I right click on diagram
pane of query designer. I think this is what I need but am not sure. Here
is the task: I have a table consisting of records have two fields: "zone"
and "connection". I want to generate a report that summarizes the table. It
should list the zones and then for each zone list the connections and total
number of connection. Am I headed in the right direction with "group by".
Why is "group by" not displaying when I right click on the diagram pane?

Table is like this:

zone connection
1 A
1 B
1 B
2 A
2 B
3 A
3 C


The report I need is like this
zone connection quantity
1 A 1
B 2
2 A 1
A 1
3 A 1
C 1

Is this possible with "group by"?


"Bob Barrows" wrote:

dennis@gate wrote:
The "group by" option is not on short-cut menu when I right click on
diagram pane of query designer. I think this is what I need but am
not sure. Here is the task: I have a table consisting of records
have two fields: "zone" and "connection". I want to generate a
report that summarizes the table. It should list the zones and then
for each zone list the connections and total number of connection.

This is a contradiction. You can list either the number of connections
for each zone (an aggregation: count) or the individual connections. You
cannot do both unless you have duplicate connections, like this, and you
want to see how many of each connection are in each zone. Like this:

zone connection
1 a
1 b
1 a
2 a
2 b
2 b

query result:
zone connection count
1 a 2
1 b 1
2 a 1
2 b 2

This would be a very strange design indeed so I suspect your table is
more like this:

zone connection
1 a
1 b
1 c
2 a
2 b

and the result you want is more like this:
1 3
2 2


Am I headed in the right direction with "group by".

If it's the aggregation you want, then yes.

Why is "group by"
not displaying when I right click on the diagram pane?

You have to click the "Totals" button in the toolbar to enable that.
Alternatively, you can select it from the View menu.
Or, you can simply switch to SQL View and input it directly:

select [zone],count(*) as connections
from yourtable
group by [zone]
--
HTH,
Bob Barrows

dennis@gate wrote:
Table is like this:

zone connection
1 A
1 B
1 B
2 A
2 B
3 A
3 C


The report I need is like this
zone connection quantity
1 A 1
B 2
2 A 1
A 1
3 A 1
C 1

Is this possible with "group by"?



Yes, include both zone and connection in the group by clause:

select [zone],[connection],count(*) as quatity
from yourtable
group by [zone],[connection]

--
HTH,
Bob Barrows