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/
