Filter Sum Nightmare

[Guest]

So I'm running a kareoke contest (great use of access, right?) and I'm trying
to keep track of contestants. Now that we're entering the Semi-Finals, I
need to construct a report that displays just those individuals that have
qualified. I had been runing a query that determined whether they had
received a qualifying score or not. That is displayed below.(Query Name is
DispReport)

SELECT Songs.ContestantName, Songs.Song, Songs.Artist, Songs.Score,
[Dates/Weeks].KarWeek, [Dates/Weeks].WhichDate, IIf([Score]>=18,-1,0) AS
Qualified
FROM (Contestants INNER JOIN Songs ON Contestants.Contestant =
Songs.ContestantName) INNER JOIN [Dates/Weeks] ON Songs.CompWeek =
[Dates/Weeks].WhichDate
GROUP BY Songs.ContestantName, Songs.Song, Songs.Artist, Songs.Score,
[Dates/Weeks].KarWeek, [Dates/Weeks].WhichDate, IIf([Score]>=18,-1,0);

You can see the calculated field I was using to determine if they had
qualified (which translated to a checkbox on my report). By running the
following query, I can see who qualified.

SELECT DispReport.ContestantName, Sum(DispReport.Qualified) AS SumOfQualified
FROM DispReport
GROUP BY DispReport.ContestantName
HAVING (((Sum(DispReport.Qualified))=-2));

I want to be able to view all records of what songs have been performed by
all individuals who have qualified for the semis. This requires bridging the
two queries somehow, and I am lost there. I'm sure it's quite simple. It's
just beyond me. Help is greatly appreciated! Thanks!

 

[Guest]

I think you can probably do that with a single query:

SELECT *
FROM Songs AS S1
WHERE EXISTS
(SELECT *
FROM Songs AS S2
WHERE S2.Score >= 18
AND S2.ContestantName = S1.ContestantName);

This returns all rows from Songs by a contestant where at least one row
exists where that contestant has scored 18 or more. Ii works by correlating
the subquery with the outer query on the ContestantName column, using the
aliases S1 and S2 to distinguish the two instances of the Songs table. If
you also want to show the date for each performance just join the Dates/Weeks
table to the S1 table in the outer query in the usual way.

Incidentally its better programming practice to use the Boolean terms TRUE
and FALSE rather than their implementation as -1 and 0, e.g.

IIf([Score]>=18, TRUE, FALSE)

Using -1 or 0 is what the head of a software company of my acquaintance once
referred to as "being unduly chummy with the implementation". In fact you
don't even need to use the IIf function at all as the expression itself
evaluates to TRUE or FALSE so simply (Score >= 18) AS Qualified would do.

Ken Sheridan
Stafford

 

[Guest]

The problem I ran into was that each contestant needed two scores of 18 or
over to qualify. The other issue I was having was that True and False were
for some reason not translating into the checkbox I had in a report, so I
boiled the code down to -1 or 0. That also allowed me to evaluate whether
they've qualified twice by suming that calculated field in the query.

I actually solved my problem by executing a join between the two queries
(which I didn't realize was possible until I did a little experimenting in
design view). Thanks for your contribution though.

I think you can probably do that with a single query:

SELECT *
FROM Songs AS S1
WHERE EXISTS
(SELECT *
FROM Songs AS S2
WHERE S2.Score >= 18
AND S2.ContestantName = S1.ContestantName);

This returns all rows from Songs by a contestant where at least one row
exists where that contestant has scored 18 or more. Ii works by correlating
the subquery with the outer query on the ContestantName column, using the
aliases S1 and S2 to distinguish the two instances of the Songs table. If
you also want to show the date for each performance just join the Dates/Weeks
table to the S1 table in the outer query in the usual way.

Incidentally its better programming practice to use the Boolean terms TRUE
and FALSE rather than their implementation as -1 and 0, e.g.

IIf([Score]>=18, TRUE, FALSE)

Using -1 or 0 is what the head of a software company of my acquaintance once
referred to as "being unduly chummy with the implementation". In fact you
don't even need to use the IIf function at all as the expression itself
evaluates to TRUE or FALSE so simply (Score >= 18) AS Qualified would do.

Ken Sheridan
Stafford

So I'm running a kareoke contest (great use of access, right?) and I'm trying
to keep track of contestants. Now that we're entering the Semi-Finals, I
need to construct a report that displays just those individuals that have
qualified. I had been runing a query that determined whether they had
received a qualifying score or not. That is displayed below.(Query Name is
DispReport)

SELECT Songs.ContestantName, Songs.Song, Songs.Artist, Songs.Score,
[Dates/Weeks].KarWeek, [Dates/Weeks].WhichDate, IIf([Score]>=18,-1,0) AS
Qualified
FROM (Contestants INNER JOIN Songs ON Contestants.Contestant =
Songs.ContestantName) INNER JOIN [Dates/Weeks] ON Songs.CompWeek =
[Dates/Weeks].WhichDate
GROUP BY Songs.ContestantName, Songs.Song, Songs.Artist, Songs.Score,
[Dates/Weeks].KarWeek, [Dates/Weeks].WhichDate, IIf([Score]>=18,-1,0);

You can see the calculated field I was using to determine if they had
qualified (which translated to a checkbox on my report). By running the
following query, I can see who qualified.

SELECT DispReport.ContestantName, Sum(DispReport.Qualified) AS SumOfQualified
FROM DispReport
GROUP BY DispReport.ContestantName
HAVING (((Sum(DispReport.Qualified))=-2));

I want to be able to view all records of what songs have been performed by
all individuals who have qualified for the semis. This requires bridging the
two queries somehow, and I am lost there. I'm sure it's quite simple. It's
just beyond me. Help is greatly appreciated! Thanks!

 

[Guest]

To cater for the '18+ twice' rule count the rows with an 18+ score for the
current contestant in the subquery :

SELECT *
FROM Songs AS S1
WHERE
(SELECT COUNT(*)
FROM Songs AS S2
WHERE S2.Score >= 18
AND S2.ContestantName = S1.ContestantName) >= 2;

This should give you all the songs performed by those contestants who have
scored more than 18 on two or more occasions.

I don't know why you should have had problems with TRUE and FALSE. They are
really just constants, to which Access assigns values of -1 and 0, so should
be interchangeable with the underlying values. You can see this by
converting the Boolean values to numbers in the debug window, which gives you:

? CInt(TRUE)
-1
? CInt(FALSE)
0

The reason its not considered a good idea to use the implementation
directly, however, is that other dialects of SQL will implement Boolean
values differently, so it inhibits portability (something SQL programmers aim
at as far as possible), and its theoretically possible, albeit very unlikely,
that Microsoft could change the implementation at some time in the future.

Summing the underlying values is again considered 'being unduly chummy with
the implementation' for the above reasons. In Access the way to count the
number of TRUE results of a Boolean expression is:

SUM(IIF(Score >= 18,1,0))

This is not portable of course as it uses the IIF function. In standard SQL
there are other ways of doing it.

Ken Sheridan
Stafford, England

The problem I ran into was that each contestant needed two scores of 18 or
over to qualify. The other issue I was having was that True and False were
for some reason not translating into the checkbox I had in a report, so I
boiled the code down to -1 or 0. That also allowed me to evaluate whether
they've qualified twice by suming that calculated field in the query.

I actually solved my problem by executing a join between the two queries
(which I didn't realize was possible until I did a little experimenting in
design view). Thanks for your contribution though.

I think you can probably do that with a single query:

SELECT *
FROM Songs AS S1
WHERE EXISTS
(SELECT *
FROM Songs AS S2
WHERE S2.Score >= 18
AND S2.ContestantName = S1.ContestantName);

This returns all rows from Songs by a contestant where at least one row
exists where that contestant has scored 18 or more. Ii works by correlating
the subquery with the outer query on the ContestantName column, using the
aliases S1 and S2 to distinguish the two instances of the Songs table. If
you also want to show the date for each performance just join the Dates/Weeks
table to the S1 table in the outer query in the usual way.

Incidentally its better programming practice to use the Boolean terms TRUE
and FALSE rather than their implementation as -1 and 0, e.g.

IIf([Score]>=18, TRUE, FALSE)

Using -1 or 0 is what the head of a software company of my acquaintance once
referred to as "being unduly chummy with the implementation". In fact you
don't even need to use the IIf function at all as the expression itself
evaluates to TRUE or FALSE so simply (Score >= 18) AS Qualified would do.

Ken Sheridan
Stafford

So I'm running a kareoke contest (great use of access, right?) and I'm trying
to keep track of contestants. Now that we're entering the Semi-Finals, I
need to construct a report that displays just those individuals that have
qualified. I had been runing a query that determined whether they had
received a qualifying score or not. That is displayed below.(Query Name is
DispReport)

SELECT Songs.ContestantName, Songs.Song, Songs.Artist, Songs.Score,
[Dates/Weeks].KarWeek, [Dates/Weeks].WhichDate, IIf([Score]>=18,-1,0) AS
Qualified
FROM (Contestants INNER JOIN Songs ON Contestants.Contestant =
Songs.ContestantName) INNER JOIN [Dates/Weeks] ON Songs.CompWeek =
[Dates/Weeks].WhichDate
GROUP BY Songs.ContestantName, Songs.Song, Songs.Artist, Songs.Score,
[Dates/Weeks].KarWeek, [Dates/Weeks].WhichDate, IIf([Score]>=18,-1,0);

You can see the calculated field I was using to determine if they had
qualified (which translated to a checkbox on my report). By running the
following query, I can see who qualified.

SELECT DispReport.ContestantName, Sum(DispReport.Qualified) AS SumOfQualified
FROM DispReport
GROUP BY DispReport.ContestantName
HAVING (((Sum(DispReport.Qualified))=-2));

I want to be able to view all records of what songs have been performed by
all individuals who have qualified for the semis. This requires bridging the
two queries somehow, and I am lost there. I'm sure it's quite simple. It's
just beyond me. Help is greatly appreciated! Thanks!

 

[Guest]

I can't recall exactly the error I was having before, but it had something to
do with counting True/False fields.

I appreciate your argument for portability, and if this was something that I
was wishing to roll out on a larger scale, I think it would be a larger
concern. As you can see though, Access Query design is not my forte, and as
such, I'm trying to get a functional tool for limited implementation.

One thing your post did provide me with was a better understanding of
sub-query syntax (as I thought I might forget my name and start calling
myself Syntax Error, due to the number of times I saw it recently). Thanks
so much for your help Ken, and also the insight on SQL design ettiquette.
Have a great day!

-Ed Wunch

P.S. Here's a copy of the final query.

SELECT ContestantName, Song, Artist, [Dates/Weeks].KarWeek
FROM (Songs AS S1)
INNER JOIN [Dates/Weeks] ON S1.CompWeek = [Dates/Weeks].WhichDate
WHERE ((((SELECT COUNT(*)
FROM Songs AS S2
WHERE S2.Score >= 18
AND S2.ContestantName = S1.ContestantName))>=2));

To cater for the '18+ twice' rule count the rows with an 18+ score for the
current contestant in the subquery :

SELECT *
FROM Songs AS S1
WHERE
(SELECT COUNT(*)
FROM Songs AS S2
WHERE S2.Score >= 18
AND S2.ContestantName = S1.ContestantName) >= 2;

This should give you all the songs performed by those contestants who have
scored more than 18 on two or more occasions.

I don't know why you should have had problems with TRUE and FALSE. They are
really just constants, to which Access assigns values of -1 and 0, so should
be interchangeable with the underlying values. You can see this by
converting the Boolean values to numbers in the debug window, which gives you:

? CInt(TRUE)
-1
? CInt(FALSE)
0

The reason its not considered a good idea to use the implementation
directly, however, is that other dialects of SQL will implement Boolean
values differently, so it inhibits portability (something SQL programmers aim
at as far as possible), and its theoretically possible, albeit very unlikely,
that Microsoft could change the implementation at some time in the future.

Summing the underlying values is again considered 'being unduly chummy with
the implementation' for the above reasons. In Access the way to count the
number of TRUE results of a Boolean expression is:

SUM(IIF(Score >= 18,1,0))

This is not portable of course as it uses the IIF function. In standard SQL
there are other ways of doing it.

Ken Sheridan
Stafford, England

The problem I ran into was that each contestant needed two scores of 18 or
over to qualify. The other issue I was having was that True and False were
for some reason not translating into the checkbox I had in a report, so I
boiled the code down to -1 or 0. That also allowed me to evaluate whether
they've qualified twice by suming that calculated field in the query.

I actually solved my problem by executing a join between the two queries
(which I didn't realize was possible until I did a little experimenting in
design view). Thanks for your contribution though.

I think you can probably do that with a single query:

SELECT *
FROM Songs AS S1
WHERE EXISTS
(SELECT *
FROM Songs AS S2
WHERE S2.Score >= 18
AND S2.ContestantName = S1.ContestantName);

This returns all rows from Songs by a contestant where at least one row
exists where that contestant has scored 18 or more. Ii works by correlating
the subquery with the outer query on the ContestantName column, using the
aliases S1 and S2 to distinguish the two instances of the Songs table. If
you also want to show the date for each performance just join the Dates/Weeks
table to the S1 table in the outer query in the usual way.

Incidentally its better programming practice to use the Boolean terms TRUE
and FALSE rather than their implementation as -1 and 0, e.g.

IIf([Score]>=18, TRUE, FALSE)

Using -1 or 0 is what the head of a software company of my acquaintance once
referred to as "being unduly chummy with the implementation". In fact you
don't even need to use the IIf function at all as the expression itself
evaluates to TRUE or FALSE so simply (Score >= 18) AS Qualified would do.

Ken Sheridan
Stafford

:

So I'm running a kareoke contest (great use of access, right?) and I'm trying
to keep track of contestants. Now that we're entering the Semi-Finals, I
need to construct a report that displays just those individuals that have
qualified. I had been runing a query that determined whether they had
received a qualifying score or not. That is displayed below.(Query Name is
DispReport)

SELECT Songs.ContestantName, Songs.Song, Songs.Artist, Songs.Score,
[Dates/Weeks].KarWeek, [Dates/Weeks].WhichDate, IIf([Score]>=18,-1,0) AS
Qualified
FROM (Contestants INNER JOIN Songs ON Contestants.Contestant =
Songs.ContestantName) INNER JOIN [Dates/Weeks] ON Songs.CompWeek =
[Dates/Weeks].WhichDate
GROUP BY Songs.ContestantName, Songs.Song, Songs.Artist, Songs.Score,
[Dates/Weeks].KarWeek, [Dates/Weeks].WhichDate, IIf([Score]>=18,-1,0);

You can see the calculated field I was using to determine if they had
qualified (which translated to a checkbox on my report). By running the
following query, I can see who qualified.

SELECT DispReport.ContestantName, Sum(DispReport.Qualified) AS SumOfQualified
FROM DispReport
GROUP BY DispReport.ContestantName
HAVING (((Sum(DispReport.Qualified))=-2));

I want to be able to view all records of what songs have been performed by
all individuals who have qualified for the semis. This requires bridging the
two queries somehow, and I am lost there. I'm sure it's quite simple. It's
just beyond me. Help is greatly appreciated! Thanks!