Displaying only the top value from a group

H

HelenJ

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;

Thanks.
 
H

HelenJ

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

Allen Browne said:
See:
SubqeuryBasics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HelenJ said:
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;
Click to expand...
Click to expand...
 
A

Allen Browne

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.

HelenJ said:
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

Allen Browne said:
See:
SubqeuryBasics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

HelenJ said:
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;
Click to expand...
Click to expand...
Click to expand...
 
H

HelenJ

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 :-(

Allen Browne said:
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.

HelenJ said:
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

Allen Browne said:
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;
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HelenJ said:
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 :-(

Allen Browne said:
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.

HelenJ said:
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;
Click to expand...
Click to expand...
Click to expand...
 
H

HelenJ

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"



Allen Browne said:
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HelenJ said:
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 :-(

Allen Browne said:
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;
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

The subquery is refering to the field in the main query.
If Access is asking for a parameter, then the spelling is not perfect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HelenJ said:
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"



Allen Browne said:
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

HelenJ said:
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;
Click to expand...
Click to expand...
Click to expand...
 
H

HelenJ

I've copied and pasted it from the main query so I know it is spelt correctly.

I've also taken another look at your example and I am confused by something.
You have :

(SELECT TOP 3 OrderID
FROM Orders AS Dupe

My equivalent would be
(SELECT TOP 3 ExhibitorID
FROM Exhibitors AS DupeE

But if I do this Access says that my ExhibitorID could come from more than
one table. Perhaps that is because the field is in several tables and this
is not a sub query problem?

I've tried to produce a cut down version of my query to see if it makes any
more sense - this is still giving me the "parameter" problem and from trial
and error I know it comes from the WHERE clause in my sub-query, but I can't
see why it doesn't recognise the Exhibitors.ExhibitorID field (it's clearly
there in the main query)

SELECT CupSectionsqry.CupName, Exhibitors.Surname
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 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)
GROUP BY CupSectionsqry.CupName, Exhibitors.Surname;

Is it the grouping that is giving me the problems??

Allen Browne said:
The subquery is refering to the field in the main query.
If Access is asking for a parameter, then the spelling is not perfect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HelenJ said:
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"



Allen Browne said:
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;
Click to expand...
Click to expand...
Click to expand...
 
H

HelenJ

OK I decided that the grouping etc was causing me too many problems - so I
have created a query to group, total and sort my points and export them to a
new table. I am now basing my subquery on that table - it at least makes it
easier to read and I figure if I can get it working this way then I *might*
be able to base it on the more complicated subquery later.

SO now I don't have problems with fields not being recognised BUT it is
still not doing what I want ie it is not restricting to the top 1.

Is there a chance that you can see what I have done wrong here - it all
"works" except the answer gives ALL the records - not just the top of each
group!!

SELECT CupSectionsqry.CupName, Exhibitors.Surname, PrizeMoney.Points
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 ExhibitorID
FROM PointsSectionTotals
WHERE PointsSectionTotals.ExhibitorID=Exhibitors.ExhibitorID
ORDER BY PointsSectionTotals.SumOfPoints)
GROUP BY CupSectionsqry.CupName, Exhibitors.Surname, PrizeMoney.Points
ORDER BY CupSectionsqry.CupName DESC , PrizeMoney.Points DESC;


HelenJ said:
I've copied and pasted it from the main query so I know it is spelt correctly.

I've also taken another look at your example and I am confused by something.
You have :

(SELECT TOP 3 OrderID
FROM Orders AS Dupe

My equivalent would be
(SELECT TOP 3 ExhibitorID
FROM Exhibitors AS DupeE

But if I do this Access says that my ExhibitorID could come from more than
one table. Perhaps that is because the field is in several tables and this
is not a sub query problem?

I've tried to produce a cut down version of my query to see if it makes any
more sense - this is still giving me the "parameter" problem and from trial
and error I know it comes from the WHERE clause in my sub-query, but I can't
see why it doesn't recognise the Exhibitors.ExhibitorID field (it's clearly
there in the main query)

SELECT CupSectionsqry.CupName, Exhibitors.Surname
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 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)
GROUP BY CupSectionsqry.CupName, Exhibitors.Surname;

Is it the grouping that is giving me the problems??

Allen Browne said:
The subquery is refering to the field in the main query.
If Access is asking for a parameter, then the spelling is not perfect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HelenJ said:
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;
Click to expand...
Click to expand...
Click to expand...
 
K

krissco

OK. I think I was a little hasty on that last post. Building a large
query can sometimes boggle the mind. I find that if the query is
sufficiently complicated, it is best to break it down into its
smallest parts - this helps when trying to understand and create it.


If I understand things correctly, you have schema similar to:
CupSectionsqry: CupName, SectionID
[Prize Money]: Points, SectionName, SectionID, ExhibitorID
Exibitors: Surname, FirstName, Title, ExhibitorID


Try this approach:

1.Save this query as 'qrySectionPoints'

Select SectionID, SectionName, ExhibitorID, sum(Points) as myPoints
from [Prize Money] as m
group by SectionID, SectionName, ExhibitorID


2.Save this query as 'qryMaxSectionPoints'

Select SectionID, SectionName, max(myPoints) as myMaxPoints
from qrySectionPoints
group by SectionID, SectionName


3.Save this query as 'qrySectionWinners'

select a.SectionID, a.SectionName, ExhibitorID, myPoints
from qrySectionPoints as a, qryMaxSectionPoints as b
where a.SectionID = b.SectionID
and myPoints = myMaxPoints


4.Report the information in one final query:

Select CupName, SectionName, Surname, FirstName, Title, myPoints
from CupSectionsqry as c, qrySectionWinners as p, Exhibitors as e
where c.SectionID = p.sectionID
and e.ExhibitorID = p.ExhibitorID


Nice, bite-sized pieces of SQL. I also recommend building
documentation for your queries (the best I've found is
http://www.lebans.com/addsqlcomments.htm).

-Kris
 
H

HelenJ

Kris that is truely awesome - It works brilliantly. I confess that I have
been tending to try and do everything in one sql statement because having
lots of queries didn't seem a good way to go. Also the great thing is that I
don't have to construct intermediate tables.

I will also look at the documenting you mentioned - that must help the
proliferation of queries (particularly when you end up with several when
trying to slove a problem).

Again many thanks (the only drawback is I still don't feel I have "cracked"
subqueries :-( )
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trouble Calculating a "score" when the Value is Negative 4

Top