Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Union All - min & max on all results

Posted on 2014-07-28
4
Medium Priority
?
459 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 14

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 14

Accepted Solution

by:
Russell Fox earned 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Implementing simple internal controls in the Microsoft Access application.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 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