Robin-
Why is there still a semi-colon in your SQL?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Thursday, May 19, 2011 4:00 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Syntax problem with my first 'derived' query.
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
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar