ACCESS FEED: RE: [MS_AccessPros] Union query dilemma

Sabtu, 04 Januari 2014

RE: [MS_AccessPros] Union query dilemma

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;