How may I use Count and Sum in Union Query (1 Viewer)

Samadkhan

Registered User.
Local time
Today, 23:24
Joined
Sep 23, 2018
Messages
31
Hi all,

I have created Union query based on two tables tblValAset and VhclAprs, got stuck, how may I apply Count and sum in this union query?
-----------------------------
SELECT ReportDt, BnkNme, BnkCod, SrviceChrgs, RecdAmt, TaxDeduct
FROM tblValAset
UNION ALL
SELECT ReportDt, BnkNme, BnkCod, SrvicChrgs AS SrviceChrgs, RecdAmt, TaxDeduct
FROM tblVhclAprsl;


Thank you very much in advance..

Thanks & regards,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:24
Joined
May 7, 2009
Messages
19,169
Create a new Aggregate (total) from the union query you have.
 

Samadkhan

Registered User.
Local time
Today, 23:24
Joined
Sep 23, 2018
Messages
31
I tried New Query by adding my Union Query with Total function, now it is producing duplicate BnkNme where I want only one BnkNme to appear as both table contain the same BnkNme...

I'm trying to use count function with BnkCod to count the BnkNme so I get one BnkNme ...

Please help with thanks

Create a new Aggregate (total) from the union query you have.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:24
Joined
May 7, 2009
Messages
19,169
because you have date field there, so it will come double or triple since the date is ever changing. remove it from the union.
 

Samadkhan

Registered User.
Local time
Today, 23:24
Joined
Sep 23, 2018
Messages
31
Thank you very much, Now it's working the ways I wanted.

However, how may I use Date filed with this query to generate summary report?

Please help thank you!


because you have date field there, so it will come double or triple since the date is ever changing. remove it from the union.
 

Samadkhan

Registered User.
Local time
Today, 23:24
Joined
Sep 23, 2018
Messages
31
Dear,

The following is my union query I wanted to create summary report based on ReportDt and BnkNme to calculate the SrviceChrgs-RecdAmt-TaxDeduct=Outstandings << New Field.

Please help with thanks..

SELECT ReportDt, BnkNme, BnkCod, SrviceChrgs, RecdAmt, TaxDeduct
FROM tblValAset
UNION ALL SELECT ReportDt, BnkNme, BnkCod, SrvicChrgs AS SrviceChrgs, RecdAmt, TaxDeduct
FROM tblVhclAprsl;


Post the aggregate query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:24
Joined
May 7, 2009
Messages
19,169
you now have 2 queries:

union
summary

create yet another union query, this time only BnkCod, ReportDt
again create a Total query against this union query.
group by BnkCod and add two columns for ReportDt using Min and Max.

another query, join the first summary and the query with Min, Max. join them on BnkCode.


now you can use the last query for reporting.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:24
Joined
May 7, 2009
Messages
19,169
or...

take your first summary query in design view.
add 2 column:

(select min(ReportDt) From UnionQuery1 As T1 Where T1.BnkCode=BnkCode) As StartDate, (select max(ReportDt) From UnionQuery1 As T1 Where T1.BnkCode=BnkCode) As EndDate
 

Samadkhan

Registered User.
Local time
Today, 23:24
Joined
Sep 23, 2018
Messages
31
I tried the following Union Query (SQL) where I used Count and Sum (Total).

The result is almost according to my needs, however, it's applying separately on both tables. If it apply on both table equally then I may be able to get my result according to my needs from one union query...

SELECT tblValAset.ReportDt, tblValAset.BnkNme, Count(tblValAset.BnkCod) AS CountOfBnkCod, Sum(tblValAset.SrviceChrgs) AS SumOfSrviceChrgs, Sum(tblValAset.RecdAmt) AS SumOfRecdAmt, Sum(tblValAset.TaxDeduct) AS SumOfTaxDeduct
FROM tblValAset
GROUP BY tblValAset.ReportDt, tblValAset.BnkNme
UNION ALL
SELECT tblVhclAprsl.ReportDt, tblVhclAprsl.BnkNme, Count(tblVhclAprsl.BnkCod) AS CountOfBnkCod, Sum(tblVhclAprsl.SrvicChrgs) AS SrviceChrgs, Sum(tblVhclAprsl.RecdAmt) AS SumOfRecdAmt, Sum(tblVhclAprsl.TaxDeduct) AS SumOfTaxDeduct
FROM tblVhclAprsl
GROUP BY tblVhclAprsl.ReportDt, tblVhclAprsl.BnkNme;

Please help me to sort this issue out if possible, I will be very thankful to you..

Thanks


or...

take your first summary query in design view.
add 2 column:

(select min(ReportDt) From UnionQuery1 As T1 Where T1.BnkCode=BnkCode) As StartDate, (select max(ReportDt) From UnionQuery1 As T1 Where T1.BnkCode=BnkCode) As EndDate
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:24
Joined
May 7, 2009
Messages
19,169
save the union query.
create another Total query using this query.
since you already Counted the BnkCod, this time use Sum().
and don't include the ReprtDt.
 

Samadkhan

Registered User.
Local time
Today, 23:24
Joined
Sep 23, 2018
Messages
31
Great!! The result is according to my needs.

Now the question is how may I use both queries in my summary report?...

I tried to use both queries in my report, and, seams the wizard can not connect both in my report...

I need report to be in following pattern where I would create an unbounded field ((Outstanding << I learnt how to do this so its not issue) for calculations SrvicChrgs - RecdAmt - TaxDeduct = Balance)

Date - BnkNme - BnkCod - SrvicChrgs - RecdAmt - TaxDeduct - Outstanding
Year - BnkNme - 5 - $1000 - $900 - $100 - 0

I highly appreciate your help.. Thank you very much!!


save the union query.
create another Total query using this query.
since you already Counted the BnkCod, this time use Sum().
and don't include the ReprtDt.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:24
Joined
May 7, 2009
Messages
19,169
Do you need the Year also for your Report?
You need to add that to your Union Query (with Total)
Code:
SELECT Year(tblValAset.ReportDt) As Year, tblValAset.BnkNme, Count(tblValAset.BnkCod) AS CountOfBnkCod, Sum(tblValAset.SrviceChrgs) AS SumOfSrviceChrgs, Sum(tblValAset.RecdAmt) AS SumOfRecdAmt, Sum(tblValAset.TaxDeduct) AS SumOfTaxDeduct
FROM tblValAset
GROUP BY Year(tblValAset.ReportDt), tblValAset.BnkNme
UNION ALL
SELECT Year(tblVhclAprsl.ReportDt) As Year, tblVhclAprsl.BnkNme, Count(tblVhclAprsl.BnkCod) AS CountOfBnkCod, Sum(tblVhclAprsl.SrvicChrgs) AS SrviceChrgs, Sum(tblVhclAprsl.RecdAmt) AS SumOfRecdAmt, Sum(tblVhclAprsl.TaxDeduct) AS SumOfTaxDeduct
FROM tblVhclAprsl
GROUP BY Year(tblValAset.ReportDt), tblVhclAprsl.BnkNme;

you then edit the last Total query you made against this union query to include the year.

to create a report, highlight the final query and on the ribbon click Report.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:24
Joined
Feb 19, 2002
Messages
42,973
Just FYI, the columns used in the where clause do not need to be included in the select clause. So, you can select data for October only but not include the actual date. Or you can include just the year and month but not the day. That makes the set of data more versatile since it still retains its place in time.
 

Users who are viewing this thread

Top Bottom