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

[Margie]

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.

SELECT Director.Lastname, Director.Firstname, LinkFilmDirector.[Film ID], LinkFilmDirector.[Director ID], Film.Title
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

[nico5038]

For this you need a "group by" query like:

SELECT Director.Lastname, Director.Firstname, LinkFilmDirector.[Director ID], count(*)
FROM Director INNER JOIN (Film INNER JOIN LinkFilmDirector ON Film.[Film ID] = LinkFilmDirector.[Film ID]) ON Director.[Director ID] = LinkFilmDirector.[Director ID]
group by Director.Lastname, Director.Firstname, LinkFilmDirector.[Director ID];

Nic;o)

[Margie]

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

[nico5038]

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)

[NeoPa]

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!