I have NO idea about access - but something like this could work (and this is off the top of my head.. so syntax may be off somewhere)
Select candidate_id, candidate_name, first_count, second_count, third_count FROM tbl_candidate, ( Select count(first_candidate_id) as first_count, candidate_id from tbl_vote, tbl_cadidate where candidate_id = first_candidate_id group by first_candidate_id ) firstGroup, ( Select count(second_candidate_id) as second_count, candidate_id from tbl_vote, tbl_cadidate where candidate_id = second_candidate_id group by second_candidate_id ) secondGroup, ( Select count(third_candidate_id) as third_count, candidate_id from tbl_vote, tbl_cadidate where candidate_id = third_candidate_id group by third_candidate_id ) thirdGroup where candidate_id = firstGroup.candidate_id AND candidate_id = secondGroup.candidate_id AND candidate_id = thirdGroup.candidate_id That looks about right to me. No idea if it even works tho ;o) HTH Mark > James Silva <[EMAIL PROTECTED]> wrote: > > sorry for the OT post.. but this is my last resort. > > I've got 2 tables structured as follows: > > tbl_cadidate > ------------------ > candidate_id > candidate_name > > > tbl_vote > ------------------ > vote_id > first_candidate_id > second_candidate_id > third_candidate_id > > > Lets say tbl_candidate contains 3 records. The tbl_vote table has > several > hundred. > > I need a query that will return a table like so: > > --------------------------------------------------------- > candidate_name | first_count | second_count | third_count > --------------------------------------------------------- > moe | 150 | 100 | 50 > larry | 100 | 50 | 150 > curly | 50 | 150 | 50 > --------------------------------------------------------- > > e.g: first_count represents the COUNT() of each candidate in the > vote.first_candidate_id field. > > I know the db structure has not been normalised (i did not design it, > nor can i > change it now). > > Just want to know if this is possible with a single query. If not, > i'll need to > nest a query inside the output of another query (i hate doing this as > it hammers > the db). > > oh, and the kicker?... needs to work using MS Access :( > > TIA, > _______________________ > James Silva > Web Production > Gruden Pty Ltd > > Tel: +61 02 9956 6388 > Fax: +61 02 9956 8433 > Email: [EMAIL PROTECTED] > Web: www.gruden.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/ ----------------------------------- [EMAIL PROTECTED] ICQ: 3094740 Safe From Bees [www.safefrombees.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/
