By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,574 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Need help rewriting this SQL syntax to get rid of doubletes

P: 2
The SQL question I want to answer is: Get a list of the exhibitions and the number of artists in every exhibition.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT exhibitionname, COUNT(Artist) AS antal
  2. FROM Exhibition, ExhibitionOccasion, Painting
  3. WHERE Exhibition.ExhibtionID=ExhibtionOccasion.ExhibitionID And 
  4. Painting.PaintID=ExhibitionOccasion.PaintID
  5. GROUP BY exhibitionname;
Hello :)! I need to get rid of the doublets in this SQL code. What can I add here to the SQL syntax to get tid of the doubletes. I have been looking at another solution but keep getting syntax errors when I want to change it and adapt it to the SQL syntax above. Please rewrite the code and post it here because I've been trying many times and I'm stuck. Your help is very much appreciated.
Expand|Select|Wrap|Line Numbers
  1. SELECT Count([%$##@_Alias].personalsecnumber) AS [antal subjects], Person.f_name, Person.e_name
  2. FROM [SELECT DISTINCT subjectid,personalsecnumber
  3. FROM Author INNER JOIN Paper ON Author.paperid = Paper.paperid]. AS [%$##@_Alias] INNER JOIN Person ON [%$##@_Alias].personalsecnumber= Person.personalsecnumber
  4. GROUP BY Person.f_name, Person.e_name, [%$##@_Alias].personalsecnumber;
May 3 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
Hi. You have three tables listed in your SQL statement, and no JOINs - this will multiply the rows of each together (called a Cartesian product). Your WHERE clause will not fully restrict the rows you see because you only have two of the three tables filtered in the WHERE.

First step is to JOIN the tables (do this in the Access query editor by dragging a join line from one ID field to the matching ones in the table being joined). If you have relationships defined for these tables (as you should if you have designed them properly) then Access will use these relationships to create the joins for you in the Access query editor when you add the tables to the query window.

To create a total query in the query editor select View, Totals, remove any fields that you don't want to group the count on, add another field to the query and change its Group By clause to a Count, giving it a new name (which will create the Alias for you in the SQL).

It is a principle of this forum that as far as possible the work for assignment-type questions is done by the original poster of the question - we can't simply write the SQL for you. We can point you in the right direction, though. Please read the Posting Guidelines for further clarification, and in particular the FAQs on homework/assignments.

-Stewart
May 3 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.