Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-14-2018, 10:50 PM   #1
Samadkhan
Newly Registered User
 
Join Date: Sep 2018
Posts: 20
Thanks: 24
Thanked 0 Times in 0 Posts
Samadkhan is on a distinguished road
How may I use Count and Sum in Union Query

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,

Samadkhan is offline   Reply With Quote
Old 10-14-2018, 11:08 PM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,182 Times in 2,093 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How may I use Count and Sum in Union Query

Create a new Aggregate (total) from the union query you have.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Samadkhan (10-14-2018)
Old 10-14-2018, 11:17 PM   #3
Samadkhan
Newly Registered User
 
Join Date: Sep 2018
Posts: 20
Thanks: 24
Thanked 0 Times in 0 Posts
Samadkhan is on a distinguished road
Re: How may I use Count and Sum in Union Query

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

Quote:
Originally Posted by arnelgp View Post
Create a new Aggregate (total) from the union query you have.

Samadkhan is offline   Reply With Quote
Old 10-14-2018, 11:29 PM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,182 Times in 2,093 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How may I use Count and Sum in Union Query

because you have date field there, so it will come double or triple since the date is ever changing. remove it from the union.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Samadkhan (10-14-2018)
Old 10-14-2018, 11:40 PM   #5
Samadkhan
Newly Registered User
 
Join Date: Sep 2018
Posts: 20
Thanks: 24
Thanked 0 Times in 0 Posts
Samadkhan is on a distinguished road
Re: How may I use Count and Sum in Union Query

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!


Quote:
Originally Posted by arnelgp View Post
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 is offline   Reply With Quote
Old 10-14-2018, 11:42 PM   #6
Samadkhan
Newly Registered User
 
Join Date: Sep 2018
Posts: 20
Thanks: 24
Thanked 0 Times in 0 Posts
Samadkhan is on a distinguished road
Re: How may I use Count and Sum in Union Query

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;


Quote:
Originally Posted by June7 View Post
Post the aggregate query.
Samadkhan is offline   Reply With Quote
Old 10-14-2018, 11:50 PM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,182 Times in 2,093 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How may I use Count and Sum in Union Query

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.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Samadkhan (10-14-2018)
Old 10-14-2018, 11:58 PM   #8
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,182 Times in 2,093 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How may I use Count and Sum in Union Query

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Samadkhan (10-15-2018)
Old 10-16-2018, 12:10 AM   #9
Samadkhan
Newly Registered User
 
Join Date: Sep 2018
Posts: 20
Thanks: 24
Thanked 0 Times in 0 Posts
Samadkhan is on a distinguished road
Re: How may I use Count and Sum in Union Query

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


Quote:
Originally Posted by arnelgp View Post
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 is offline   Reply With Quote
Old 10-16-2018, 12:21 AM   #10
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,182 Times in 2,093 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How may I use Count and Sum in Union Query

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Samadkhan (10-16-2018)
Old 10-16-2018, 01:48 AM   #11
Samadkhan
Newly Registered User
 
Join Date: Sep 2018
Posts: 20
Thanks: 24
Thanked 0 Times in 0 Posts
Samadkhan is on a distinguished road
Re: How may I use Count and Sum in Union Query

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!!


Quote:
Originally Posted by arnelgp View Post
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 is offline   Reply With Quote
Old 10-16-2018, 02:58 AM   #12
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,182 Times in 2,093 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: How may I use Count and Sum in Union Query

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Samadkhan (10-16-2018)
Old 10-18-2018, 08:29 PM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: How may I use Count and Sum in Union Query

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
UNION / UNION ALL Query Crashed Access LB79 Queries 3 06-11-2017 03:22 AM
Union Query (2013) - qry using union pulling SOME dups dragct Queries 7 11-03-2015 06:06 AM
count rows of union query pavlos Queries 10 07-07-2009 12:20 AM
Union Query and Count At Same Time rfear Queries 8 01-06-2009 08:42 AM
UNION vs. UNION ALL query Baldrick Queries 3 11-26-2001 08:29 AM




All times are GMT -8. The time now is 06:34 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World