row which has TOTALS of coulmns?

Hi,

I have created a query using "group by" and am trying to find out how to
create a "Total" row at the bottom which adds up the numbers in each column.
(details of table and query below) Please, please can anyone help me?
Thankyou so much for your help
Cheers
Barbara

eg, I have grouped by "Type" so each type apears down the left.
Along the top I have headings such as "Count for that type", "No Vendors in
that type" and "no vendors in that type where the vendor is not IBM", "and
sum of the costs in that type"

So my table looks like:
Count for that type No. Vendors No. vendors not IBM
Sum cost
Type1 10 4 1
300.00
Type2 22 4 4
250.00
Type 3 12 5 3
400.00
TOTAL ? ? ?
?

My query at the moment is:
SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type;

That's the sort of thing you would normally do in a report, not in a query,
but you can "trick" Access to do it in the form of a Union query, so you put
together the results of two separate queries, one that sums groupped by
type, and another that sums all. It would look something like:

SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type

UNION SELECT "Type All" AS Expr1, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY "Type All"

HTH,
Nikos

"confused" wrote in message
...
Hi,

I have created a query using "group by" and am trying to find out how to
create a "Total" row at the bottom which adds up the numbers in each
column.
(details of table and query below) Please, please can anyone help me?
Thankyou so much for your help
Cheers
Barbara

eg, I have grouped by "Type" so each type apears down the left.
Along the top I have headings such as "Count for that type", "No Vendors
in
that type" and "no vendors in that type where the vendor is not IBM", "and
sum of the costs in that type"

So my table looks like:
Count for that type No. Vendors No. vendors not
IBM
Sum cost
Type1 10 4 1
300.00
Type2 22 4 4
250.00
Type 3 12 5 3
400.00
TOTAL ? ? ?
?

My query at the moment is:
SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type;

Hi Barbara,

You may not want to perform a total in the query....
Either you want this total to be shown on a form or a
report. Is this correct? If so, let me know if it is a
report or a form and I will assist you as to how to
achieve this.

John
-----Original Message-----
Hi,

I have created a query using "group by" and am trying to
find out how to
create a "Total" row at the bottom which adds up the
numbers in each column.
(details of table and query below) Please, please can
anyone help me?
Thankyou so much for your help
Cheers
Barbara

eg, I have grouped by "Type" so each type apears down
the left.
Along the top I have headings such as "Count for that
type", "No Vendors in
that type" and "no vendors in that type where the vendor
is not IBM", "and
sum of the costs in that type"

So my table looks like:
Count for that type No. Vendors
No. vendors not IBM
Sum cost
Type1 10
4 1
300.00
Type2 22
4 4
250.00
Type 3 12
5 3
400.00
TOTAL ? ?
?
?

My query at the moment is:
SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors
not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type;







.

Hi,

thanks for you help! Ive decided to stick with the query solution as it is
easy to then do a dump from Excell. Thanks!

"John" wrote:

Hi Barbara,

You may not want to perform a total in the query....
Either you want this total to be shown on a form or a
report. Is this correct? If so, let me know if it is a
report or a form and I will assist you as to how to
achieve this.

John
-----Original Message-----
Hi,

I have created a query using "group by" and am trying to
find out how to
create a "Total" row at the bottom which adds up the
numbers in each column.
(details of table and query below) Please, please can
anyone help me?
Thankyou so much for your help
Cheers
Barbara

eg, I have grouped by "Type" so each type apears down
the left.
Along the top I have headings such as "Count for that
type", "No Vendors in
that type" and "no vendors in that type where the vendor
is not IBM", "and
sum of the costs in that type"

So my table looks like:
Count for that type No. Vendors
No. vendors not IBM
Sum cost
Type1 10
4 1
300.00
Type2 22
4 4
250.00
Type 3 12
5 3
400.00
TOTAL ? ?
?
?

My query at the moment is:
SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors
not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type;







.

Hiya!

Thanks very much for your advice! much appreciated!!
Ive tried this and it works well, the only problem being that the "Total
All" row doesnt appear at the end - Access seems to force my rows to be
alphabetically ordered for some reason - any ideas?
Thanks again!
Barbara

"Nikos Yannacopoulos" wrote:

That's the sort of thing you would normally do in a report, not in a query,
but you can "trick" Access to do it in the form of a Union query, so you put
together the results of two separate queries, one that sums groupped by
type, and another that sums all. It would look something like:

SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type

UNION SELECT "Type All" AS Expr1, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY "Type All"

HTH,
Nikos

"confused" wrote in message
...
Hi,

I have created a query using "group by" and am trying to find out how to
create a "Total" row at the bottom which adds up the numbers in each
column.
(details of table and query below) Please, please can anyone help me?
Thankyou so much for your help
Cheers
Barbara

eg, I have grouped by "Type" so each type apears down the left.
Along the top I have headings such as "Count for that type", "No Vendors
in
that type" and "no vendors in that type where the vendor is not IBM", "and
sum of the costs in that type"

So my table looks like:
Count for that type No. Vendors No. vendors not
IBM
Sum cost
Type1 10 4 1
300.00
Type2 22 4 4
250.00
Type 3 12 5 3
400.00
TOTAL ? ? ?
?

My query at the moment is:
SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type;

The order is imposed by Access when it eliminates any duplicate values (which a
union does).

You can add another column to your queries - to force a sort order.

SELECT "Detail" as SpecialOrder, Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type

UNION SELECT "Total", "Type All" AS Expr1, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY "Type All"
ORDER BY SpecialOrder, Type


confused wrote:

Hiya!

Thanks very much for your advice! much appreciated!!
Ive tried this and it works well, the only problem being that the "Total
All" row doesnt appear at the end - Access seems to force my rows to be
alphabetically ordered for some reason - any ideas?
Thanks again!
Barbara

"Nikos Yannacopoulos" wrote:

That's the sort of thing you would normally do in a report, not in a query,
but you can "trick" Access to do it in the form of a Union query, so you put
together the results of two separate queries, one that sums groupped by
type, and another that sums all. It would look something like:

SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type

UNION SELECT "Type All" AS Expr1, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY "Type All"

HTH,
Nikos

"confused" wrote in message
...
Hi,

I have created a query using "group by" and am trying to find out how to
create a "Total" row at the bottom which adds up the numbers in each
column.
(details of table and query below) Please, please can anyone help me?
Thankyou so much for your help
Cheers
Barbara

eg, I have grouped by "Type" so each type apears down the left.
Along the top I have headings such as "Count for that type", "No Vendors
in
that type" and "no vendors in that type where the vendor is not IBM", "and
sum of the costs in that type"

So my table looks like:
Count for that type No. Vendors No. vendors not
IBM
Sum cost
Type1 10 4 1
300.00
Type2 22 4 4
250.00
Type 3 12 5 3
400.00
TOTAL ? ? ?
?

My query at the moment is:
SELECT Type, Count(Type) AS [Count for that type],
Count(Vendors) AS [No. Vendor],
Count(IIf(Vendors Like '*IBM*',Null,1)) AS [No Vendors not IBM],
Sum(Cost) AS [Sum Cost]
FROM [myTable]
GROUP BY Type;