Solved

Union All - min & max on all results

Posted on 2014-07-28
4
435 Views
Last Modified: 2014-07-28
Hi all,

I have this union all query that I need to get the MIN and MAX values of the overall result, not each part of the union all. How do you do that in Access?

Select 
  Val(RO.CUST_NO) As CUST_NO,
  MIN(RO.RODATE) As createdstamp,
  MAX(RO.RODATE) As editstamp
From
  RO
Group By
  RO.CUST_NO
Union All
Select 
  Val(HRO.CUST_NO) As CUST_NO,
  MIN(HRO.RODATE) As createdstamp,
  MAX(HRO.RODATE) As editstamp
From
  HRO
Group By
  HRO.CUST_NO

Open in new window

0
Comment
Question by:ckelsoe
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40225770
This is how I would do it in SQL Server, but I think it works in Access, too:
SELECT MAX(createdstamp)
FROM (
	Select 
	  Val(RO.CUST_NO) As CUST_NO,
	  MIN(RO.RODATE) As createdstamp,
	  MAX(RO.RODATE) As editstamp
	From
	  RO
	Group By
	  RO.CUST_NO
	Union All
	Select 
	  Val(HRO.CUST_NO) As CUST_NO,
	  MIN(HRO.RODATE) As createdstamp,
	  MAX(HRO.RODATE) As editstamp
	From
	  HRO
	Group By
	  HRO.CUST_NO
	) t1

Open in new window

0
 

Author Comment

by:ckelsoe
ID: 40225785
Ok - that returned the min and max of the results over all. I need it by the cust_id - something like this except working code

SELECT CUST_NO, MIN(createdstamp), MAX(editstamp)
FROM (
	Select 
	  Val(RO.CUST_NO) As CUST_NO,
	  MAX(RO.RODATE) As createdstamp,
	  MAX(RO.RODATE) As editstamp
	From
	  RO
	
	Union All
	Select 
	  Val(HRO.CUST_NO) As CUST_NO,
	  MAX(HRO.RODATE) As createdstamp,
	  MAX(HRO.RODATE) As editstamp
	From
	  HRO
	
	) t1
Group By
	CUST_NO

Open in new window

0
 
LVL 13

Accepted Solution

by:
Russell Fox earned 500 total points
ID: 40225798
If you're doing the GROUP BY on the outside, you don't need it inside. Just pull all data inside the t1 select statement and then do the GROUP BY & MIN/MAX:
SELECT CUST_NO, MIN(createdstamp), MAX(editstamp)
FROM (
	Select 
	  Val(RO.CUST_NO) As CUST_NO,
	  RO.RODATE As createdstamp,
	  RO.RODATE As editstamp
	FROM RO
	Union All
	Select 
	  Val(HRO.CUST_NO) As CUST_NO,
	  HRO.RODATE As createdstamp,
	  HRO.RODATE As editstamp
	FROM HRO
	) t1
Group BY t1.CUST_NO

Open in new window

Alternatively, do the GROUP BY within each half of the UNION query:
SELECT CUST_NO, MIN(createdstamp), MAX(editstamp)
FROM (
	Select 
	  Val(RO.CUST_NO) As CUST_NO,
	  MAX(RO.RODATE) As createdstamp,
	  MAX(RO.RODATE) As editstamp
	From RO
	GROUP BY Val(RO.CUST_NO)
	
	Union All
	Select 
	  Val(HRO.CUST_NO) As CUST_NO,
	  MAX(HRO.RODATE) As createdstamp,
	  MAX(HRO.RODATE) As editstamp
	From HRO
	GROUP BY Val(HRO.CUST_NO)
	) t1
Group BY CUST_NO

Open in new window

0
 

Author Closing Comment

by:ckelsoe
ID: 40225810
Thank you for the help and quick results.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question