• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 21
  • Last Modified:

how to get 2 different group totals in one access query?

I have 2 tables, t1 (col CustomerName, InvoiceNo) and t2 (col InvoiceNo, Model, SN)

Data is like:
Table1                                                     Table2
CustomerName   InvoiceNo                  InvoiceNo   Model  SN
Name1                 Inv1                            Inv1            A          A-1
Name1                 Inv2                            Inv1            B          B-1
Name1                 Inv3                            Inv1            A          A-2
Name2                 Inv4                            Inv2            B          B-2
Name2                 Inv5                            Inv2            C          C-1

I want to get a total (count(SN)) for InvoiceNo and also total for CustomerName.

SELECT t1.CustomerName, t1.InvoiceNo, COUNT(t2.SN) as Qty FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.CustomerName, t1.InvoiceNo

This only shows the total for InvoiceNo.  How can I get total for CustomerName and InvoiceNo in one query?  Or possible?

Thank you very much in advance.  HB
0
kg6lfz
Asked:
kg6lfz
  • 2
  • 2
1 Solution
 
Saurabh Singh TeotiaCommented:
You can use the following query to do what you are looking for...

SELECT t1.CustomerName, COUNT(t2.SN) as Qty 
FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.CustomerName

Union all 

SELECT t1.InvoiceNo, COUNT(t2.SN) as Qty 
FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.InvoiceNo;

Open in new window


Saurabh...
0
 
kg6lfzAuthor Commented:
Thank you for your suggestions.  They query returns the result but it's kind of hard to summarize.  Is it possible to show the result like
Name1  Inv1   2
Name1  Inv2   1
Name1  Inv3   1
  Name1          4
Name2  Inv4   3
Name2  Inv5   2
Name2  Inv6   1
  Name2          6

I appreciate your inputs.  Thank you very much.
0
 
Saurabh Singh TeotiaCommented:
Use the below mentioned query...

select t10.CustomerName,t10.invoice,t10.QTY
from 
(SELECT t1.CustomerName,t1.InvoiceNo as invoice,COUNT(t2.SN) as Qty 
FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.CustomerName,t1.InvoiceNo

Union all 

SELECT t1.CustomerName,'Total' as Invoice , COUNT(t2.SN) as Qty 
FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.CustomerName)t10
order by t10.customername;

Open in new window


Saurabh...
0
 
kg6lfzAuthor Commented:
Great.  That was what I wanted.  Very appreciated.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now