group by distinct

hey guys i have a problem with dups in my statement. ive tried using multiple select statements but i just cant get it. any help would be greatly appreciated.

here is the sql:

SqlJunk = " SELECT distinct count(*) as icount, Quiz.LessonID, Quiz.Number, quiz.registerdate, Quiz.Market, Quiz.Division, Quiz.LastName, Quiz.zip, Quiz.ea, Quiz.fee, Quiz.FirstName, " & _
" Quiz.phone, Quiz.phone_work, Quiz.phone_cell, Quiz.phone_fax, Quiz.ssn, Quiz.ssn_her, Quiz.email, Quiz.city, Quiz.state, Quiz.tax_consultant, " & _
" Quiz.business_name, Quiz.business_address, Quiz.business_city, Quiz.business_zip, Quiz.business_state, Quiz.qID, Marks.userID, Marks.lessonID, Marks.mark, Marks.qID , " & _
" Marks.ert, Marks.lien, Marks.alarm1, Marks.a1_comments, Marks.a1_onoff, Marks.alarm2, Marks.a2_comments, Marks.a2_onoff, Marks.alam3, Marks.a3_comments, Marks.a3_onoff, " & _
" Marks.packet, Marks.poa, Marks.engagement, Marks.taxreturns, Marks.oicsubmitted, Marks.oiccomplete, Marks.installment, Marks.hardship, Marks.levy, " & _
" Marks.ins_health, Marks.ins_life, Marks.payroll_svc, Marks.cancelled, Marks.closed, marks.vote , sum(Billing.Amount) as sumamount , billing.status " & _
" FROM Quiz LEFT JOIN (Billing RIGHT JOIN Marks ON Billing.qID = Marks.qID) ON Quiz.qID = Marks.qID " & _
" " & _
" GROUP BY Quiz.LessonID, Quiz.Number, quiz.registerdate, Quiz.Market, Quiz.Division, Quiz.LastName, Quiz.zip, " & _
" Quiz.ea, Quiz.fee, Quiz.lead, Quiz.FirstName, Quiz.phone, Quiz.phone_work, Quiz.phone_cell, Quiz.phone_fax, Quiz.ssn, Quiz.ssn_her, Quiz.email, Quiz.city, Quiz.state, " & _
" Quiz.tax_consultant, Quiz.business_name, Quiz.business_address, Quiz.business_city, Quiz.business_zip, Quiz.business_state, Quiz.qID, Marks.userID, Marks.lessonID, Marks.mark, Marks.qID, " & _
" Marks.ert, Marks.lien, Marks.alarm1, Marks.a1_comments, Marks.a1_onoff, Marks.alarm2, Marks.a2_comments, Marks.a2_onoff, Marks.alam3, Marks.a3_comments, Marks.a3_onoff, Marks.packet, " & _
" Marks.poa, Marks.engagement, Marks.taxreturns, Marks.oicsubmitted, Marks.oiccomplete, Marks.installment, Marks.hardship, " & _
" Marks.levy, Marks.ins_health, Marks.ins_life, Marks.payroll_svc, Marks.cancelled, " & _
" Marks.closed, marks.vote, billing.status "

the results bring back multiples because status can be equal to 1 or 2 but i just want distinct on the group by.

table structure:

table quiz
client information

table marks
marking services complete or not

table billing
has the multiples in it - has a qid for each payment that is entered - this is where my problem occurs.

thanks again

re: group by distinct

i researched this problem and tried fixing it using some selects. i am still getting an error. thx for the help

SELECT * FROM quiz q LEFT JOIN marks m ON m.qid=q.qid LEFT JOIN (SELECT b1.* FROM billing b1 INNER JOIN (SELECT qid,MAX(status) AS status2 FROM billing GROUP BY qid) b2 ON b2.qId=b1.qId AND b2.status2=b1.qId)b ON b.qId=e.qId



error:
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'm.qid=q.qid LEFT JOIN (SELECT b1.* FROM billing b1 INNER JOIN (SELECT qid,MAX(status) AS status2 FROM billing GROUP BY qid) b2 ON b2.qId=b1.qId'.

/Engine2.asp, line 591

re: group by distinct

im not an expert with sql i just use the builder for mine but this line:

looks to me like you shouldt have it wrapped in brackets?

just a guess really

Dan

re: group by distinct

yeah im using mutiple selects to join and remove duplicates - i dont think the brackets are the problem with the code - but i appriciate the response man. also i corrected an error heres the updated code:

SELECT * FROM quiz q LEFT JOIN marks m ON m.qid=q.qid LEFT JOIN (SELECT b1.* FROM billing b1 INNER JOIN (SELECT qid,MAX(status) AS status2 FROM billing GROUP BY qid)b2 ON b2.qid=b1.qid AND b2.status2=b1.qid)b ON b.qid=q.qid

i could be getting nulls or somehting that would throw the code off

re: group by distinct

i just cheated and used min,max, and avg to remove the duplicates but id still like to know how to fix the above code. here is the working code:


" SELECT count(*) as icount, Quiz.LessonID, Quiz.Number, quiz.registerdate, Quiz.Market, Quiz.Division, Quiz.LastName, Quiz.zip, Quiz.ea, Quiz.fee, Quiz.FirstName, " & _
" Quiz.phone, Quiz.phone_work, Quiz.phone_cell, Quiz.phone_fax, Quiz.ssn, Quiz.ssn_her, Quiz.email, Quiz.city, Quiz.state, Quiz.tax_consultant, " & _
" Quiz.business_name, Quiz.business_address, Quiz.business_city, Quiz.business_zip, Quiz.business_state, Quiz.qID, Marks.userID, Marks.lessonID, Marks.mark, Marks.qID , " & _
" Marks.ert, Marks.lien, Marks.alarm1, Marks.a1_comments, Marks.a1_onoff, Marks.alarm2, Marks.a2_comments, Marks.a2_onoff, Marks.alam3, Marks.a3_comments, Marks.a3_onoff, " & _
" Marks.packet, Marks.poa, Marks.engagement, Marks.taxreturns, Marks.oicsubmitted, Marks.oiccomplete, Marks.installment, Marks.hardship, Marks.levy, " & _
" Marks.ins_health, Marks.ins_life, Marks.payroll_svc, Marks.cancelled, Marks.closed, marks.vote , sum(Billing.Amount) as sumamount , max(billing.status) as status , min(billing.status) as mstatus , avg(billing.status) as astatus " & _
" FROM Quiz inner JOIN (Billing inner JOIN Marks ON Billing.qID = Marks.qID) ON Quiz.qID = Marks.qID " & _
" " & _
" GROUP BY Quiz.LessonID, Quiz.Number, quiz.registerdate, Quiz.Market, Quiz.Division, Quiz.LastName, Quiz.zip, " & _
" Quiz.ea, Quiz.fee, Quiz.lead, Quiz.FirstName, Quiz.phone, Quiz.phone_work, Quiz.phone_cell, Quiz.phone_fax, Quiz.ssn, Quiz.ssn_her, Quiz.email, Quiz.city, Quiz.state, " & _
" Quiz.tax_consultant, Quiz.business_name, Quiz.business_address, Quiz.business_city, Quiz.business_zip, Quiz.business_state, Quiz.qID, Marks.userID, Marks.lessonID, Marks.mark, Marks.qID, " & _
" Marks.ert, Marks.lien, Marks.alarm1, Marks.a1_comments, Marks.a1_onoff, Marks.alarm2, Marks.a2_comments, Marks.a2_onoff, Marks.alam3, Marks.a3_comments, Marks.a3_onoff, Marks.packet, " & _
" Marks.poa, Marks.engagement, Marks.taxreturns, Marks.oicsubmitted, Marks.oiccomplete, Marks.installment, Marks.hardship, " & _
" Marks.levy, Marks.ins_health, Marks.ins_life, Marks.payroll_svc, Marks.cancelled, " & _
" Marks.closed, marks.vote "