Thanks for that - (I can now see that bit in your eample- doh!)
my sub query now looks like this:
SELECT TOP 1 DupeE.ExhibitorID
FROM (CupSectionsqry AS DupeC INNER JOIN PrizeMoney AS Dupem ON
DupeC.SectionID = Dupem.SectionID) INNER JOIN Exhibitors AS DupeE ON
Dupem.ExhibitorID = DupeE.ExhibitorID
WHERE DupeE.ExhibitorID=Exhibitors.ExhibitorID
GROUP BY DupeE.ExhibitorID, DupeC.SectionID
ORDER BY Sum(Dupem.Points) DESC;
However the Exhibitors.ExhibitorID - isn't recognised - as in Access puts
up
the dialogue box "Enter parameter Value"
:
Okay, I'm not sure of the structure of your tables, but the subquery will
need a WHERE clause that limits it to only the exhibitor in the main
query,
e.g.:
WHERE EupeE.ExhibitorID = Exhabitors.ExhibitorID
Sorry Allen - I was in a rush and must have posted an earlier attmept.
Here
is one with the aliases in - but it still only gives me one result
overall
not one per group:
SELECT CupSectionsqry.CupName, PrizeMoney.SectionName,
Exhibitors.Surname,
Exhibitors.FirstName, Exhibitors.Title, Sum(PrizeMoney.Points) AS
SumOfPoints
FROM CupSectionsqry INNER JOIN (PrizeMoney INNER JOIN Exhibitors ON
PrizeMoney.ExhibitorID=Exhibitors.ExhibitorID) ON
CupSectionsqry.SectionID=PrizeMoney.SectionID
WHERE (Exhibitors.ExhibitorID) In (SELECT TOP 1 Dupem.ExhibitorID
FROM (CupSectionsqry AS DupeC INNER JOIN PrizeMoney AS Dupem ON
DupeC.SectionID = Dupem.SectionID) INNER JOIN Exhibitors AS DupeE ON
Dupem.ExhibitorID = DupeE.ExhibitorID
GROUP BY Dupem.ExhibitorID, DupeC.SectionID
ORDER BY Sum(Dupem.Points) DESC
GROUP BY CupSectionsqry.CupName, PrizeMoney.SectionName,
Exhibitors.Surname,
Exhibitors.FirstName, Exhibitors.Title;
I thought I had understood the sub-query - but it's not working :-(
:
You have the same tables in the main query and in the subquery.
If you really need all the tables in both, you will need to alias
them.
For
example:
...
WHERE Exhibitors.ExhibitorID IN
(SELECT TOP 12 PM.ExhibitorID
FROM CupSectionsqry AS CS
INNER JOIN (PrizeMoney AS PM ...
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Thanks Allen, I thoughy sub-queries might be the answer - HOWEVER, I
have
spent some time using your excellent site and I thought I had got
there -
but
I only appear to get the top 1 value overall - not one per group.
Here is my query sql:
SELECT CupSectionsqry.CupName, PrizeMoney.SectionName,
Exhibitors.Surname,
Exhibitors.FirstName, Exhibitors.Title
FROM CupSectionsqry INNER JOIN (PrizeMoney INNER JOIN Exhibitors ON
PrizeMoney.ExhibitorID = Exhibitors.ExhibitorID) ON
CupSectionsqry.SectionID
= PrizeMoney.SectionID
WHERE (((Exhibitors.ExhibitorID) In (SELECT TOP 1
PrizeMoney.ExhibitorID
FROM CupSectionsqry INNER JOIN (PrizeMoney INNER JOIN Exhibitors ON
PrizeMoney.ExhibitorID = Exhibitors.ExhibitorID) ON
CupSectionsqry.SectionID
= PrizeMoney.SectionID GROUP BY CupSectionsqry.SectionID,
PrizeMoney.ExhibitorID ORDER BY Sum(PrizeMoney.Points) DESC)))
GROUP BY CupSectionsqry.CupName, PrizeMoney.SectionName,
Exhibitors.Surname,
Exhibitors.FirstName, Exhibitors.Title;
Any thoughts?
Many thanks
:
See:
SubqeuryBasics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN
I'm sure this is possible but I just can't work out a way, I'm
sure
some
clever person can
I have a competition and there are classes, which people can win
and
get
points, the classes are organised into different sections. There
are
cups
for the person with the most points in a section. I would like
to
produce
a
list that shows the cups and the winners of each section.
Using the following SQL I can easily produce a report showing the
total
points that each person has gained - but I cannot find a way of
listing
just
the top one (and what if there are 2 with the same number of
points?)
SELECT CupSectionsqry.CupName, [Prize money].SectionName,
Sum([Prize money].Points) AS SumOfPoints,
Exhibitors.Surname, Exhibitors.FirstName, Exhibitors.Title
FROM CupSectionsqry INNER JOIN ([Prize money]
INNER JOIN Exhibitors
ON [Prize money].ExhibitorID = Exhibitors.[Exhibitor ID])
ON CupSectionsqry.SectionID = [Prize money].SectionID
GROUP BY CupSectionsqry.CupName, [Prize money].SectionName,
Exhibitors.Surname, Exhibitors.FirstName, Exhibitors.Title
ORDER BY Sum([Prize money].Points) DESC;