Iram said:
Hello. I am using Access 2000 and I have a query with about 20 fields but
what I want to know is how do you go into a regular Select Query and
change
it into a query or something that will give me totals for certain fields.
Could you give me in laymans terms what row headings and column headings
and
value fields are? I am confused on how to do this.
In sample NorthWind mdb,
a simple SELECT query might be:
SELECT
Orders.ShipCountry,
Orders.EmployeeID
FROM Orders
ORDER BY
Orders.ShipCountry;
in query designer, the grid will look like:
Field: ShipCountry EmployeeID
Table: Orders Orders
Sort: Ascending
Show: <checked> <checked>
Criteria:
or:
I'm sorry to complicate things right off the bat,
but because EmployeeID is a (bad) lookup field,
right-mouse click on the column for
EmployeeID and choose Properties. Under
Lookup tab, make sure Display Control is set
for "TextBox" for our purposes so we will see actual
EmployeeId numbers, not "looked-up" employee names.
Sorry...
Now, if you look at the query results you will get
as many records as there are records in table Orders
where some combinations of Country/Emp
are repeated.
More meaningful results might be if we were to
show only the distinct combinations (or groups).
We could do this by adding DISTINCT to the
query, or we could change the query to a totals
query as Karl has said by clicking on the sigma icon.
In our grid we now get a "Total" row
Field: ShipCountry EmployeeID
Table: Orders Orders
Total: Group By Group By
Sort: Ascending
Show: <checked> <checked>
Criteria:
or:
Ship Country Employee
Argentina 1
Argentina 2
Argentina 3
Argentina 4
Argentina 6
Argentina 7
Argentina 8
Argentina 9
Austria 1
Austria 2
Austria 3
Austria 4
Austria 6
Austria 7
Austria 8
Austria 9
Belgium 1
Belgium 2
Belgium 3
Belgium 4
Belgium 5
Belgium 6
Belgium 7
Belgium 9
<etc>
if we look through the results, we see some
interesting things (with difficulty) such as Emp 5
ships to Belgium, but not Argentina nor Austria...
we could easily count number of orders in each
group (this is a totals query after all) by adding
a third column to the grid...actually we could count
the number of records in each group 2 ways:
Field: EmployeeID cnt: Count(*)
Table: Orders
Total: Count Expression
Sort:
Show: <checked> <checked>
Criteria:
or:
SELECT
Orders.ShipCountry,
Orders.EmployeeID,
Count(Orders.EmployeeID) AS CountOfEmployeeID,
Count(*) AS cnt
FROM Orders
GROUP BY
Orders.ShipCountry,
Orders.EmployeeID
ORDER BY
Orders.ShipCountry;
If you look at the query results, in this case the
counts will be the same, but if an EmployeeID
had been NULL in a record of a group,
Count(*) would have counted that record,
but Count(EmployeeID) would not have counted it.
I'd like to change both to an "Expression" and
give each an alias (and change order of columns):
Field: TotalOrders: Count(*) cnt: Count(*)
Table:
Total: Expression Expression
Sort:
Show: <checked> <checked>
Criteria:
or:
SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders,
Orders.EmployeeID,
Count(*) AS cnt
FROM Orders
GROUP BY
Orders.ShipCountry,
Orders.EmployeeID
ORDER BY
Orders.ShipCountry;
To get a result where data "pops out at you,"
lets change to a crosstab query by clicking on
"Query" in top menu and selecting "Crosstab Query."
Our grid will now have one more row:
Field: ShipCountry TotalOrders: Count(*)
Table: Orders
Total: Group By Expression
Crosstab:
Sort: Ascending
Criteria:
or:
Field: EmployeeID cnt: Count(*)
Table:
Total: Group By Expression
Crosstab:
Sort:
Criteria:
or:
So....I think this is where you are having difficulty,
what selection do you make in the "Crosstab" row?
1) Row Heading
2) Column Heading
3) Value
I guess it starts with what you wish to see, i.e.,
I'd like to see....
------
on the left I'd like to see every distinct country
and how many total orders there were for each
country....
so we have our Row Headings:
Field: ShipCountry TotalOrders: Count(*)
Table: Orders
Total: Group By Expression
Crosstab: Row Heading Row Heading
Sort: Ascending
Criteria:
or:
------
I'd like Access to create a column on the right
for each Employee...
so we have our Column Heading:
Field: EmployeeID
Table:
Total: Group By
Crosstab: Column Heading
Sort:
Criteria:
or:
------
and under each EmployeeID column,
I'd like to see the number of orders
made by that employee for each country row...
so we have our Value:
Field: cnt: Count(*)
Table:
Total: Expression
Crosstab: Value
Sort:
Criteria:
or:
-----
The SQL View of our crosstab query
then would look like:
TRANSFORM Count(*) AS cnt
SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders
FROM Orders
GROUP BY Orders.ShipCountry
ORDER BY Orders.ShipCountry
PIVOT Orders.EmployeeID;
The results might be broken down into "3 field positions":
1) Row Heading
2) Column Heading
3) Value
1) The Row Heading(s) come from
any field(s) in the Group By clause
of your SELECT stmt
(plus any other aggregates in the
SELECT stmt):
"SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders
FROM Orders
GROUP BY orders.ShipCountry"
2) The Column Headings come from
the PIVOT clause at the end of the
query:
"PIVOT orders.EmployeeID;"
3)The Value comes from the crosstab's beginning
TRANSFORM clause:
"TRANSFORM Count(*) AS cnt"
here be an attempt to graphically "break down" results
from the above query:
[---row heading---------------][-col heading=employee id--]
[-GroupBy---][-Expression--][-value=count(*)for each id-]
Ship Country TotalOrders 1 2 3 4 5 6 7 8 9
Argentina 16 1 1 1 4 1 3 3 2
Austria 40 5 6 5 6 4 6 5 3
Belgium 19 1 2 1 6 4 1 2 2
Brazil 83 11 9 10 20 5 8 8 9 3
Canada 30 5 5 9 3 3 2 2 1
Denmark 18 4 3 1 3 1 4 2
Finland 22 2 6 2 3 2 1 1 4 1
France 77 9 11 13 14 5 9 5 8 3
Germany 122 19 14 19 25 4 9 6 17 9
Ireland 19 1 3 5 1 3 2 1 3
Italy 28 5 7 1 6 1 1 2 3 2
Mexico 28 6 4 6 4 1 5 2
Norway 6 2 1 1 2
Poland 7 2 2 1 1 1
Portugal 13 2 2 3 2 1 2 1
Spain 23 3 2 3 7 2 3 2 1
Sweden 37 5 4 8 3 3 2 2 8 2
Switzerland 18 2 3 4 1 2 3 1 2
UK 56 9 5 8 12 2 5 5 6 4
USA 122 21 9 21 22 6 14 7 19 3
Venezuela 46 8 4 8 8 3 2 3 9 1
Note the "gaps." One typical method to get 0 instead
of the "gap" is to use the Null-To-Zero function in your
TRANSFORM clause:
TRANSFORM NZ(Count(*),0) AS cnt
====================
If we knew for sure before hand all the EmployeeID's,
we could create a "totals" query that mimics
the crosstab above using subqueries.
SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=1) As 1,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=2) As 2,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=3) As 3,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=4) As 4,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=5) As 5,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=6) As 6,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=7) As 7,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=8) As 8,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=9) As 9,
FROM Orders
GROUP BY orders.ShipCountry;
You might think of the crosstab query
as just a shorthand for creating all those
subqueries where Access does all the work
for you
1) What aggregate should we use for
each subquery?
Put it in a TRANFORM clause (value)
TRANSFORM Count(*)
2) What fields do we want to match
between main query and subquery?
GROUP BY fields of SELECT clause
3) Within each group, what field (or expression)
will determine a distinct aggregate value
(and what will be the alias for each subquery)?
Put it in the PIVOT clause (column heading)
PIVOT orders.EmployeeID
