I use a code generator to make ASP pages.
The software does not accept UNION queries. I am advised to make a
derived table. I have followed their advice:
""SELECT * FROM (<Your UNION SQL>) AS t - to create your query in
Access. 2. There should not be any ";" in the SQL""
Which gives me a syntax error in Access but does not define the error:
SELECT * FROM (SELECT tClubPostHolders.MemberID,
tClubPostHolders.YearID, tClubPostHolders.ClubID, 'Club
'+tClubPosts.ClubPost AS PostHeld FROM tClubPosts RIGHT JOIN
(tMembers RIGHT JOIN tClubPostHolders ON tMembers.MemberID =
tClubPostHolders.MemberID) ON tClubPosts.tblClubPostID =
tClubPostHolders.ClubPostID WHERE (((tMembers.Status)=1 Or
(tMembers.Status)=2)) UNION SELECT tDistrictPostHolders.MemberID,
tDistrictPostHolders.YearID, Members.ClubID, 'District
'+tDistrictPosts.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));) AS qPostsHeld
This is my original SQL which does work:
SELECT tClubPostHolders.MemberID, tClubPostHolders.YearID,
tClubPostHolders.ClubID, 'Club '+tClubPosts.ClubPost AS PostHeld FROM
tClubPosts RIGHT JOIN (tMembers RIGHT JOIN tClubPostHolders ON
tMembers.MemberID = tClubPostHolders.MemberID) ON
tClubPosts.tblClubPostID = tClubPostHolders.ClubPostID WHERE
(((tMembers.Status)=1 Or (tMembers.Status)=2));
UNION SELECT tDistrictPostHolders.MemberID,
tDistrictPostHolders.YearID, Members.ClubID, 'District
'+tDistrictPosts.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));
I am well out of my depth.
What have I done?
Many thanks,
Robin Chapple
Tidak ada komentar:
Posting Komentar