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
4  1621 
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
im not an expert with sql i just use the builder for mine but this line:
(SELECT qid,MAX(status) AS status2 FROM billing GROUP BY qid)
looks to me like you shouldt have it wrapped in brackets?
just a guess really
Dan
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
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 "
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jai |
last post by:
Say the following are the columns of a table
A B C D E F
Can a aggregate function like sum be applied to A like sum(a) and then
order by b and c similarly aggregate function on d and group by e...
|
by: Paxton |
last post by:
Hi,
I'm trying to display the total page views per page within a given date
range, but the correct SQL is seemingly beyond me. I get the correct result
with a straightforward Group By and Count...
|
by: Elvis |
last post by:
Hi,
i need to create a query on Access 2000 for select some records.
Here is an example with records inserted on my two tables.
TABLE ACQ:
ID ARTICLE PERIOD DATE QTY RESERVE...
|
by: Shawn |
last post by:
First I have to say I do not know SQL or VB very well...so if I'm able
to get this to work without learning a new language then I'll be
ecstatic.
OK...I have a tbl of approximately 1000 lines of...
|
by: Darin |
last post by:
I have a report that sub-totals on a group, then grand-totals at the
report footer. If there's only one group, the sub-total and grand
total are redundant, so I only want to show one of them. I...
|
by: Amadelle |
last post by:
Hi All and thanks in advance,
I was wondering what is the best way of grouping data in a dataset. Like
using a "Group By" clause in Sql statements. I was thinking of using XML
but I don't even...
|
by: Bill Moran |
last post by:
Hey all.
I've hit an SQL problem that I'm a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?
The following query:
SELECT GCP.id,...
|
by: jacob.dba |
last post by:
I have a table with first name, last name, SSN(social security number)
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar...
|
by: igotyourdotnet |
last post by:
I'm getting my dataset like this:
SalesMan Sales Make
Smith 25,000 1
Smith 9500 10
Smith 72,252 1
Smith 125,000 ...
|
by: Rudolf Bargholz |
last post by:
Perhaps some kind soul could help me out with an SQL I have been trying all
day to get to work, where one colum is just not summing up the way I want it
to.
I have the following data
GRP_SEQ ...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
