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
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;
>
> 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;
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar