James >> i initially tried using table aliases... access didn't approve :(
hmmm...are you sure? SELECT a.nameAuthor,a.emailAuthor, a.levelAuthor FROM tAuthor a WHERE (((a.levelAuthor)>1)); works in Access SQL... cheers barry.b -----Original Message----- From: James Silva [mailto:[EMAIL PROTECTED] Sent: Tuesday, 22 July 2003 10:06 AM To: CFAussie Mailing List Subject: [cfaussie] RE: SQL fun Mike, i initially tried using table aliases... access didn't approve :( Heres (a simplified version of) what i ended up with... (credit goes to Onnis): SELECT candidate_name, (SELECT COUNT(first_count) FROM tbl_vote WHERE first_count = candidate_id) AS [FirstChoice], (SELECT COUNT(second_count) FROM tbl_vote WHERE second_count = candidate_id) AS [Second Choice], (SELECT COUNT(third_count) FROM tbl_vote WHERE third_count = candidate_id) AS [Third Choice] FROM tbl_cadidate 3 nested queries... blindingly obvious... :( back to the corner i go... _______________________ James Silva Web Production Gruden Pty Ltd Tel: +61 02 9956 6388 Fax: +61 02 9956 8433 Email: [EMAIL PROTECTED] Web: www.gruden.com _______________________ > table alias may help: > > select > tbl_cadidate.candidate_name, > count(fist.first_candidate_id) as first, > count(second.first_candidate_id) as second, > count(third.first_candidate_id) as second > from > tbl_cadidate, > tbl_vote as first, > tbl_vote as second, > tbl_vote as third > where > tbl_cadidate.candidate_id = first.first_candidate_id and > tbl_cadidate.candidate_id = second.second_candidate_id and > tbl_cadidate.candidate_id = third.third_candidate_id > > +----------------------------------------------------+ > | VeriPay mPOS - Anywhere, Anytime | > | Puts money in your bank while you're on the road | > | Secure Credit Card Transactions via SMS messaging | > | From Xilo Online www.xilo.com/mpos | > +----------------------------------------------------+ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
