Access '07: Refine 'group by' query so it uses lowest value (out of several values)

[Margie]

With information gathered from a question I posted 3 weeks ago, I made the following query for my movie database:

SELECT Nation.Nation, LinkFilmNation.[Nation ID], Count(*) AS Total, Avg(Film.[Score]) AS [Average Score] 
FROM Film INNER JOIN Nation INNER JOIN LinkFilmNation ON Nation.[Nation ID] = LinkFilmNation.[Nation ID]) ON Film.[Film ID] = LinkFilmNation.[Film ID] 
GROUP BY Nation.Nation, LinkFilmNation.[Nation ID], Film.Status, Film.Ok 
HAVING (((Film.Status)="Seen") AND ((Film.Ok)=Yes)) 
ORDER BY Count(*) DESC;

The query shows how many movies a nation has listed (including an average score). The problem is that a movie might have several production nations and this query counts all of them. Instead, I want the query to just use the main nation. I can't figure it out by myself, so here I am again.

I think the answer lies in the linked table where each entry has a unique ID (LinkFilmNation ID). The table has three colums:

SELECT LinkFilmNation.[LinkFilmNation ID], LinkFilmNation.[Film ID], LinkFilmNation.[Nation ID]
FROM LinkFilmNation;

For each 'Film ID', the query should only select the 'LinkFilmNation ID' that has the lowest number (the lowest number is the first in line and represents the main production nation).

A simplified example in layman's terms:

According to the site IMDb.com, the movie The Matrix (Film ID = 25) has two production nations, the USA and Australia. For this movie the LinkFilmNation ID's are 1051 and 1052. With the current query I would get the following (simplified):

Nation ID Nation Total
53 USA 1
12 Australia 1

What a want is that each movie (aka Film ID) counts only one time; therefore the query should only use the entry marked 1051 and discard the other entry that goes with the same Film ID (1052). This would result in:

Nation ID Nation Total
53 USA 1

And this is what I want.

I hope I made myself clear enough. Thank you. Margie

[Delerna]

I think you need a query of the table that you can then use instead of the table in other queries.
Something like

SELECT b.[LinkFilmNation ID] , 
       b.[Film ID], 
       b.[Nation ID]
FROM
(   SELECT [LinkFilmNation ID] as LnkFlmNatID, 
           [Film ID]
     FROM LinkFilmNation
     GROUP BY [Film ID]
)a
JOIN LinkFilmNation b on a.[LnkFlmNatID]=b.[LinkFilmNation ID]
                     and a.[Film ID]=b.[Film ID]
 

if you name that query ....say....qryLinkFilmNation
then you can use it instead of your LinkFilmNation table
in your other query.

[NeoPa]

It would be quite helpful to see some example data laid out in table form, rather than a few oblique references to them in your explanation.

There's little information as to what data you are dealing with in your tables. You simply refer to them. That leaves us having to work harder to try to work out what you're dealing with. It's easier if you tell us.

Fundamentally, a sub-query (similar to Delerna's example) could well work. It may be more complicated than required though. Hence the need for an understanding of the question.

[Margie]

I was afraid this would happen. Explaining things to others isn't a strength of mine. I tend to make things much more difficult then necessary. Thank you for your patience! And sorry for the long wait. Tomorrow is my day off, so I can respond much quicker.

I hope the following screens help to clarify things:

Screen 1: in this part of the form I enter the production nations of a movie; this shows the information of a specific movie (Film ID 2)

Screen 2: this is the table that contains all the nations and their ID's

Screen 3: in this table each nation that's linked to a movie get's a unique ID (LinkFilmNation ID); The information in the first screen (USA, France, UK) is shown here as LinkFilmNation ID 2, 3 and 4 (Film ID is the ID of a specific movie)

Screen 4: this is a sample of the query I'm trying to perfect; Total represents the number of times a nation has been selected

The problem with the query is that it uses / shows all selected production nations. What I want is for it to use only uses the first nation. Using the screens, this would mean that in screen 1 only USA (Nation ID 195 according to screen 2) is selected (France and UK are left out!). In screen 3 it means that for each Film ID, the LinkFilmNation ID with the lowest number is chosen (in this case for Film ID 2 only LinkFilmNation ID 2 is chosen and the rest, LinkFilmNation ID 3 and 4, aren't used).

The outcome of the query I want should be that each movie is represented only once by only 1 nation. But in the current query, the movie called Film ID 2 is represented / counted 3 times. The following screen shows the outcome as it should be

I will see what I can come up with tomorrow using Delerna's solution. But I hope there is a less complicated way to resolve this.

Margie

/edit: move cursor over picture to view it

[NeoPa]

Hi Margie.

This should be enough info to work from (good job) but I've been very busy today & recently with an important project at work (I know, being held up by work! It's just not on really :D) Anyway, I should be able to look at this again tomorrow and find something that'll work for you.

For now I have some workings out which will help me to start from then so I'll include them to get me going then. This is little more than an "easier to work with" equivalent of your posted SQL. It isn't supposed to be helpful yet.

SELECT   tN.Nation,
         tLFN.[Nation ID],
         Count(*) AS Total,
         Avg(tF.Score) AS [Average Score]
 
FROM     Film AS tF INNER JOIN
         (Nation AS tN INNER JOIN
         LinkFilmNation AS tLFN
  ON     tN.[Nation ID]=tLFN.[Nation ID])
  ON     tF.[Film ID]=tLFN.[Film ID] 
 
GROUP BY tN.Nation,
         tLFN.[Nation ID],
         tF.Status,
         tF.Ok 
 
HAVING  ((tF.Status='Seen')
   AND   (tF.Ok=Yes))
 
ORDER BY Count(*) DESC

[NeoPa]

Hi Margie.

Sorry I've taken so long to return to this. It was a little fiddly so I couldn't just pick it up in a spare 5 minutes I'm afraid.

Anyway, I've put some SQL together that should point you in the right direction. I couldn't see what the numbers you showed were, or where they came from. Nor could I see how the films were included in the results shown, so what I did was prepare some SQL which included the Film table but didn't select any of the fields. I'm sure you know what it is you want from there and I'm equally sure you can build from there. It does at least select the correct nation related to all the films held.

Have some fun with it :)

SELECT   tN.Nation,
         tN.[Nation ID]
 
FROM     (((
    SELECT   Min([LinkFilmNation ID]) AS MinLFN
    FROM     LinkFilmNation
    GROUP BY [Film ID]
    ORDER BY MinLFN
    ) AS subLFN INNER JOIN
         LinkFilmNation AS tLFN
  ON     subLFN.MinLFN=tLFN.[LinkFilmNation ID]) INNER JOIN
         Film AS tF
  ON     tLFN.[Film ID]=tF.[Film ID]) INNER JOIN
         Nation AS tN
  ON     tLFN.[Nation ID]=tN.[Nation ID]

[Margie]

It has been a while since the last post (had to do Prince2 in the meantime) , but I would like to thank both Delerna and NeoPa for their contributions, efforts and patience!

Delerna for pointing out the existence of virtual tables, something I had no awareness of, and 'forcing' me to think. It felt overwhelming in the beginning but after letting it rest first for a few days, I began to grasp the matter and the query was on its way. And NeoPa for practically making it a walk in the park and making my life easier.

Thank you both! Margie

[NeoPa]

It's always a pleasure to help Margie - especially when you take the time to reply so nicely.

In case it helps, I include a link to Subqueries in SQL. I assume this is what you mean when referring to Virtual Tables.