"group by" option not on short-cut menu

  • Thread starter Thread starter dennis@gate
  • Start date Start date
D

dennis@gate

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 said:
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.
Click to expand...

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".
Click to expand...

If it's the aggregation you want, then yes.
Why is "group by"
not displaying when I right click on the diagram pane?
Click to expand...

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]
 
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
 
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 said:
dennis@gate said:
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.
Click to expand...

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".
Click to expand...

If it's the aggregation you want, then yes.
Why is "group by"
not displaying when I right click on the diagram pane?
Click to expand...

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]
Click to expand...
 
dennis@gate said:
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"?
Click to expand...

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

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