Solved

Union All - min & max on all results

Posted on 2014-07-28
4
427 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hit enter key to run macro 13 23
sql server insert 12 30
SYbase 4 27
is Microsoft Access going to Die? 9 44
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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