UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Union Select - Group By, Access 2016    
 
   
hunnibun
post Mar 26 2020, 08:04 AM
Post#1



Posts: 31
Joined: 18-October 13
From: North East


Can any one help I am trying to group all records by the study type so I can see which people are in which study at a glance

SELECT Qry_001_P.[Number], Qry_001_P.[First Name], Qry_001_P.[Second Name], Qry_001_P.[Year Of Birth], Qry_001_P.Study
FROM Qry_001_P
Union Select Qry_002_R.[Number], Qry_002_R.[First Name], Qry_002_R.[Second Name], Qry_002_R.[Year Of Birth],Qry_002_R.[Study]
FROM Qry_002_R
Union Select Qry_003_I.[Number], Qry_003_I.[First Name], Qry_003_I.[Second Name], Qry_003_I.[Year Of Birth], Qry_003_I.[Study]
FROM Qry_003_I
GROUP BY Study;
This post has been edited by hunnibun: Mar 26 2020, 08:04 AM
Go to the top of the page
 
orange999
post Mar 26 2020, 08:16 AM
Post#2



Posts: 2,076
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


You have posted SQL with no context or question?? What does this SQL do compared to what you expected it to do? What exactly are the other queries Qry_001_P, Qry_002_R, Qry_003_I?
This post has been edited by orange999: Mar 26 2020, 08:18 AM

--------------------
Good luck with your project!
Go to the top of the page
 
Gustav
post Mar 26 2020, 08:18 AM
Post#3


UtterAccess VIP
Posts: 2,204
Joined: 21-February 07
From: Copenhagen


Move Study to the first position:

CODE
Select Qry_001_P.Study, Qry_001_P.[Number], Qry_001_P.[First Name], Qry_001_P.[Second Name], Qry_001_P.[Year Of Birth]
From Qry_001_P
Union
Select Qry_002_R.[Study], Qry_002_R.[Number], Qry_002_R.[First Name], Qry_002_R.[Second Name], Qry_002_R.[Year Of Birth]
From Qry_002_R
Union
Select Qry_003_I.[Study], Qry_003_I.[Number], Qry_003_I.[First Name], Qry_003_I.[Second Name], Qry_003_I.[Year Of Birth]
From Qry_003_I

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
hunnibun
post Mar 26 2020, 09:00 AM
Post#4



Posts: 31
Joined: 18-October 13
From: North East


Hi Gustav,

Thank you for answering my query so quickly

I’ve tried as you suggested But get an error

Your study does not include the specified expression ‘number’ as part of an aggregate function

Hi Orange999 again thank you

The separate queries are simply pulling specific data from three separate linked live excel spreadsheets (spreadsheets p, r and i are different studies) the queries add the study name and additional columns that I have defined

Potentially there could be the same person in all three studies therefore I want a query to show me who is what study at a glance

E.g Joe bloggs could be on just two spreadsheets p and r but not i
And
Mary bracket could be on all three

Go to the top of the page
 
projecttoday
post Mar 26 2020, 10:12 AM
Post#5


UtterAccess VIP
Posts: 11,782
Joined: 10-February 04
From: South Charleston, WV


Why don't you just make a listing and sort it by study?

--------------------
Robert Crouser
Go to the top of the page
 
Gustav
post Mar 26 2020, 03:55 PM
Post#6


UtterAccess VIP
Posts: 2,204
Joined: 21-February 07
From: Copenhagen


QUOTE
Your study does not include the specified expression ‘number’ as part of an aggregate function


As there is no grouping in the union query, the message must be caused by one of the subqueries.

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
MadPiet
post Mar 26 2020, 04:25 PM
Post#7



Posts: 3,550
Joined: 27-February 09



CODE
SELECT Qry_001_P.[Number], Qry_001_P.[First Name], Qry_001_P.[Second Name], Qry_001_P.[Year Of Birth], Qry_001_P.Study
FROM Qry_001_P
Union Select Qry_002_R.[Number], Qry_002_R.[First Name], Qry_002_R.[Second Name], Qry_002_R.[Year Of Birth],Qry_002_R.[Study]
FROM Qry_002_R
Union Select Qry_003_I.[Number], Qry_003_I.[First Name], Qry_003_I.[Second Name], Qry_003_I.[Year Of Birth], Qry_003_I.[Study]
FROM Qry_003_I
GROUP BY Study;


Any column that you're not grouping by must be an aggregate (SUM, MIN, MAX...)

What are you really trying to accomplish, UNION problem aside?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    29th March 2020 - 04:36 AM