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
4 1877 nico5038 3,080
Recognized Expert Specialist
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)
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
NeoPa 32,564
Recognized Expert Moderator MVP
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!
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why...
|
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what a spreadsheet might show, the
column names will actually be dynamic, based on data from a SQL Server 2000
database. The row data will also come...
|
by: Peter Frost |
last post by:
Please help
I don't know if this is possible but what I would really like to do is
to use On Error Goto to capture the code that is being executed when
an error occurs.
Any help would be much appreciated.
Thanks in advance
|
by: Phil Longworth |
last post by:
Im very new to Access 97 and Im sure I should be able to do this but
cant work out how.
Im bulding a database for my stamp collection. I have two tables; one
with details of all the individual stamps, and one with details of
covers/envelopes the stamps are fixed to. Each indivdual stamp has a
specific reference number (not an auto-number). ...
|
by: Eric Ellsworth |
last post by:
Hi all,
Does anyone have any bright ideas for Access' tendency to add square
brackets when it parses queries, then tell you that the query syntax is
invalid. In my case, I'm trying to do a LEFT JOIN on a subquery, like so:
SELECT DISTINCT .
FROM LEFT JOIN (SELECT FROM targetMarkers
WHERE targetID = Forms!frmTargetDetails ) as TM
ON ....
| |
by: Nicolae Fieraru |
last post by:
Hi All,
I have a Table1 with ID, Name, Address, Purchase.
I want to build a query which shows all the records from Table1 (only fields
ID, Name, Address), but there shouldn't be listed records which have the
same Name and Address (if a customer made two different purchases, he should
be listed only once).
Any help appreciated
Regards,
|
by: sara |
last post by:
I am helping a non-profit track their elder clients and care given to
the clients.
The organization would like a report that shows various info on ALL
clients, such as:
# in each town,
# and Percent Male, Female, Smoker,
# in age range
# that use wheelchair or walker.
|
by: markcarroll |
last post by:
I have a rather complicating query (the SQL is about a page long) so I hope I can solve this without needing to get into specifics.
Basically, the database I am working on has information about investment firms, and what companies they hold shares of. I set up the query to find all the stocks that a specified company held for the most recent...
|
by: colin spalding |
last post by:
How do I design a totals query that instead of the following result
YearOfAcc PremiumGBP
2004 9,142,306.95
2004 1,481,153.21
2005 11,981,987.85
2006 20,653,195.20
2006 -521,140.08
2006 -455,788.20
2006 -212,507.35
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |