row which has TOTALS of coulmns?

C

confused

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;
 
N

Nikos Yannacopoulos

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
 
J

John

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
 
C

confused

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 said:
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;







.
Click to expand...
Click to expand...
 
C

confused

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

John Spencer (MVP)

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query, forcing order of rows 1
Select Query & Crosstab Query 1
trying to get a sum in access 2003 1
minimize repeating query criteria & generating monthly YTD totals 1
Filter by sum of counts 11
Problem 6
Which function to use......and how? 4
Weird Question regarding report but might be in query 9

Top