Solved

Union All - min & max on all results

Posted on 2014-07-28
4
441 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

738 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