ACCESS FEED: Re: [MS_AccessPros] Union query dilemma

Minggu, 05 Januari 2014

Re: [MS_AccessPros] Union query dilemma

Thanks John,

That is another new trick!!!!

Robin

At 5/01/2014 07:30 PM, you wrote:

Robin-

In a UNION query, you can have only one ORDER BY clause at the very end of the query. At that point, the field names have lost their table affiliation. In fact, the field names get inherited from the field names you use in the first query in the UNION. Duane used positional indicators in the ORDER BY clause (sort on the 2nd field, then the 3rd field), but he could have also done:

ORDER BY LastName, YearID

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 5, 2014, at 1:42 AM, Robin Chapple <robinski@mymail.net.au > wrote:

Thanks Duanne,

That certainly works. Now I must look at the construction to understand it!!

Cheers,

Robin

At 5/01/2014 11:27 AM, you wrote:

Try this SQL. Notice I changed the OR to IN for simplification.

SELECT tClubPostHolders.MemberID, Members.LastName,
tClubPostHolders.YearID, tClubPostHolders.ClubID, "Club " &
[ClubPost] AS PostHeld
FROM (tClubPosts INNER JOIN (tRotaryYear RIGHT JOIN tClubPostHolders
ON tRotaryYear.YearID = tClubPostHolders.YearID) ON
tClubPosts.tblClubPostID = tClubPostHolders.ClubPostID) INNER JOIN
Members ON tClubPostHolders.MemberID = Members.MemberID
WHERE Members.status IN (1 ,2)
UNION SELECT Members.MemberID, Members.LastName,
tDistrictPostHolders.YearID, Members.ClubID, "District " & [DistPost]
AS PostHeld
FROM Members RIGHT JOIN (tDistrictPosts INNER JOIN
tDistrictPostHolders ON tDistrictPosts.DistPostID =
tDistrictPostHolders.DistPostID) ON Members.MemberID =
tDistrictPostHolders.MemberID
WHERE Members.status IN (1 ,2)
ORDER BY 2,3

Duane Hookom MVP
MS Access

> From: robinski@mymail.net.au
>
> I have a Union query which takes records from two tables to combine
> them. I planned to display the data in Last Name oder and then by the year.
>
> Access provides an error message that the field names must be the
> same for the Year order,
>
> Is it possible?
>
> Many thanks,
>
> Robin Chapple
>
>
> SELECT tClubPostHolders.MemberID, Members.LastName,
> tClubPostHolders.YearID, tClubPostHolders.ClubID, "Club " &
> [ClubPost] AS PostHeld
> FROM (tClubPosts INNER JOIN (tRotaryYear RIGHT JOIN tClubPostHolders
> ON tRotaryYear.YearID = tClubPostHolders.YearID) ON
> tClubPosts.tblClubPostID = tClubPostHolders.ClubPostID) INNER JOIN
> Members ON tClubPostHolders.MemberID = Members.MemberID
> WHERE (((Members.status)=1 Or (Members.status)=2))
> ORDER BY Members.LastName, tClubPostHolders.YearID;
>
> UNION SELECT Members.MemberID, Members.LastName,
> tDistrictPostHolders.YearID, Members.ClubID, "District " & [DistPost]
> AS PostHeld
> FROM Members RIGHT JOIN (tDistrictPosts INNER JOIN
> tDistrictPostHolders ON tDistrictPosts.DistPostID =
> tDistrictPostHolders.DistPostID) ON Members.MemberID =
> tDistrictPostHolders.MemberID
> WHERE (((Members.Status)=1 Or (Members.Status)=2))
> ORDER BY Members.LastName, tDistrictPostHolders.YearID;