469,644 Members | 1,774 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,644 developers. It's quick & easy.

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

With information gathered from a question I posted 3 weeks ago, I made the following query for my movie database:
Expand|Select|Wrap|Line Numbers
  1. SELECT Nation.Nation, LinkFilmNation.[Nation ID], Count(*) AS Total, Avg(Film.[Score]) AS [Average Score] 
  2. FROM Film INNER JOIN Nation INNER JOIN LinkFilmNation ON Nation.[Nation ID] = LinkFilmNation.[Nation ID]) ON Film.[Film ID] = LinkFilmNation.[Film ID] 
  3. GROUP BY Nation.Nation, LinkFilmNation.[Nation ID], Film.Status, Film.Ok 
  4. HAVING (((Film.Status)="Seen") AND ((Film.Ok)=Yes)) 
  5. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT LinkFilmNation.[LinkFilmNation ID], LinkFilmNation.[Film ID], LinkFilmNation.[Nation ID]
  2. 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
Feb 7 '10 #1
7 2367
1,134 Expert 1GB
I think you need a query of the table that you can then use instead of the table in other queries.
Something like
Expand|Select|Wrap|Line Numbers
  1. SELECT b.[LinkFilmNation ID] , 
  2.        b.[Film ID], 
  3.        b.[Nation ID]
  4. FROM
  5. (   SELECT [LinkFilmNation ID] as LnkFlmNatID, 
  6.            [Film ID]
  7.      FROM LinkFilmNation
  8.      GROUP BY [Film ID]
  9. )a
  10. JOIN LinkFilmNation b on a.[LnkFlmNatID]=b.[LinkFilmNation ID]
  11.                      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.
Feb 8 '10 #2
32,203 Expert Mod 16PB
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.
Feb 8 '10 #3
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.


/edit: move cursor over picture to view it
Feb 9 '10 #4
32,203 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT   tN.Nation,
  2.          tLFN.[Nation ID],
  3.          Count(*) AS Total,
  4.          Avg(tF.Score) AS [Average Score]
  6. FROM     Film AS tF INNER JOIN
  7.          (Nation AS tN INNER JOIN
  8.          LinkFilmNation AS tLFN
  9.   ON     tN.[Nation ID]=tLFN.[Nation ID])
  10.   ON     tF.[Film ID]=tLFN.[Film ID] 
  12. GROUP BY tN.Nation,
  13.          tLFN.[Nation ID],
  14.          tF.Status,
  15.          tF.Ok 
  17. HAVING  ((tF.Status='Seen')
  18.    AND   (tF.Ok=Yes))
  20. ORDER BY Count(*) DESC
Feb 11 '10 #5
32,203 Expert Mod 16PB
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 :)
Expand|Select|Wrap|Line Numbers
  1. SELECT   tN.Nation,
  2.          tN.[Nation ID]
  4. FROM     (((
  5.     SELECT   Min([LinkFilmNation ID]) AS MinLFN
  6.     FROM     LinkFilmNation
  7.     GROUP BY [Film ID]
  8.     ORDER BY MinLFN
  9.     ) AS subLFN INNER JOIN
  10.          LinkFilmNation AS tLFN
  11.   ON     subLFN.MinLFN=tLFN.[LinkFilmNation ID]) INNER JOIN
  12.          Film AS tF
  13.   ON     tLFN.[Film ID]=tF.[Film ID]) INNER JOIN
  14.          Nation AS tN
  15.   ON     tLFN.[Nation ID]=tN.[Nation ID]
Feb 16 '10 #6
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
Mar 5 '10 #7
32,203 Expert Mod 16PB
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.
Mar 5 '10 #8

Post your reply

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

Similar topics

14 posts views Thread by Sean C. | last post: by
9 posts views Thread by WalterR | last post: by
6 posts views Thread by Peter Frost | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.