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

Access '07: refine SQL so query shows unique values with totals instead of all values

P: 17
It has been a while since my last visit, but first off, I would like to thank everyone who helped me creating my first movie database some two years ago. Although it worked pretty good, it made me realize that it didn't follow the rules of normalization at all. A typical first timer mistake I guess. So now I've made a new one that does and it works perfectly. I'm just having trouble with creating the right queries that I want. One of these problems is:

The following query returns a list of all the directors in the database and the movie they made.

Expand|Select|Wrap|Line Numbers
  1. SELECT Director.Lastname, Director.Firstname, LinkFilmDirector.[Film ID], LinkFilmDirector.[Director ID], Film.Title
  2. FROM Director INNER JOIN (Film INNER JOIN LinkFilmDirector ON Film.[Film ID] = LinkFilmDirector.[Film ID]) ON Director.[Director ID] = LinkFilmDirector.[Director ID];

The will give a result like:

Lastname Firstname Film ID Director ID Title
Spielberg Steven 1 55 Close Encounters Of The Third Kind
Spielberg Steven 2 55 Color Purple, The
Spielberg Steven 3 55 Amistad
Spielberg Steven 4 55 Catch Me If You Can

What I would like is a query that returns the name of a director just once and next to that name, in a new table, a number that shows the amount of movies.

In layman's terms:

Lastname Firstname Director ID Amount
Spielberg Steven 55 4

I hope I have made myself clear.

Thank you in advance. Margie
Jan 10 '10 #1

✓ answered by nico5038

For this you need a "group by" query like:
Expand|Select|Wrap|Line Numbers
  1. SELECT Director.Lastname, Director.Firstname, LinkFilmDirector.[Director ID], count(*)
  2. FROM Director INNER JOIN (Film INNER JOIN LinkFilmDirector ON Film.[Film ID] = LinkFilmDirector.[Film ID]) ON Director.[Director ID] = LinkFilmDirector.[Director ID]
  3. group by Director.Lastname, Director.Firstname, LinkFilmDirector.[Director ID];
Nic;o)

Share this Question
Share on Google+
4 Replies


nico5038
Expert 2.5K+
P: 3,072
For this you need a "group by" query like:
Expand|Select|Wrap|Line Numbers
  1. SELECT Director.Lastname, Director.Firstname, LinkFilmDirector.[Director ID], count(*)
  2. FROM Director INNER JOIN (Film INNER JOIN LinkFilmDirector ON Film.[Film ID] = LinkFilmDirector.[Film ID]) ON Director.[Director ID] = LinkFilmDirector.[Director ID]
  3. group by Director.Lastname, Director.Firstname, LinkFilmDirector.[Director ID];
Nic;o)
Jan 10 '10 #2

P: 17
Works perfectly and what quick response!
Now to study it and work with it a little so that I also understand the code instead of just accepting the answer.

Many thanks! Margie
Jan 10 '10 #3

nico5038
Expert 2.5K+
P: 3,072
Check out some theory about the Group By query like http://www.blueclaw-db.com/accessque...clause_sql.htm.
Basically it's always a number of "group by" fields and one or more aggregation functions like Count(), Max(), etc.

Success !

Nic;o)
Jan 10 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
Furthermore, all results in the SELECT clause must either be relative to an item in the GROUP BY list, or be aggregated in some way. Aggregation functions are things like Sum(), First(), Max() etc. Any reference to a field must fit either of these stipulations.

PS. Finding Jet SQL Help may be worth a look too. Access does have help on SQL. It's just not too easy to find.

Welcome (back) to Bytes!
Jan 11 '10 #5

Post your reply

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