ACCESS FEED: [MS_AccessPros] Union query dilemma

Sabtu, 04 Januari 2014

[MS_AccessPros] Union query dilemma

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;