Union All - min & max on all results

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

ckelsoeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
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
ckelsoeAuthor Commented:
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
Russell FoxDatabase DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ckelsoeAuthor Commented:
Thank you for the help and quick results.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.